SQL 报错 sql_mode=only_full_group_by 问题

SQL 报错 sql_mode=only_full_group_by 问题

原因分析

一、原理层面
这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题：
mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。
很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

二、sql层面
在sql执行时，出现该原因，简单来说就是：
由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，
并且select 的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，那么这条sql查询是被mysql认为非法的，会报错误…

解决

1. 查看sql_mode的语句

select @@GLOBAL.sql_mode;

2. 通过sql语句暂时性修改sql_mode, 去掉ONLY_FULL_GROUP_BY，重新设置值

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据库下执行：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

问题

重启mysql数据库服务之后，ONLY_FULL_GROUP_BY还会出现，所以这只是暂时性的。

永久生效修改

以win10系统为例，到数据库安装的目录，找到my.ini文件，进行修改，在[mysqld]的下面添加：

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

一定要在[mysqld]的下面添加，否则重启mysql 将会失败。

修改好后，以管理员身份运行命令提示符。

进行重启