--- title: MYSQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于MYSQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项相关的常见问题和使用技巧，帮助您快速解决MYSQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # MYSQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项 更新时间：2026-05-19 08:21 适用版本： V1.4.x、V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x 内容类型：Troubleshoot ## 问题现象 实际业务 SQL 执行 `select count(1)` 和 `select tb.* from` 两条 SQL 对相同的表做查询结果集不一致，预期 `select tb.* from` 查询结果为 1 行，实际 `select count(1)` 的结果也应该是 1 行。 ![image01](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20250318things-note-when-group-by01.png) select tb.* from，如下所示。 ![image02](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20250318things-note-when-group-by02.png) ```shell select count(1) 计划 ===================================================== |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| ----------------------------------------------------- |0 |SCALAR GROUP BY | |1 |108 | |1 |└─SUBPLAN SCAN |t |4 |108 | |2 | └─HASH GROUP BY | |4 |108 | |3 | └─TABLE FULL SCAN|t1 |42 |96 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)(0x7fa353882010)]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT(*)(0x7fa353882010)]) 1 - output(nil), filter(nil), rowset=256 access(nil) 2 - output([1]), filter([DATE_FORMAT(t1.payTime(0x7fa35386be30), '%Y-%m')(0x7fa35389d950) >= '2024-10'(0x7fa35389e1c0)], [DATE_FORMAT(t1.payTime(0x7fa35386be30), '%Y-%m')(0x7fa35389d950) <= '2024-11'(0x7fa35389eb10)]), rowset=256 group([t1.recoverParkId(0x7fa353868500)], [t1.escapeParkId(0x7fa353864d20)]), agg_func(nil) 3 - output([t1.escapeParkId(0x7fa353864d20)], [t1.recoverParkId(0x7fa353868500)], [DATE_FORMAT(t1.payTime(0x7fa35386be30), '%Y-%m')(0x7fa35389d950)]), filter([t1.recoverParkId(0x7fa353868500) != t1.escapeParkId(0x7fa353864d20)(0x7fa35386a5e0)], [t1.state(0x7fa353869a50) = 3(0x7fa353869300)]), rowset=256 access([t1.escapeParkId(0x7fa353864d20)], [t1.recoverParkId(0x7fa353868500)], [t1.state(0x7fa353869a50)], [t1.payTime(0x7fa35386be30)]), partitions(p0) is_index_back=false, is_global_index=false, filter_before_indexback[false,false], range_key([t1.id(0x7fa353883b00)]), range(MIN ; MAX)always true ``` ```shell SELECT count(1) FROM ( select recoverMonth, recoverParkName, escapeParkName, payCount, payinAmount, startDate, endDate FROM ( SELECT t.recoverMonth, t.escapeParkName, t.payinAmount, t.recoverParkName, t.payCount, startDate, endDate FROM ( SELECT DATE_FORMAT(t1.payTime, '%Y-%m') AS recoverMonth, t2.lot_name AS escapeParkName, t3.lot_name AS recoverParkName, round(sum(t1.payinAmount) / 100, 2) AS payinAmount, count(*) AS payCount, DATE_FORMAT( LAST_DAY(DATE_SUB(t1.payTime, INTERVAL 1 MONTH)), '%Y-%m-%d' ) AS startDate, DATE_FORMAT( LAST_DAY(t1.payTime) - INTERVAL 1 DAY, '%Y-%m-%d' ) AS endDate FROM t1 LEFT JOIN t2 ON t1.escapeParkId = t2.lot_id LEFT JOIN t3 ON t1.recoverParkId = t3.lot_id WHERE t1.state = 3 AND t1.recoverParkId != t1.escapeParkId GROUP BY t1.recoverParkId, t1.escapeParkId ) t WHERE 1 = 1 AND recoverMonth between '2024-10' and '2024-11' ) as a ) as tb ``` ## 关键信息 OceanBase 数据库 MySQL 兼容 MySQL 模式中 GROUP BY 不包含所有的非聚合字段时的注意事项，OceanBase 数据库默认的 sql mode 中是没增加约束查询必须是聚合的列所以执行不报错。 ## 问题原因 主要是 SQL 中有个子查询中的 GROUP BY 写法不标准导致的执行结果不一致，这个查询每一个（recoverParkId, escapeParkId）GROUP BY 分组里，recoverMonth，escapeParkName，recoverParkName 都是返回分组内的某一行数据，SQL 引擎只能保证这三个投影项投出去的都是分组里确实存在的数据，但无法保证具体返回哪一行数据。 ![image03](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20250318things-note-when-group-by03.png) 举例说明。 ```shell CREATE TABLE `student` ( `sno` varchar(20) NOT NULL, `sname` varchar(20) DEFAULT NULL, `ssex` varchar(20) DEFAULT NULL, `sage` int(11) DEFAULT NULL, `sdept` varchar(20) DEFAULT NULL, PRIMARY KEY (`sno`) ) ``` 下面是 OceanBase 数据库 MySQL 模式和原生 MYSQL 的对比，OceanBase 数据库默认的 sql mode 中是没增加约束查询必须是聚合的列所以执行不报错。 - OceanBase 数据库 MySQL 模式。 ```shell -- OceanBase 数据库 MySQL 模式 -- 非严格模式 OceanBase(root@test)> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname; Empty set (0.16 sec) OceanBase(root@test)> explain SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname; +---------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------+ | ==================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------- | | |0 |HASH GROUP BY | |1 |5 | | | |1 |└─TABLE FULL SCAN|student|1 |4 | | | ==================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([student.sname], [student.sdept], [T_FUN_MAX(student.sage)]), filter(nil), rowset=16 | | group([student.sname]), agg_func([T_FUN_MAX(student.sage)]) | | 1 - output([student.sname], [student.sdept], [student.sage]), filter(nil), rowset=16 | | access([student.sname], [student.sdept], [student.sage]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([student.sno]), range(MIN ; MAX)always true | +---------------------------------------------------------------------------------------------------+ 14 rows in set (0.01 sec) OceanBase(root@test)> show variables like "%sql_mode%"; +---------------+-------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------+ | sql_mode | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER | +---------------+-------------------------------------------------------+ 1 row in set (0.02 sec) -- 增加约束 ONLY_FULL_GROUP_BY 该设置则约束查询必须是聚合的列 OceanBase(root@test)> set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER'; Query OK, 0 rows affected (0.00 sec) OceanBase(root@test)> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname; ERROR 1055 (42000): nonaggregated column 'test.s.sno' is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by -- 去掉 sdept 列，投影列中就不存在非聚合的列，所以正常执行 OceanBase(root@test)> SELECT sname, MAX(sage) FROM student GROUP BY sname; Empty set (0.00 sec) ``` - 原生 MySQL。 ```shell # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.s.sno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname; ERROR 1055 (42000): Expression mysql> show variables like "%sql_mode%"; +---------------+-----------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +---------------+-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec) -- ONLY_FULL_GROUP_BY 该设置则约束查询必须是聚合的列 mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) -- 非严格模式所以不报错 mysql> SELECT sname, sdept, MAX(sage) FROM student GROUP BY sname; Empty set (0.00 sec) ``` ## 问题的风险及影响 执行结果不稳定。 ## 影响租户 影响 OceanBase 数据库中的 SYS 租户和 MySQL 租户，对于 Oracle 租户无影响。 ## 适用版本 OceanBase 数据库所有版本。 ## 解决方法 推荐正常写法 GROUP BY 不包含所有的非聚合字段时需要注意。 ```shell -- 增加约束 ONLY_FULL_GROUP_BY 该设置则约束查询必须是聚合的列 OceanBase(root@test)>set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER'; Query OK, 0 rows affected (0.00 sec) ``` ## 规避方式 推荐正常写法 GROUP BY 不包含所有的非聚合字段时需要注意。 详情参见：[MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html)。 上一篇 [OceanBase 数据库普通租户下自定义函数调用异常](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207738) 下一篇 [使用 nlssort 报错：-4077, 不支持的功能](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000210001) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线