MySQL时间加减的正确打开方式

一、背景介绍


业务会有这样的需求：时间字段需要加1或减1秒。
研发sql：update table set time = time + 1 where id=1; 
看似好像挺对的，但是偶尔会出现不是想要的结果。


二、模拟测试


新建一个表test1，有3条记录如下，执行+1操作：
CREATE TABLE `test1` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Type` smallint(6) DEFAULT '0',
  `Status` smallint(6) DEFAULT '0',
  `CreateTime` datetime DEFAULT NULL,
  `ModifyTime` timestamp DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


> select CreateTime,ModifyTime from test1;
+-+-------------+-------------+
| Id | CreateTime      | ModifyTime       |
+-+-------------+-------------+
|  1 | 2017-08-01 18:30:59 | 2017-08-01 18:30:59 |
|  2 | 2017-08-01 18:31:01 | 2017-08-01 18:31:01 |
|  3 | 2017-08-01 18:31:02 | 2017-08-01 18:31:02 |
+-+-------------+-------------+

> update test1 set CreateTime=CreateTime+1,ModifyTime=ModifyTime+1;

> select * from test1;
+-+-------------+-------------+
| Id | CreateTime      | ModifyTime       |
+-+-------------+-------------+
|  1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  2 | 2017-08-01 18:31:02 | 2017-08-01 18:31:02 |
|  3 | 2017-08-01 18:31:03 | 2017-08-01 18:31:03 |
+-+-------------+-------------+

继续测试：
> update test1 set CreateTime=CreateTime+55,ModifyTime=ModifyTime+105;

> select CreateTime,ModifyTime from test1;
+---------------------+---------------------+
| CreateTime            | ModifyTime          |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2000-01-05 00:00:00 |
| 2017-08-01 18:31:57 | 2017-08-01 18:32:07 |
| 2017-08-01 18:31:58 | 2017-08-01 18:32:08 |

+---------------------+---------------------+

CreateTime+55,ModifyTime+105后，并不是我们想的逢整进位的关系。
测试后我们看到59秒的时候加1秒全部变成了0000-00-00 00:00:00，而其他是正确的，此时我们会觉得是不是跟逢整进位有关系，59秒的时候再加上1秒进位1分钟，结果却变成了0000-00-00 00:00:00，这是为什么？


三、问题分析

> select ModifyTime from test1 limit 1;                      
+----------------+             
| ModifyTime            |             
+----------------+             
| 2017-08-01 18:30:59 |             
+----------------+   

> update test1 set ModifyTime = ModifyTime + <n>; 
                                                                                                 
其实只要我们知道datatime类型以'YYYY-MM-DD HH:MM:SS'的形式来显示的，就知道原因了。
例如：
n=61，会转换成 '0000-00-00 00-00-61'；
n=101，会转换成 '0000-00-00 00-01-01'；
n=65535，会转换成 '0000-00-00 06-55-35'；
因为秒只能是0~59，不会有大于59秒的时候存在，如果大于59属于异常，会初始化成'0000-00-00 00-00-00'状态。分钟也一样。
所以如果此时秒正好为0：
当1<=n<60时，可以正常相加；
当60<=n<100时，超过59秒属于异常，初始化成'0000-00-00 00-00-00'；
当n=100时，会转换成 '0000-00-00 00-01-00'，也就是1分钟，如果此时为59分，也会初始化成'0000-00-00 00-00-00'；
以此类推，所以并不是所有的都会成功，也不是所有的都会失败，因为这种方式本来就不符合时间加减规范，其他日期类型同理。 所以要杜绝此类问题，研发就不能偷懒，必须使用时间函数。
 
四、正确方式

为日期加上一个时间间隔：date_add()
date_add(@dt, interval 1 microsecond); -加1毫秒
date_add(@dt, interval 1 second); -加1秒 
date_add(@dt, interval 1 minute); -加1分钟  
date_add(@dt, interval 1 hour); -加1小时  
date_add(@dt, interval 1 day); -加1天 
date_add(@dt, interval 1 week); -加1周  
date_add(@dt, interval 1 month); -加1月  
date_add(@dt, interval 1 quarter); -加1季  
date_add(@dt, interval 1 year); -加1年  
为日期减去一个时间间隔：date_sub()，格式同date_add()

改写后：
> update test1 set CreateTime=date_add(CreateTime, interval 1 second),ModifyTime=date_add(ModifyTime, interval 1 second);

> select * from test1;
+-+-------------+-------------+
| Id | CreateTime      | ModifyTime       |
+-+-------------+-------------+
|  1 | 2017-08-01 18:31:00 | 2017-08-01 18:31:00 |
|  2 | 2017-08-01 18:31:02 | 2017-08-01 18:31:02 |
|  3 | 2017-08-01 18:31:03 | 2017-08-01 18:31:03 |
+-+-------------+-------------+