该图片由Simon Steinberger在Pixabay上发布
前言
本周有一个业务开发同学要刷数据将 datetime 类型的字段增加1s，但是部分字段的内容变成了 "0000-00-00 00:00:00" 导致相关业务查询数据不一致。具体开发怎么操作的呢？又触发了什么开关导致数据不一致的呢？
问题场景
数据库版本Percona 5.7.22
环境配置
sql_mode: NO_ENGINE_SUBSTITUTION
表结构
CREATE TABLE `dt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`b` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
复现
更新字段的sql 其实很简单update dt set a=a+1 ,b=b+1 where 条件
test [RW] 11:11:38 >select * from dt;
+-------+---------------------+---------------------+
| id | a | b |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:50 | 2020-08-20 12:08:59 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)
test [RW] 11:11:46 >update dt set a=a+1 ,b=b+1 ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
test [RW] 11:12:09 >show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
test [RW] 11:12:17 >select * from dt;
+-------+---------------------+---------------------+
| id | a | b |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:51 | 0000-00-00 00:00:00 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)
我们可以看到 a字段 "2020-08-20 12:08:50"变成"2020-08-20 12:08:51"，时间戳增加1s，符合预期。但是b字段的值从 2020-08-20 12:08:59 变成 0000-00-00 00:00:00，明显不符合预期。这也是导致后面查询某些时间段的数据，导致正常的数据缺失的。
分析
在MySQL 中 对于datetime 和 time类型而言秒级数据的有效值是 0-59，在这个范围内加减都可以，但是结果值超过59秒就会溢出，提示 Data truncated for column 'b'。
在严格模式下,针对 59秒直接+1s的时候，直接报错，而不是像 NO_ENGINE_SUBSTITUTION 时给一个warning值。
test [RW] 11:36:15 >show variables like '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)
test [RW] 11:36:18 >insert into dt(a,b) values('2020-08-20 12:08:50','2020-08-20 12:08:59');
Query OK, 1 row affected (0.00 sec)
test [RW] 11:36:36 >select * from dt;
+-------+---------------------+---------------------+
| id | a | b |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:52 | 0000-00-00 00:00:00 |
| 10032 | 2020-08-20 12:08:50 | 2020-08-20 12:08:59 |
+-------+---------------------+---------------------+
2 rows in set (0.00 sec)
test [RW] 11:36:44 >update dt set a=a+1 ,b=b+1 where id=10032;
ERROR 1292 (22007): Incorrect datetime value: '20200820120860' for column 'b' at row 1
正确的方式是什么呢？合理的利用 data_add() 函数。
test [RW] 11:40:47 >update dt set a=date_add(a,interval 1 second),b=date_add(b,interval 1 second) where id=10032;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
test [RW] 11:41:10 >select * from dt;
+-------+---------------------+---------------------+
| id | a | b |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:52 | 0000-00-00 00:00:00 |
| 10032 | 2020-08-20 12:08:51 | 2020-08-20 12:09:00 |
+-------+---------------------+---------------------+
2 rows in set (0.00 sec)
总结
我想强调的
datetime 合理的时间段是: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
timestamp 合理的时间段是: '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'
在开发设计时间字段的时候，不要设置这个两个时间范围之外的值。
MySQL 做逻辑判断或者运算的时候，判断符号，等号左右两边的值务必保持一致。datetime = datetime +1 本身也是不合理的用法。
最后做个小调查吧 你们的生产环境使用了严格模式的 sql_mode吗
-The End-
本公众号长期关注于数据库技术以及性能优化，故障案例分析，数据库运维技术知识分享，个人成长和自我管理等主题，欢迎扫码关注。
