1292 - Incorrect datetime value: ‘2021-10-20T10:26:51+08:00‘ for column ‘nginx

分析

该错误是由于 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';