MySQL 5.7 版本 sql_mode=only_full_group_by 问题
1 在 MySQL 环境下执行分组 sql, 如下
mysql> select db_server_name,login_user,count(db_server_name) from `mysql_audit_log` group by login_user;
提示
- ERROR 1055(42000) : Expression#1 of SELECT list is not in GROUP BY clause and contains nonaggregated columncollect_mysql_audit_log.mysql_audit_log.db_server_namewhich is not functionally dependent on colum ns in GROUP BY clause;
- this is incompatible with sql_mode = only_full_group_by
2 解决:
执行 SELECT @@GLOBAL.sql_mode 查看
- mysql > SELECT@@GLOBAL.sql_mode; + -------------------------------------------------------------------------------------------------------------------------------------------+|@@GLOBAL.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 row in set(0.06 sec)
重新设置 sql_mode, 禁用 ONLY_FULL_GROUP_BY 如下设置, 下面设置是临时生效, 如果想永久生效, 请在配置文件中添加配置
- mysql> SET sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
- Query OK, 0 rows affected (0.00 sec)
配置文件中添加配置
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
MySQL 5.7.9 版本 sql_mode=only_full_group_by 问题
来源: http://www.bubuko.com/infodetail-2490384.html