MySQL 尽量避免使用 TIMESTAMP!!
共 7953字,需浏览 16分钟
· 2021-10-29
![](https://filescdn.proginn.com/c5792d9a34eaed993e2a3283006eaab6/aec8129437fb86878dddf663b424941a.webp)
MySQL 中常见的时间类型有三种DATE
, DATETIME
和 TIMESTAMP
,其中DATE
类型用于表示日期,但是不会包含时间,格式为YYYY-MM-DD
,而DATETIME
和TIMESTAMP
用于表示日期和时间,常见的格式为YYYY-MM-DD HH:MM:SS
,也可以带6位小数来表示微秒。
不同于DATETIME
,TIMESTAMP
支持的时间范围从1970-01-01 00:00:01.000000
到2038-01-19 03:14:07.999999
,使用了TIMESTAMP
的应用很有可能在2038-01-19 03:14:07.999999
之后宕机,同样面临这个问题的还有所有的类Unix
系统,因为他们使用了time_t
这一32位数字来表示时间,这就是著名的2038年问题。
因为时间问题搞坏系统的例子可不少,在2016年曾经爆出过一个iPhone
的bug
,如果将iPhone
的时间调整到1970-01-01 00:00:00
,则会导致手机”变砖“,原因是IOS
基于BSD
这种Unix
系统构建,在将时间调整到1970-01-01 00:00:00
后,如果手机需要展示之前的时间,例如之前收到过短信,则会导致整数溢出。
对于2038问题,Linux的解法是提供新的用户接口:https://kernelnewbies.org/y2038.但是MySql
至今还没有相应的公告。
TIMESTAMP
的设计之初是为了支持自动时区转换:
mysql> CREATE TABLE `employee` (
-> `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `employee` (`entry_time`) VALUES (CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM `employee`;
+---------------------+
| entry_time |
+---------------------+
| 2021-05-09 08:14:08 |
+---------------------+
1 row in set (0.00 sec)
mysql> SET @@session.time_zone = '-05:00'; SELECT * FROM `employee`;
Query OK, 0 rows affected (0.00 sec)
+---------------------+
| entry_time |
+---------------------+
| 2021-05-09 03:14:08 |
+---------------------+
1 row in set (0.00 sec)
但是TIMESTAMP
的一些设计却非常鬼畜,比如:
如果表中包含 TIMESTAMP
的列,那么其建表语句有可能被系统篡改,取决于MySql的版本和参数设置。当 MySQL
参数time_zone=system
时,高并发可能会引起CPU使用率暴涨,系统响应变慢甚至假死如果存入超过范围的时间,在非严格状态下,MySql不会报错,反而会插入 '0000-00-00 00:00:00'
新建一个包含TIMESTAMP
的表可真难
MySql
5.6.6版本引入了explicit_defaults_for_timestamp
这个参数,随即被标记为废弃,这个参数主要影响表中类型为TIMESTAMP
的那些列在新建表时的表现
mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
mysql> create table t1
-> (
-> ts1 timestamp,
-> ts2 timestamp,
-> ts3 timestamp default '2010-01-01 00:00:00'
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`ts3` timestamp NOT NULL DEFAULT '2010-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
虽然我们输入的建表语句很简单,但是MySql
却对于我们输入的建表语句做了诸多的篡改:
对于表中的第一个 TIMESTAMP
列,系统自动加了NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
,这些操作对于新建表的开发者完全是不感知的。对于表中的第二个 TIMESTAMP
列,系统自动加了一个默认值0000-00-00 00:00:00
,这个操作同样对于新建表的开发者完全不感知。
在系统对我们的建表语句做了自动修改之后,对表的插入操作可能就不会如开发者预期的那样:
mysql> insert into t1 values (null,null,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+---------------------+---------------------+---------------------+
| ts1 | ts2 | ts3 |
+---------------------+---------------------+---------------------+
| 2021-05-09 07:47:50 | 2021-05-09 07:47:50 | 2021-05-09 07:47:50 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
可以看到,MySql
的表现非常的鬼畜
对于第一个 TIMESTAMP
列,建表语句中指定可以为null
,但是插入null
的时候存到表里的却是当前时间对于第二个 TIMESTAMP
列,虽然通过语句show create table t1\G
查出来的建表语句指定的默认值是'0000-00-00 00:00:00'
但是存到表里的却是当前时间最奇怪的是第三个 TIMESTAMP
列,尽管我们显式指定默认值为'2010-01-01 00:00:00'
,但是落表的时间仍然是当前时间
这一切都是在参数explicit_defaults_for_timestamp
被设置为OFF
的时候发生的,但是遗憾的是OFF
恰恰就是参数explicit_defaults_for_timestamp
的默认值。
![](https://filescdn.proginn.com/dbac5b45c549735c87a31f6bc8d99e2b/b3e4e32123986559983388706c92b92f.webp)
explicit_defaults_for_timestamp
的值改为ON
,则事情会变得好很多mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON |
+---------------------------------+-------+
mysql> create table t2
-> (
-> ts1 timestamp,
-> ts2 timestamp,
-> ts3 timestamp default '2010-01-01 00:00:00'
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`ts1` timestamp NULL DEFAULT NULL,
`ts2` timestamp NULL DEFAULT NULL,
`ts3` timestamp NULL DEFAULT '2010-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> insert into t2 values (null,null,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------+------+------+
| ts1 | ts2 | ts3 |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)
这一次,建表语句中那些奇怪的默认值都没有了,清爽了好多,而且TIMESTAMP
的的列也可以插入NULL
了,如果我们显式指定了NOT NULL
,STRICT_TRANS_TABLES
被指定的情况下直接报错,如果STRICT_TRANS_TABLES
没有被指定,那么会向该列中插入0000-00-00 00:00:00
并且产生一个warning
mysql> create table t3
-> (
-> ts1 timestamp,
-> ts2 timestamp,
-> ts3 timestamp not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`ts1` timestamp NULL DEFAULT NULL,
`ts2` timestamp NULL DEFAULT NULL,
`ts3` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> insert into t3 values (null,null,null);
ERROR 1048 (23000): Column 'ts3' cannot be null
mysql> insert into t3 (ts1,ts2) values (null,null);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1364 | Field 'ts3' doesn't have a default value |
+---------+------+------------------------------------------+
mysql> select * from t3;
+------+------+---------------------+
| ts1 | ts2 | ts3 |
+------+------+---------------------+
| NULL | NULL | 0000-00-00 00:00:00 |
+------+------+---------------------+
高并发环境下并不适合使用TIMESTAMP
这一点MySql
的文档中有明确的说明:
Note
If set to
SYSTEM
, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.
虽然通过TIMESTAMP
可以自动转换时区,代价是当MySQL
参数time_zone=system
时每次都会尝试获取一个全局锁,这在高并发的环境下无疑是致命的,可能会导致线程上下文频繁切换,CPU使用率暴涨,系统响应变慢甚至假死。点击这个Java面试库小程序可以在线刷题。
时间范围并不是强校验的
如果我们尝试往MySql
中插入超过TIMESTAMP
可表示的时间范围的值,MySql
在非严格模式下并不会报错,仅会产生一个warning
mysql> insert into t1 values ('2039-01-01 00:00:00',null,null);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'ts1' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+---------------------+---------------------+---------------------+
| ts1 | ts2 | ts3 |
+---------------------+---------------------+---------------------+
| 2021-05-09 07:47:50 | 2021-05-09 07:47:50 | 2021-05-09 07:47:50 |
| 0000-00-00 00:00:00 | 2021-05-09 08:09:06 | 2021-05-09 08:09:06 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
总结
现在用TIMESTAMP
比较少了,的确也应该尽量避免使用TIMESTAMP
,MySql
在TIMESTAMP
的设计上实在是蹩脚,如果你正在维护一个老的系统,涉及到TIMESTAMP
的改动需要格外注意,尽量要在充分的测试后再上线。
最后,关注公众号Java技术栈,在后台回复:面试,可以获取我整理的 Java/ MySQL 系列面试题和答案,非常齐全。
参考资料
![](https://filescdn.proginn.com/3e91bcf651eed534ba48ec8649103081/2d727330a57b6af82b5c85f8bed5f31c.webp)
![](https://filescdn.proginn.com/544dda225941367ad9d89360a7731a69/9b9c4bce0e9f430eeae5b18daab9a660.webp)
![](https://filescdn.proginn.com/56cd526cf7abf1d05daeb985900e71b8/19c644ad7e9c30e5c1088ea1822b2b45.webp)
![](https://filescdn.proginn.com/0c97838d71fa847e2873adf273e0592f/b3213d544a1456207d772882ccc9385b.webp)
![](https://filescdn.proginn.com/9bba4ac864eb0b2d2ebc5dc993089753/1e5349e1e5b17e4291998630b072568f.webp)
![](https://filescdn.proginn.com/2b880182ff8c2fd4a3f283305983d4e7/b0ac6d28c8c385ac7ec2ac1be68058ba.webp)
关注Java技术栈看更多干货
![](https://filescdn.proginn.com/574047a11471f0c6d7655d69e98cad49/30d6f4bd73568d22021f1ae377fcfeef.webp)