1. 自营销首页
  2. 技术

案例| +1s导致的故障

案例| +1s导致的故障

该图片由Simon Steinberger在Pixabay上发布

前言

本周有一个业务开发同学要刷数据将 datetime 类型的字段增加1s,但是部分字段的内容变成了 “0000-00-00 00:00:00” 导致相关业务查询数据不一致。具体开发怎么操作的呢?又触发了什么开关导致数据不一致的呢?

问题场景

数据库版本Percona 5.7.22

环境配置

sql_modeNO_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: 1test [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

来源:yangyidba,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/50826

发表评论

电子邮件地址不会被公开。 必填项已用*标注

侵权联系
分享本页
返回顶部