MySQL中第二次调用ADDTIME函数为何返回错误结果？

问题描述 #

在MySQL 5.7中使用ADDTIME函数时遇到以下异常：

1. 创建testdates表并插入日期数据；
2. 直接查询表中日期，用ADDTIME(d, "24:00:00")转换为次日日期，结果正常；
3. 基于该查询创建视图v1，查询视图结果正常；
4. 从视图v1中对计算出的d2字段再次调用ADDTIME时，未得到预期的d+2天，反而返回当前日期；但对视图中的原生日期d执行相同计算则结果正常。

操作代码与结果 #

mysql> create table testdates (d DATE);  
Query OK, 0 rows affected (0.02 sec)

mysql> insert into testdates (d) VALUES ("2020-01-01"), ("2021-02-02"), ("2022-03-03");  
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select d, CAST(ADDTIME(d, "24:00:00") AS DATE) from testdates;  
+------------+--------------------------------------+  
| d          | CAST(ADDTIME(d, "24:00:00") AS DATE) |  
+------------+--------------------------------------+  
| 2020-01-01 | 2020-01-02                           |  
| 2021-02-02 | 2021-02-03                           |  
| 2022-03-03 | 2022-03-04                           |  
+------------+--------------------------------------+  
3 rows in set (0.00 sec)  

mysql> create view v1 as select d, CAST(ADDTIME(d, "24:00:00") AS DATE) d2 from testdates;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1;
+------------+------------+
| d          | d2         |
+------------+------------+
| 2020-01-01 | 2020-01-02 |
| 2021-02-02 | 2021-02-03 |
| 2022-03-03 | 2022-03-04 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> select d, d2, CAST(ADDTIME(d2, "24:00:00") AS DATE) from v1;
+------------+------------+---------------------------------------+
| d          | d2         | CAST(ADDTIME(d2, "24:00:00") AS DATE) |
+------------+------------+---------------------------------------+
| 2020-01-01 | 2020-01-02 | 2023-04-18                            |
| 2021-02-02 | 2021-02-03 | 2023-04-18                            |
| 2022-03-03 | 2022-03-04 | 2023-04-18                            |
+------------+------------+---------------------------------------+
3 rows in set (0.00 sec)

原因分析 #

这是MySQL 5.7中ADDTIME函数隐式类型转换的已知bug：

• ADDTIME设计用于处理TIME或DATETIME类型，当传入表原生的DATE字段时，MySQL能正确将其隐式转换为YYYY-MM-DD 00:00:00格式的DATETIME，因此计算正常。
• 但视图中的d2是通过CAST(ADDTIME(d, "24:00:00") AS DATE)生成的，MySQL对视图中这种衍生DATE字段的类型处理逻辑存在异常：无法将其正确转换为DATETIME，反而将DATE字符串视为无效的TIME格式输入。此时ADDTIME会默认使用当前系统时间作为计算基准，加上24:00:00后转成DATE，就得到了执行查询时的当前日期。

该问题在MySQL 8.0版本中已被修复，对视图中的DATE字段调用ADDTIME会正确转换为DATETIME进行计算，得到预期结果。

解决方法 #

方法1：显式转换为DATETIME #

在调用ADDTIME前，将视图中的DATE字段显式转换为DATETIME：

SELECT d, d2, CAST(ADDTIME(CAST(d2 AS DATETIME), "24:00:00") AS DATE) FROM v1;

方法2：改用DATE_ADD函数（推荐） #

DATE_ADD是专门为日期运算设计的函数，不存在类型转换异常问题：

-- 创建视图时使用DATE_ADD替代ADDTIME
CREATE VIEW v1 AS SELECT d, DATE_ADD(d, INTERVAL 1 DAY) d2 FROM testdates;

-- 查询时同样使用DATE_ADD
SELECT d, d2, DATE_ADD(d2, INTERVAL 1 DAY) FROM v1;

内容的提问来源于stack exchange，提问作者jollytall