MySQL日期数据类型详解

共计 2172 个字符，预计需要花费 6 分钟才能阅读完成。

MySQL 日期和时间类型

MySQL 有 5 种表示时间值的日期和时间类型，分别为、DATE，TIME，YEAR，DATETIME，TIMESTAMP。

TIMESTAMP 类型有专有的自动更新特性，

TIMESTAMP 类型有专有的自动更新特性，

TIMESTAMP 类型有专有的自动更新特性，

日期类型

如果要用来表示年月日，通常用 DATE 来表示。

如果要用来表示年月日时分秒，通常用 DATETIME 表示。

如果只用来表示时分秒，通常用 TIME 来表示。

TIMESTAMP 表示格式：YYYY-MM-DD HH:MM:SS。

如果只是表示年份，可以用 YEAR 来表示。DATE，DATETIME，TIME 是常用三种。

在 MySQL 中创建表时，对照上面的表格，很容易就能选择到合适自己的数据类型。不过到底是选择 datetime 还是 timestamp，可能会有点犯难。这两个日期时间类型各有优点：datetime 的日期范围比较大；timestamp 所占存储空间比较小，只是 datetime 的一半。

另外，timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充 / 更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。
 

建表的代码为:

create table t8 (
  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `id2` datetime default NULL
);

一般情况下，我倾向于使用 datetime 日期类型。

两者之间的比较:

1. timestamp 容易所支持的范围比 timedate 要小。并且容易出现超出的情况

2.timestamp 比较受时区 timezone 的影响以及 MYSQL 版本和服务器的 SQL MODE 的影响.

MySQL 时间类型：时间格式、所占存储空间、时间范围。
时间类型        存储空间      时间格式                时间范围
———— ———  ——————— —————————————–
time          3 bytes  HH:MM:SS              -838:59:59          ~ 838:59:59

time 时间范围居然有这么大的范围，特别是 time 可以取负值，有点奇怪。后来，看了 MySQL 手册才知道这是为了满足两个日期时间相减才这样设计的。

select timediff(‘2000:01:31 23:59:59’, ‘2000:01:01 00:00:00’); — 743:59:59
select timediff(‘2000:01:01 00:00:00’, ‘2000:01:31 23:59:59’); — -743:59:59
select timediff(’23:59:59′, ’12:00:00′);                        — 11:59:59

注意，timediff 的两个参数只能是 datetime/timestamp, time 类型的，并且这两个参数类型要相同。即：datetime/timestamp 和 datetime/timestamp 比较；time 和 time 相比较。

虽然 MySQL 中的日期时间类型比较丰富，但遗憾的是，目前（2008-08-08）这些日期时间类型只能支持到秒级别，不支持毫秒、微秒。也没有产生毫秒的函数。

CREATE TABLE t(d DATE,t TIME,dt DATETIME)
— 插入当前日期
INSERT INTO t VALUES(NOW(),NOW(),NOW());
— 查看
SELECT * FROM t;

TIMESTAMP 也用来表示日期, 和时区相关（DATETIME 则只能反应出插入时当地的时区，其他时区的人查看数据必然会有误差的。）

CREATE TABLE t3(d1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t3 VALUES(NOW());

最后采用不同的格式将日期“2007-9-3 12:10:10”插入到 DATETIME 列中

CREATE TABLE t6(dt DATETIME);
INSERT INTO t6 VALUES(‘2007-9-3 12:10:10’);
INSERT INTO t6 VALUES(‘2007/9/3 12+10+10’);
INSERT INTO t6 VALUES(‘20070903121010’);
INSERT INTO t6 VALUES(20070903121010);
SELECT * FROM t6;

：