记录一个sql_mode=only_full_group_by问题

今天打开电脑发现一个服务报错

java.sql.SQLSyntaxErrorException: Expression #50 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ums.tab_sys_area.full_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

百度了下发现是和sql_mode=only_full_group_by不兼容的（即不支持）。这是一种严谨的SQL模式，规定SELECT、HAVING、ORDER后的非聚合字段必须和GROUP BY后的字段保持完全一致。

解决办法

首先查看mysql是否启用ONLY_FULL_GROUP_BY

select @@global.sql_mode

如果查询出来有ONLY_FULL_GROUP_BY，则关闭

在配置文件的[mysqld]下加上

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

my.cnf修改后的内容：

[mysqld]

user=mysql

character-set-server=utf8

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

max_connections=1000

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

最后保存配置，重新启动mysql即可

关于ONLY_FULL_GROUP_BY的具体内容详解可参考以下链接

https://zhuanlan.zhihu.com/p/368440685