当MYSQL出现时间插入失败的兼容性错误时的解决办法:
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘IntoLineTime’ at row 1
mysql> INSERT INTO testtable(IntoLineTime,StartTime,EndTime) values('0000-00-00 00:00:00','2020-07-01 08:59:43','2020-07-01 09:02:59');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'IntoLineTime' at row 1
mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> set @@sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO testtable(IntoLineTime,StartTime,EndTime) values('0000-00-00 00:00:00','2020-07-01 08:59:43','2020-07-01 09:02:59');
Query OK, 1 row affected (0.02 sec)
以上方法重启后会失效,如果要长期有效必须改配置文件my.cnf
例如:
vi /etc/my.cnf
在[mysqld]下面添加如下列:
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
参考链接:https://blog.csdn.net/weixin_41486034/article/details/106941492