imcompatiable with sql_mode=only_full_group_by 解决方案

imcompatiable with sql_mode=only_full_group_by 解决方案

---

 

一、背景介绍

在执行 group by 语句对数据进行分组查询过程中，会出现如下报错信息：

如何优化查询SQL，从而避免如上报错呢？ 

 

二、问题原因

"sql_mode=only_full_group_by" 是 MySQL 数据库中的一个严格模式，它要求在使用 GROUP BY 子句时，SELECT 中的列必须是聚合函数或出现在 GROUP BY 子句中。如果你的 SQL 查询不符合这个要求，就会报错。

 

三、解决方案

要优化你的 SQL 查询以适应这个严格模式，可以考虑以下几个方法：

1、调整 SELECT 列

确保你的 SELECT 列中的每一列都是聚合函数，或者出现在 GROUP BY 子句中。这是符合 "only_full_group_by" 要求的一种方式。

示例SQL如下：

SELECT column1, COUNT(column2)
FROM your_table
GROUP BY column1;

 

2、使用聚合函数

将不在 GROUP BY 子句中的列改为相应的聚合函数，如 COUNT、SUM、MAX、MIN 等。

SELECT column1, COUNT(column2) AS count_column2
FROM your_table
GROUP BY column1;

 

3、使用子查询

将不符合 GROUP BY 要求的列放入子查询中，然后再进行主查询。

示例SQL如下：

SELECT column1, subquery.count_column2
FROM (
    SELECT column1, COUNT(column2) AS count_column2
    FROM your_table
    GROUP BY column1
) AS subquery;

 

4、合理使用HAVING子句

如果你的查询中有条件限制，可以使用 HAVING 子句而不是 WHERE 子句，因为 HAVING 在分组后进行过滤。

示例SQL如下：

SELECT column1, COUNT(column2) AS count_column2
FROM your_table
GROUP BY column1
HAVING count_column2 > 10;

 

PS：

也可以通过修改数据库的 sql_mode 取值，解决上述报错问题。

1、临时方式

1）查询 sql_mode 的值

SHOW VARIABLES LIKE "sql_mode";

可以查询到 sql_mode 的值如下：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 

2）修改sql_mode 的值

# 修改全局配置 sql_mode 
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 修改session配置 sql_mode 
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 

2、永久方式

1）修改 MySQL 配置文件 /etc/my.cnf

打开这个 my.cnf 文件找到[mysql]，在 [mysqld] 下设置 sql_mode ，将通过SHOW VARIABLES LIKE "sql_mode";查询出来的 sql_mode 剔除 ONLY_FULL_GROUP_BY 即可。

添加内容如下：

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

 

2）重启 MySQL 服务

systemctl restart mysqld

或者

service mysqld restart