5.5-5.7 sql不兼容问题总结
参考官方文档:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict
5.5 sql_mode配置:
空
5.7(>= 5.7.8) 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.包含group by的sql:
样例SQL：select id,name,class,max(score) from test group by class;
假设select后的字段与group by 后字段不匹配(聚合函数不算)，在sql_mode不设置ONLY_FULL_GROUP_BY限制条件下可以成功执行，而在5.7中默认设置了ONLY_FULL_GROUP_BY，则会报下面错误:
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法:
改写SQL:
select class,max(score) from test group by class;
2.insert插入不合规数据:
不合规数据包括超长数值，缺省值，数据类型错误的数值等。不包括myisam存储引擎。
以超长数值为样例
在5.5中，未设置STRICT_TRANS_TABLES限制的情况下，插入超长数值会默认截断，依然可以插入成功，不会报异常。而在5.7中则会直接报错:
缺省值样例：
在5.5中，未设置STRICT_TRANS_TABLES限制的情况下，插入NOT NULL字段缺省值，可以成功。int类型字段默认插入0，varchar类型字段默认插入null。而在5.7中则会直接报错：
数据类型错误数值样例：
在5.5中，未设置STRICT_TRANS_TABLES限制的情况下，插入错误数据类型的数据仅会抛出警告，不会报错，将错误类型数据插入int字段会置为0，插入varchar会置为null。而在5.7中则会报错：
解决方法:
该SQL无法进行语法调整，需业务层面针对超长数值，缺省值，数据类型错误的数值做校验判断，提前规避掉不合规数据。
3.通过grant创建新用户:
样例SQL:grant all on *.* to test@’%’;
该SQL在sql_mode为空的5.5环境下可以成功创建用户+赋权限
在sql_mode包含NO_AUTO_CREATE_USER限制条件下的5.7环境中则会报错：
ERROR 1133 (42000): Can't find any matching row in the user table
解决方法：
需要附加用户密码:
mysql> grant all on *.* to test@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
4.插入或更新除数为0的结果：
样例SQL:insert into test1 values(mod(10,0),’qwer’);
该SQL在sql_mode为空的5.5环境下可以成功执行,将该值当做null处理。
在sql_mode包含ERROR_FOR_DIVISION_BY_ZERO限制条件下的5.7环境则会报错:
解决方法:
该SQL无法进行语法调整，若出现除数为0的情况，需要业务做规避。
5.使用不合理的日期格式：
在sql_mode设置了NO_ZERO_IN_DATE，NO_ZERO_DATE限制条件下的5.7环境会报错：
ERROR 1292 (22007): Incorrect date value: '0100-00-00' for column 'datetest' at row 1
不合理的日期格式:0000-00-00,1000-00-00等
经测试，在NO_ZERO_IN_DATE，NO_ZERO_DATE条件下，只有月，日中包含00才会报错。
同理，在创建表时也不能指定时间字段默认值为零值日期：
解决方法：
改为非零日期即可。
