极客笔记mysql only_full_group_by详解
简介
在使用 MySQL 数据库时，我们经常会遇到 ONLY_FULL_GROUP_BY 的错误提示。本文将详细解释 ONLY_FULL_GROUP_BY 模式的含义和作用，并提供示例代码以帮助读者更好地理解。
什么是 ONLY_FULL_GROUP_BY 模式？
MySQL 中的 ONLY_FULL_GROUP_BY 模式是一种表示数据库以严格的方式处理 GROUP BY 语句的设置。在这种模式下，当使用 GROUP BY 进行分组查询时，MySQL 引擎会检查 SELECT 语句中的任何列是否具有以下特性：
- 在
GROUP BY子句中列出，或者 - 在
SELECT子句中使用的聚合函数（如SUM、COUNT、MAX等）的参数。
如果有任何列不属于上述两种情况，MySQL 就会抛出 1055 error: Expression #X of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_name.column_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 这个错误。
简而言之，ONLY_FULL_GROUP_BY 模式要求 SELECT 语句中的列要么出现在 GROUP BY 子句中，要么是一个聚合函数的参数。
默认模式和启用 ONLY_FULL_GROUP_BY 模式
在 MySQL 5.7 版本之前，MySQL 的默认模式是关闭的，即不启用 ONLY_FULL_GROUP_BY 模式。这意味着，GROUP BY 查询可能会返回不符合 SQL 标准的结果，也就是会不合理地产生聚合。
从 MySQL 5.7 版本开始，默认模式变为启用 ONLY_FULL_GROUP_BY 模式，以更好地符合 SQL 标准，并避免产生错误的结果。当我们遇到 1055 error 错误时，通常是因为我们在使用 GROUP BY 时没有遵循 ONLY_FULL_GROUP_BY 模式的要求。
示例代码
为了更好地理解 ONLY_FULL_GROUP_BY 模式的使用和作用，我们将提供一些示例代码。假设有以下一张名为 orders 的表，用于存储顾客的订单信息：
| order_id | customer_name | order_date | quantity | price |
|---|---|---|---|---|
| 1 | Alice | 2021-01-01 | 10 | 20 |
| 2 | Bob | 2021-01-02 | 5 | 15 |
| 3 | Alice | 2021-01-03 | 8 | 25 |
| 4 | Charlie | 2021-01-04 | 12 | 30 |
| 5 | Alice | 2021-01-05 | 6 | 18 |
示例 1：查询每个顾客的订单总数
我们想要查询每个顾客的订单总数，可以使用以下 SQL 语句：
SELECT customer_name, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_name;
在启用 ONLY_FULL_GROUP_BY 模式的情况下，执行以上 SQL 语句会得到正确的结果：
| customer_name | order_count |
|---|---|
| Alice | 3 |
| Bob | 1 |
| Charlie | 1 |
如果在禁用 ONLY_FULL_GROUP_BY 模式的情况下执行上述 SQL 语句，MySQL 引擎将会返回不可预测的结果，可能会显示错误的订单总数。
示例 2：查询每个顾客的订单总金额
我们希望查询每个顾客的订单总金额，可以使用以下 SQL 语句：
SELECT customer_name, SUM(quantity * price) AS total_amount
FROM orders
GROUP BY customer_name;
同样地，在启用 ONLY_FULL_GROUP_BY 模式的情况下，执行以上 SQL 语句会得到正确的结果：
| customer_name | total_amount |
|---|---|
| Alice | 320 |
| Bob | 75 |
| Charlie | 360 |
如果在禁用 ONLY_FULL_GROUP_BY 模式的情况下执行上述 SQL 语句，MySQL 引擎将会返回不可预测的结果，可能会显示错误的订单总金额。
示例 3：查询每个顾客的订单数量和平均价格
我们想要查询每个顾客的订单数量和平均价格，可以使用以下 SQL 语句：
SELECT customer_name, COUNT(order_id) AS order_count, AVG(price) AS avg_price
FROM orders
GROUP BY customer_name;
同样地，在启用 ONLY_FULL_GROUP_BY 模式的情况下，执行以上 SQL 语句会得到正确的结果：
| customer_name | order_count | avg_price |
|---|---|---|
| Alice | 3 | 21 |
| Bob | 1 | 15 |
| Charlie | 1 | 30 |
如果在禁用 ONLY_FULL_GROUP_BY 模式的情况下执行上述 SQL 语句，MySQL 引擎将会返回不可预测的结果，可能会显示错误的订单数量和平均价格。
如何启用和禁用 ONLY_FULL_GROUP_BY 模式
要启用和禁用 ONLY_FULL_GROUP_BY 模式，可以通过修改 MySQL 的 sql_mode 参数来实现。以下是一些常用的方法：
- 修改全局参数：编辑 MySQL 配置文件（如
my.cnf或my.ini），将sql_mode参数设置为所需的模式。例如，将sql_mode设置为"ONLY_FULL_GROUP_BY"表示启用ONLY_FULL_GROUP_BY模式。 -
修改会话参数：在 MySQL 会话中执行以下 SQL 语句：
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY';这将在当前会话中启用
ONLY_FULL_GROUP_BY模式。
无论使用哪种方法，将 sql_mode 设置为空字符串（''）将禁用所有模式，包括 ONLY_FULL_GROUP_BY。
总结
本文详细解释了 ONLY_FULL_GROUP_BY 模式的含义和作用，以及在 MySQL 中启用和禁用 ONLY_FULL_GROUP_BY 模式的方法。我们了解到，在启用 ONLY_FULL_GROUP_BY 模式的情况下，使用 GROUP BY 进行分组查询时需要遵循一定的规则，以产生正确的结果。
当我们在使用 MySQL 数据库时遇到 1055 error 错误时，可以通过检查是否启用了 ONLY_FULL_GROUP_BY 模式来解决问题。最后，为了确保数据库查询的准确性和合理性，建议始终启用 ONLY_FULL_GROUP_BY 模式。