MySQL sql_mode=only_full_group_by 解决

最新推荐文章于 2024-09-29 16:12:54 发布

原创最新推荐文章于 2024-09-29 16:12:54 发布 · 556 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #mysql

当MySQL的sql_mode包含ONLY_FULL_GROUP_BY时，不允许在GROUP BY语句中使用未聚合的列。为了解决这个问题，可以修改my.cnf配置文件，将sql_mode设置为指定模式并重启MySQL服务。另外，可以使用ANY_VALUE函数规避ONLY_FULL_GROUP_BY限制。此外，文章还介绍了如何在已存在数据库中更改sql_mode以及如何通过SELECT...GROUP BY语句进行正确查询。

查看 sql_mode

	select @@GLOBAL.sql_mode;

查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

则下面sql不能正常执行

select a,b,c from table_a group by a,b

只能执行sql select a,b from table_a group by a,b

解决方案

修改 mysql配置文件
my.cnf 中修改以下内容(没有sql_mode 在添加)
需要加入到[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

修改完重启mysql

systemctl restart mysqld

执行sql 语句

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

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

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';

使用any_value函数
ONLY_FULL_GROUP_BY 简单的说来，在此模式下，select后面的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by后面的值。
MySQL也提供了any_value()函数来抑制ONLY_FULL_GROUP_BY 个人推荐

select a,b,any_value(c) from table_a group by a,b