mysql timestamp类型比较_MySQL 的 timestamp 和 datetime 类型比较

前言

最近做项目的时候，发现对mysql的timestamp 和datetime 的区别认识不清,所以建了一个测试表，测试了一下区别。另外，我在社区搜索相关文章时，没有发现针对性的文章，所以写出来。

测试环境

mysql 5.7 + phpmyadmin

测试步骤

插入数据时，timestamp 和 datetime的表现

更新数据时，timestamp 和 datetime的表现

更改mysql 的time_zone 值，timestamp 和 datetime的表现

测试数据表

其中字段datetime1 是datetime类型, 字段timestamp1, timestamp2, timestamp3 是timestamp类型。

有以下区别：

datetime类型 可以设置默认值，也可以不设置

第一个timestamp类型的默认值自动变为 CURRENT_TIMESTAMP， 约束为 ON UPDATE CURRENT_TIMESTAMP 。CURRENT_TIMESTAMP 是mysql 的变量，值是当前时间。

其他timestamp类型的默认值自动变为0000-00-00 00:00:00

字段默认值调整

为了有差异，我把字段timestamp3 的默认值更改为 CURRENT_TIMESTAMP。这样， 字段timestamp1和timestamp3的区别变成了ON UPDATE CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP的区别

插入数据

timestamp 类型

INSERT INTO `datetest`(`id`, `datetime1`, `timestamp1`, `timestamp2`, `timestamp3`) VALUES (1, now(), null, null, null);

INSERT INTO `datetest`(`id`, `datetime1`) VALUES (2, now());

结果：

小结：

插入数据， timestamp类型的传值为null时， 会自动变为当前时间

更新数据

UPDATE `datetest` SET `datetime1`=now(),`timestamp1`= null ,`timestamp2`= null,`timestamp3`= null WHERE id = 1;

UPDATE `datetest` SET `datetime1`=now() WHERE id = 2;

结果：

小结：

更新数据, timestamp类型没传值时，默认值为ON UPDATE CURRENT_TIMESTAMP的timestamp, 会更新为当前时间。以 CURRENT_TIMESTAMP 或者0000-00-00 00:00:00 为默认值的timestamp不会更新。

更改time_zone 值

set time_zone = "+9:00";

INSERT INTO `datetest`(`id`, `datetime1`, `timestamp1`, `timestamp2`, `timestamp3`) VALUES (4, now(), null, null, null);

show VARIABLES like "time_zone";

小结：

更改时区后， 改为+9:00时区后， datetime类型的小时 数值+1， timestamp类型的值不变。说明：timestamp存储的是UTC时间，然后显示的是根据当前时区的转换时间；datetime类型 存储的是根据操作数据当时的时区的时间。

结论

datetime 和 timestamp 类型的表现形式都为：yyyy-mm-dd hh:mm:ss ，其中datetime 占据8字节，timestamp占据4字节

datetime 的表示范围 '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'； timestamp的范围为'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'。

插入数据时， timestamp类型的传值为null， 会自动变为当前时间

更新数据时，timestamp类型没传值时，默认值为ON UPDATE CURRENT_TIMESTAMP的timestamp, 会更新为当前时间。以 CURRENT_TIMESTAMP 或者0000-00-00 00:00:00 为默认值的timestamp不会更新。

更改时区后， 改为+9:00时区后， datetime类型的小时 数值+1， timestamp类型的值不变。说明：timestamp存储的是UTC时间，然后显示的是根据当前时区的转换时间；datetime类型 存储的是根据操作数据当时的时区的时间。

本作品采用《CC 协议》，转载必须注明作者和本文链接