mysql 的datetime和timestamp的知识小结

最新推荐文章于 2025-07-28 17:21:34 发布

原创最新推荐文章于 2025-07-28 17:21:34 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #timestamp #DATETIME

Database- mysql 专栏收录该内容

15 篇文章

订阅专栏

本文对比了MySQL中datetime和timestamp两种日期时间类型的特性与区别，包括取值范围、自动初始化和更新行为、时区转换能力及存储大小。通过具体实例展示了timestamp在不同场景下的表现，如自动更新限制和NULL值处理。

总结下mysql的datetime和timestamp两种类型的相关知识

mysql 的datetime和timestamp的区别

datetime的取值范围是1000-01-01 00:00:00 到9999-12-31 23:59:59 而timestamp仅为1970-01-01 00:00:00 到2037 23:59:59
timestamp具有自动初始化和自动更新的特性：自动初始化为当前日期和时间，当发生变化时自动更新为当前时间
timestamp可以自动转换为客户端时区
timestamp 只需要4个字节而datetime需要8个字节

对于timestamp还有以下特性：

一张表中可以同时有多个TIMESTAMP类型列，但是只能有一列具有上述特性，其他列都会被初始化为0测试验证如下：

create table test (id int(32) ,time1 timestamp, time2 timestamp);

insert test values(1);

insert test values(2);

select * from test;
+------+---------------------+---------------------+
| id   | time1               | time2               |
+------+---------------------+---------------------+
|    1 | 2014-03-04 11:51:56 | 0000-00-00 00:00:00 |
|    2 | 2014-03-04 11:51:58 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+

可以看出只有time1被初始化了当前时间

而如果执行

alter table test modify column `time2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

即看出不允许一张表同时有两个都是自动更新的。

而可以通过设置为NULL将其值更新为当前时间

insert test(id,time2) values(3, NULL);

+------+---------------------+---------------------+
| id   | time1               | time2               |
+------+---------------------+---------------------+
|    1 | 2014-03-04 11:51:56 | 0000-00-00 00:00:00 |
|    2 | 2014-03-04 11:51:58 | 0000-00-00 00:00:00 |
|    3 | 2014-03-04 11:56:07 | 2014-03-04 11:56:07 |
+------+---------------------+---------------------+

mysql 建表时，如果不对timestamp做任何限制，则默认就是自动初始化和自动更新的特性

show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(32) DEFAULT NULL,
`time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`time2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

同时对于timestamp 的not null，说明其不能保存NULL值，但是仍然可以给该列赋NULL，数据库会自动将其保存为当前日期和时间

如果希望timestamp自动初始化但是不自动更新，可以只使用DEFAULT CURRENT_TIMESTAMP