分析
该错误是由于 MySQL 对字符值是否符合字段要求进行了严格的检查,但是有时候,这个 检查的结果却是错误的。就像下面示例
INSERT INTO cloud_study.study_log(material_id,course_id,type,nginx_time,account_id,chapter_id,session_id,progress,resolution,material_completed,org_id,timestamp,terminal_device,site_id,terminal_type) VALUES('1399980991762034688','1438395190598729728','video','2021-10-20T10:26:51+08:00','1235477994669285376','1438395228364242944','yn90417914xuixyr1634696791343','2','SD',0,'1379248901018058752','1634696810068','iPhone;CPUiPhoneOS10_3_1likeMacOSX','1369096880696819712','wechat')
> 1292 - Incorrect datetime value: '2021-10-20T10:26:51+08:00' for column 'nginx_time' at row 1 > 时间: 0.011s
- 查看SQL_MODE
MariaDB [(none)]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
- 解决
主要是把sql_mode中的strict_trans_tables去掉即可。
set [global | session] variables sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set global sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';