sql_mode解决方案

问题一： "this is incompatible with sql_mode=only_full_group_by"错误解决方案

1、首先查看

select @@GLOBAL.sql_mode;

2、临时性修改

set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

  flush privileges;

3、永久修改

vim /etc/my.cnf

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

问题二：Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘return_create_time’ at row 1

MySQL导入数据库时报错：ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘return_create_time’ at row 1
原因：时间字段的格式为 ‘0000-00-00 00:00:00’，不能使用 no_zero_date 模式

mysql> show variables like 'sql_mode';
±--------------±----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±--------------±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

解决办法：去掉NO_ZERO_DATE

临时解决方案：

mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

永久解决方案：

添加到my.cnf里边

sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';