问题描述
MySQL 5.7 的 SQL Modes 对 SQL 的检查更加严格和规范，会出现一些 SQL 兼容性问题。
问题分析
具体常见的问题包含以下几个方面：
1.SQL查询的字段不包含在GROUP BY 语句中(ONLY_FULL_GROUP_BY)
如果SELECT的字段与GROUP BY的字段不匹配，那么就会报错如下:
MySQL [dbtest]> select min(update_time),name,id from tb_author group by name;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbtest.tb_author.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法
需要改写SQL,去掉GROUP BY后不包含的id字段，保证数据统计的准确性
MySQL [dbtest]> select min(update_time),name from tb_author group by name;
+---------------------+-----------+
| min(update_time) | name |
+---------------------+-----------+
| 2021-08-10 22:27:21 | b |
| 2021-08-20 12:27:06 | baihua |
| 2021-08-20 12:27:06 | baihuawen |
| 2021-08-10 22:27:35 | c |
| 2021-08-04 22:27:46 | m |
| 2021-08-10 22:27:06 | z |
+---------------------+-----------+
6 rows in set (0.002 sec)
2.插入的日期为0(NO_ZERO_IN_DATE,NO_ZERO_DATE)
插入的日期时间中有为0的数值
MySQL [dbtest]> insert into tb_author(id,name,update_time) values(8,"dbtest",'0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'update_time' at row 1
MySQL [dbtest]> insert into tb_author(id,name,update_time) values(8,"dbtest",'2022-01-00');
ERROR 1292 (22007): Incorrect datetime value: '2022-01-00' for column 'update_time' at row 1
MySQL [dbtest]> insert into tb_author(id,name,update_time) values(8,"dbtest",'2022-00-01');
ERROR 1292 (22007): Incorrect datetime value: '2022-00-01' for column 'update_time' at row 1
解决方法
插入非零有效日期
3.通过grant创建新用户(NO_AUTO_CREATE_USER)
mysql> grant all on *.* to 'dbmantest'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
解决方法
加上identified by 赋值账户密码（实际也会有warning提示,这种语法后续也会废弃)
mysql> grant all on *.* to 'dbmantest'@'%' identified by 'Dba#$54r';
Query OK, 0 rows affected, 1 warning (0.00 sec)
标准的写法
#1.正常形式
create user dbast@'%' identified by '3eEEDWFSs';
grant all privileges on *.* to 'dbast'@'%' WITH GRANT OPTION; ; #赋予超级权限
4.插入或者更新除数为0的结果(ERROR_FOR_DIVISION_BY_ZERO)
mysql> insert into test(value) values(10/0);
ERROR 1365 (22012): Division by 0
解决方法
该SQL无法进行语法调整，若出现除数为0的情况，需要业务做规避。
5.CREATE或者ALTER TABLE时指定的引擎不存在(NO_ENGINE_SUBSTITUTION)
如未开启，CREATE 将使用默认存储引擎，并有warning提示；ALTER 语句会有warning提示，且引擎不会改变:
mysql> create table test(id int) ENGINE=FEDERATED;
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
解决方法
该SQL无法进行语法调整，生产建议开启并设置默认 default_storage_engine存储引擎为InnoDB。
6.插入不合规的数据
不合规数据包括超长数值，缺省值，数据类型错误的数值等
mysql> create table test2(id int,name varchar(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2 values(1,'there is a error');
ERROR 1406 (22001): Data too long for column 'name' at row 1
解决方法
该SQL无法进行语法调整，需业务层面规避掉不合规数据。
参考文档
[1] https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables
如果您有其他问题，欢迎您联系火山引擎技术支持服务
