我试图在mysql中创建一个事件,但似乎如果结束日期超过20年,它就无法创建它。
今天开始,明天结束工作:
mysql> CREATE EVENT test
-> ON SCHEDULE EVERY 1 DAY
-> STARTS '2020-07-24 13:30:00'
-> ENDS '2020-07-25 13:30:00'
-> DO
-> SELECT 1 FROM DUAL;
Query OK, 0 rows affected (0.00 sec)
从今天开始,50年后结束,不起作用:
mysql> CREATE EVENT test
-> ON SCHEDULE EVERY 1 DAY
-> STARTS '2020-07-24 13:30:00'
-> ENDS '2070-07-25 13:30:00'
-> DO
-> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS
mysql> -- also with different time format
mysql> CREATE EVENT test
-> ON SCHEDULE EVERY 1 DAY
-> STARTS '2020-07-24 13:30:00'
-> ENDS CURRENT_TIMESTAMP + INTERVAL 50 YEAR
-> DO
-> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS
20年后两项工作都没有:
mysql> CREATE EVENT test
-> ON SCHEDULE EVERY 1 DAY
-> STARTS '2020-07-24 13:30:00'
-> ENDS '2040-07-25 13:30:00'
-> DO
-> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS
mysql> CREATE EVENT test
-> ON SCHEDULE EVERY 1 DAY
-> STARTS '2020-07-24 13:30:00'
-> ENDS CURRENT_TIMESTAMP + INTERVAL 20 YEAR
-> DO
-> SELECT 1 FROM DUAL;
ERROR 1543 (HY000): ENDS is either invalid or before STARTS
15年是可以接受的:
mysql> CREATE EVENT test
-> ON SCHEDULE EVERY 1 DAY
-> STARTS '2020-07-24 13:30:00'
-> ENDS CURRENT_TIMESTAMP + INTERVAL 15 YEAR
-> DO
-> SELECT 1 FROM DUAL;
Query OK, 0 rows affected (0.01 sec)
有人知道为什么会发生这种事吗?
MySQL事件时间戳使用32位UNIX时间戳格式。
https://dev.mysql.com/doc/refman/8.0/en/datetime.html说:
TIMESTAMP数据类型用于同时包含日期和时间部分的值。TIMESTAMP的范围为协调世界时1970-01-01 00:00:01'到协调世界时2038-01-19 03:14:07'。
此缺陷中也发现了此限制:https://bugs.mysql.com/bug.php?id=16396