ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 关于 only_full_group_by 限制 [打印本页]
作者: 八卦阵    时间: 2022-8-21 19:02
标题: MySQL 关于 only_full_group_by 限制
先上结论
如果 only_full_group_by 被启用，那么在查询时，如果某个列不在group by 列表中，此时如果不对该列进行聚合处理，则该列不能出现在 select 列表，having 条件中及order by 列表中
MySQL 8.0 默认启用了sql_mode，我们可以通过 select @@session.sql_mode 查看会话中的 sql_mode 配置。
  1. mysql> SELECT @@session.sql_mode;
  2. +-----------------------------------------------------------------------------------------------------------------------+
  3. | @@session.sql_mode
  4. |
  5. +-----------------------------------------------------------------------------------------------------------------------+
  6. | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
  7. +-----------------------------------------------------------------------------------------------------------------------+
  8. 1 row in set (0.00 sec)
复制代码
有这么一张表
  1. CREATE TABLE `mytable`
  2. (
  3.     `id` int unsigned NOT NULL,
  4.     `a`  varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  5.     `b`  int                                    DEFAULT NULL,
  6.     PRIMARY KEY (`id`)
  7. ) ENGINE = InnoDB
  8.   DEFAULT CHARSET = utf8mb4
  9.   COLLATE = utf8mb4_general_ci;
  11. INSERT INTO mytable
  12. VALUES (1, 'abc', 1000),
  13.        (2, 'abc', 2000),
  14.        (3, 'def', 4000);
复制代码
当我们执行的 SQL 语句包含聚合函数时，MYSQL 提示需要使用 GROUP BY 进行分组。
  1. mysql> SELECT a,SUM(b) FROM mytable;
  2. ERROR 1140 (42000): In aggregated query without GROUP BY,
  3. expression #1 of SELECT list contains nonaggregated column 'study.mytable.a';
  4. this is incompatible with sql_mode=only_full_group_by
  5. 如果我们关掉 only_full_group_by 限制，SQL 语句就正常执行了，但又没有完全正常执行。
  7. mysql> SET sql_mode = '';
  8. Query OK, 0 rows affected (0.00 sec)
  10. mysql> SELECT a,SUM(b) FROM mytable;
  11. +------+--------+
  12. | a    | SUM(b) |
  13. +------+--------+
  14. | abc  |   7000 |
  15. +------+--------+
  16. 1 row in set (0.00 sec)
复制代码
可以看到，虽然我们得到了 SUM(b) 的值为 7000 是期望的，但是 a 的值为 abc 不是我们期望的。
MySQL 8.0 里的文档提到这么一句话
the query is processed by treating all rows as a single group, but the value selected for each named column is nondeterministic
在这个例子中，a 的值就是不确定的
当 WHERE 过滤条件中包含了 SELECT 列表中全部非聚合列的字段，则在开启 only_full_group_by 下也可以正常工作
  1. In this case, every such column must be limited to a single value in theWHEREclause, and all such limiting conditions must be joined by logicalAND
  2. mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
  3. Query OK, 0 rows affected (0.01 sec)
  6. mysql> SELECT a,SUM(b) FROM mytable;
  7. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'study.mytable.a'; this is incompatible with sql_mode=only_full_group_by
  8. mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
  9. +------+--------+
  10. | a    | SUM(b) |
  11. +------+--------+
  12. | abc  |   3000 |
  13. +------+--------+
  14. 1 row in set (0.00 sec)
  16. mysql> SELECT * FROM mytable1;
  17. +----+------+------+-------+
  18. | id | a    | b    | c     |
  19. +----+------+------+-------+
  20. |  1 | abc  | qrs  |  1000 |
  21. |  2 | abc  | tuv  |  2000 |
  22. |  3 | def  | qrs  |  4000 |
  23. |  4 | def  | tuv  |  8000 |
  24. |  5 | abc  | qrs  | 16000 |
  25. |  6 | def  | tuv  | 32000 |
  26. +----+------+------+-------+
  27. 6 rows in set (0.00 sec)
  29. mysql> SELECT a, b, SUM(c) FROM mytable1 WHERE a = 'abc' OR b = 'qrs';
  30. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'study.mytable1.a'; this is incompatible with sql_mode=only_full_group_by
  31. mysql> SELECT a, b, SUM(c) FROM mytable1 WHERE a = 'abc' AND b = 'qrs';
  32. +------+------+--------+
  33. | a    | b    | SUM(c) |
  34. +------+------+--------+
  35. | abc  | qrs  |  17000 |
  36. +------+------+--------+
  37. 1 row in set (0.00 sec)
复制代码
这种方式可以理解为通过条件限制确定了分组条件。因为没有指名分组时，MySQL 将所有字段视为一个组处理。
在开启 only_full_group_by 限制时，也可以通过 ANY_VALUE 函数，使MySQL 正常执行语句，显而易见的是，我们得到的值是不确切的。
  1. mysql> SELECT a,SUM(b) FROM mytable;
  2. ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'study.mytable.a'; this is incompatible with sql_mode=only_full_group_by
  3. mysql> SELECT ANY_VALUE(a),SUM(b) FROM mytable;
  4. +--------------+--------+
  5. | ANY_VALUE(a) | SUM(b) |
  6. +--------------+--------+
  7. | abc          |   7000 |
  8. +--------------+--------+
  9. 1 row in set (0.00 sec)
复制代码
综上，在使用聚合函数的场景中，使用 GROUP BY 进行分组可以确保逻辑严谨性。
推荐阅读：
https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4