1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '2024.tb_order_detail.cnt' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

<think>我们正在解决MySQL中因sql_mode包含ONLY_FULL_GROUP_BY导致的GROUP BY查询错误。错误信息通常为：Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。 根据引用内容，错误的原因是：在SELECT列表中出现了非聚合列，而这些列没有在GROUP BY子句中列出，且与GROUP BY子句中的列不存在函数依赖关系（即不是GROUP BY列的子集或函数依赖列）。MySQL的sql_mode设置为ONLY_FULL_GROUP_BY时，要求SELECT列表中的非聚合列必须出现在GROUP BY子句中，或者是聚合函数的参数，或者与GROUP BY列有函数依赖关系（如主键或唯一索引）。 解决方案有以下几种： 1. 修改sql_mode，移除ONLY_FULL_GROUP_BY。 这是最直接的解决方法，但可能会降低查询的严谨性，因为允许SELECT列表中出现不在GROUP BY子句中的非聚合列，此时MySQL会随机选择一个值（这可能导致数据不一致）。 具体操作： - 临时修改（当前会话有效）： ```sql SET SESSION 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 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'; ``` - 永久修改：修改MySQL配置文件（如my.ini或my.cnf），在[mysqld]部分添加： ``` sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ``` 注意：不同MySQL版本可能默认的sql_mode不同，建议先查看当前的sql_mode（`SELECT @@GLOBAL.sql_mode;`），然后去掉ONLY_FULL_GROUP_BY，再写入配置文件。修改后重启MySQL服务。 2. 修改查询语句，使其符合ONLY_FULL_GROUP_BY的要求。 有两种方式： a) 将SELECT列表中的非聚合列都添加到GROUP BY子句中。 例如，原查询： ```sql SELECT name, address, MAX(age) FROM person GROUP BY name; ``` 错误原因：address不在GROUP BY子句中，且不是聚合函数。修改后： ```sql SELECT name, address, MAX(age) FROM person GROUP BY name, address; ``` 注意：这样可能会改变分组逻辑，得到不同的结果。 b) 对非聚合列使用聚合函数（如MAX、MIN、AVG等）或函数依赖列（如果存在函数依赖关系，如主键或唯一索引，但MySQL 5.7及以上版本要求显式指定）。 例如： ```sql SELECT name, MAX(address) as address, MAX(age) FROM person GROUP BY name; ``` 这样处理可能不符合业务逻辑，因为address的最大值可能没有实际意义。 3. 使用ANY_VALUE()函数（MySQL 5.7及以上版本提供）。 如果某个非聚合列不需要进行聚合，且你接受任意一个值，可以使用ANY_VALUE()函数： ```sql SELECT name, ANY_VALUE(address) as address, MAX(age) FROM person GROUP BY name; ``` 这样MySQL会随机选择一个address值，避免错误。 4. 通过修改表结构，确保非聚合列与GROUP BY列有函数依赖关系（例如，如果GROUP BY的列是主键或唯一索引，那么其他列就函数依赖于该列）。但这种情况较少，因为通常GROUP BY的列不是主键。 根据引用[4]和[5]，使用小皮面板（PHPStudy）等工具可以方便地修改sql_mode，通过取消勾选ONLY_FULL_GROUP_BY选项实现。 总结：推荐优先考虑修改查询语句（使用聚合函数或ANY_VALUE()）以符合ONLY_FULL_GROUP_BY的要求，因为这样能保证数据的一致性。如果业务允许，也可以修改sql_mode，但需注意潜在的数据不一致风险。 注意：在MySQL 8.0中，默认的sql_mode包括ONLY_FULL_GROUP_BY，因此需要特别注意。 下面是一个示例，展示如何修改查询语句： 原错误查询： ```sql SELECT region, product, SUM(sales) FROM sales_data GROUP BY region; ``` 修改方法1（将product加入GROUP BY）： ```sql SELECT region, product, SUM(sales) FROM sales_data GROUP BY region, product; ``` 修改方法2（使用ANY_VALUE）： ```sql SELECT region, ANY_VALUE(product) as product, SUM(sales) FROM sales_data GROUP BY region; ``` 修改方法3（对product使用聚合函数，如果业务允许）： ```sql SELECT region, MAX(product) as product, SUM(sales) FROM sales_data GROUP BY region; ``` 请根据实际业务需求选择合适的解决方案。 §§相关问题§§ 1. 什么是SQL的ONLY_FULL_GROUP_BY模式？它有什么作用？ 2. 在MySQL中，如何查看和修改当前的sql_mode设置？ 3. 除了ONLY_FULL_GROUP_BY，MySQL的sql_mode还有哪些常用选项？它们的作用是什么？ 4. 在GROUP BY查询中，函数依赖（functional dependency）是什么？MySQL如何利用它？ 5. 使用ANY_VALUE()函数有什么优缺点？</think>### 解决 `ONLY_FULL_GROUP_BY` 导致的 SQL 错误 #### 错误原因 当 MySQL 的 `sql_mode` 包含 `ONLY_FULL_GROUP_BY` 时（MySQL 5.7+ 默认启用），`GROUP BY` 查询需满足严格规则： **SELECT 列表中的非聚合列必须出现在 GROUP BY 子句中，或是函数依赖列**。否则会报错： ```sql Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ``` --- ### 解决方案（任选其一） #### 方法 1：修改 SQL 查询（推荐） 确保 SELECT 中的**非聚合列**都包含在 `GROUP BY` 中，或使用聚合函数包裹： ```sql -- 原始错误查询（假设 region 未在 GROUP BY 中） SELECT region, product, SUM(sales) FROM sales_data GROUP BY product; -- 修复方案 1：将 region 加入 GROUP BY SELECT region, product, SUM(sales) FROM sales_data GROUP BY region, product; -- ✅ 所有非聚合列均被分组 -- 修复方案 2：对 region 使用聚合函数 SELECT MAX(region) AS region, product, SUM(sales) -- ✅ 使用 MAX() 聚合 FROM sales_data GROUP BY product; ``` #### 方法 2：使用 `ANY_VALUE()` 函数（MySQL 5.7+） 对无需聚合的列使用 `ANY_VALUE()`，明确表示接受任意值： ```sql SELECT ANY_VALUE(region) AS region, product, SUM(sales) -- ✅ 显式声明任意值 FROM sales_data GROUP BY product; ``` #### 方法 3：临时禁用 `ONLY_FULL_GROUP_BY`（谨慎） 通过会话或全局设置修改 `sql_mode`： ```sql -- 当前会话生效 SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- 全局生效（需管理员权限） SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; ``` #### 方法 4：永久修改配置（配置文件） 1. 编辑 MySQL 配置文件（如 `my.cnf` 或 `my.ini`） 2. 在 `[mysqld]` 部分添加： ```ini sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" ``` 3. 重启 MySQL 服务生效。 #### 方法 5：通过管理工具（如 PHPMyAdmin） 1. 执行查询检查当前模式： ```sql SELECT @@GLOBAL.sql_mode; ``` 2. 在工具设置中移除 `ONLY_FULL_GROUP_BY`（如小皮面板的 SQL 模式设置）[^4]。 --- ### 建议优先级 1. **首选方法 1 或 2**：保证查询符合 SQL 标准，避免数据歧义。 2. 若需快速修复且能接受非严格分组，用方法 3 或 4（注意可能引发数据不一致）。 3. 生产环境修改 `sql_mode` 前需评估业务影响。 > **关键提示**：`ONLY_FULL_GROUP_BY` 的设计是为了防止非确定性查询结果（如未明确分组的列可能返回随机值）。禁用后需确保业务逻辑允许此类行为[^5]。 ---