MySQL 的 timestamp 和 datetime 类型比较

matteao 的个人博客 / 2892 / 9 / 创建于 4年前 / 更新于 4年前

前言

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

测试环境

mysql 5.7 + phpmyadmin

测试步骤

插入数据时，timestamp 和 datetime的表现
更新数据时，timestamp 和 datetime的表现
更改mysql 的time_zone 值，timestamp 和 datetime的表现

测试数据表

MySQL 的 timestamp 和 datetime 类型比较

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

有以下区别：
1. datetime类型可以设置默认值，也可以不设置
2. 第一个timestamp类型的默认值自动变为 CURRENT_TIMESTAMP，约束为 ON UPDATE CURRENT_TIMESTAMP 。CURRENT_TIMESTAMP 是mysql 的变量，值是当前时间。
3. 其他timestamp类型的默认值自动变为0000-00-00 00:00:00

字段默认值调整

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

MySQL 的 timestamp 和 datetime 类型比较

插入数据

timestamp 类型

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

结果：

MySQL 的 timestamp 和 datetime 类型比较

小结：

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

更新数据

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

结果：
MySQL 的 timestamp 和 datetime 类型比较

小结：

更新数据, 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";

mysql 的timestamp和datetime 类型比较

MySQL 的 timestamp 和 datetime 类型比较

小结：

更改时区后，改为+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类型存储的是根据操作数据当时的时区的时间。