ONLY_FULL_GROUP_BY 问题解决

一、SQL_MODE

首先让我介绍 的概念 SQL_MODE 。

MySQL 可以使用影响查询和验证检查的语法的不同 SQL 模式工作。 基于变量 的配置值 sql_mode ， 表示查询可以有效并定期执行或可以收到验证错误而无法执行。

MySQL 的最旧版本让用户习惯于编写语义不正确的查询，因为它被设计为在“宽容模式”下工作。 无论 SQL 标准合规性或语义规则如何，用户都可以编写任何类型的语法有效查询。 这是一个坏习惯，通过引入 sql_mode 来指示 MySQL 以更严格的方式工作以进行查询验证。

一些用户不知道此功能，因为默认值没有那么严格。 从 5.7 开始，默认值更加严格，这也是一些用户在迁移到 5.7 或 8.0 后遇到意外查询失败问题的原因。

二、ONLY_FULL_GROUP_BY 问题

让我们关注迁移到 5.7 或 8.0 时最常见的错误原因。 正如我们所说，5.7 的默认 SQL 模式比 5.6 更严格，因此它适用于 8.0。 当您升级 MySQL 并复制旧的 该 my.cnf 文件时， 文件没有 的特定设置，这是 sql_mode 正确的 变量 。 所以，请注意。

让我们创建一个示例表来存储我们网站网页上的点击次数。 我们想记录页面名称和注册用户的 id。

第一种，sql_mode设置为宽容模式

# let's turn the sql mode to "forgiving"
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT page_url, user_id, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+---------+--------+
| page_url          | user_id | visits |
+-------------------+---------+--------+
| /index.html       |       1 |      3 |
| /news.php         |       1 |      2 |
| /store_offers.php |       3 |      2 |
| /faq.html         |       1 |      1 |
+-------------------+---------+--------+
4 rows in set (0.00 sec)

第二种，sql_mode设置为 ONLY_FULL_GROUP_BY

mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT page_url, user_id, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'test.web_log.user_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

三、解决方案 1 – 重写查询

mysql> SELECT page_url, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------+
| page_url          | visits |
+-------------------+--------+
| /index.html       |      3 |
| /news.php         |      2 |
| /store_offers.php |      2 |
| /faq.html         |      1 |
+-------------------+--------+

四、解决方案 2 – 回到宽容模式

#set the complete "forgiving" mode
mysql> SET GLOBAL sql_mode='';

# alternatively you can set sql mode to the following
mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';

五、解决方案 3 – 使用聚合函数

mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';

mysql> SELECT page_url, MAX(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------+--------+
| page_url          | MAX(user_id) | visits |
+-------------------+--------------+--------+
| /index.html       |            3 |      3 |
| /news.php         |            2 |      2 |
| /store_offers.php |            3 |      2 |
| /faq.html         |            1 |      1 |
+-------------------+--------------+--------+

mysql> SELECT page_url, GROUP_CONCAT(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+-----------------------+--------+
| page_url          | GROUP_CONCAT(user_id) | visits |
+-------------------+-----------------------+--------+
| /index.html       |                 1,2,3 |      3 |
| /news.php         |                   1,2 |      2 |
| /store_offers.php |                   3,2 |      2 |
| /faq.html         |                     1 |      1 |
+-------------------+-----------------------+--------+

六、结论

我个人更喜欢解决方案 1，因为它迫使您编写符合 SQL-92 的查询。 遵循标准通常被认为是最佳实践。

原文：https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/