---
title: 使用 GROUP BY 报错 ERROR 1055-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于使用 GROUP BY 报错 ERROR 1055相关的常见问题和使用技巧，帮助您快速解决使用 GROUP BY 报错 ERROR 1055的难题。
---
切换语言

- 简体中文
- English

划线反馈

# 使用 GROUP BY 报错 ERROR 1055

更新时间：2026-05-26 09:46

适用版本： V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x 内容类型：Troubleshoot  

## 适用版本

OceanBase 数据库所有版本。

## 问题现象

在 OceanBase 数据库 MySQL模式中，执行如下 SQL 会报出 ERROR 1055 错误。

```shell
obclient [test]> SELECT NAME,ID,COUNT(*) FROM TEST WHERE ID<10 GROUP BY NAME;
ERROR 1055 (42000): 'test.test.id' is not in GROUP BY

```

## 问题原因

根据错误信息判断，主要是 GROUP BY 的使用问题，ERROR 1055 表示 SELECT 列表不在 GROUP BY 语句内且存在不依赖函数 GROUP BY 语句的非聚合字段'id'。

SQL92 中标准 group by 的定义为 SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause。

要求 SQL-92 里 SELECT、HAVING、ORDER 后的非聚合字段必须和 GROUP BY 后的字段保持完全一致。

确认当前 session 的 sql_mode。

```shell
obclient [test]> show variables like '%sql_mode%';
+---------------+------------------------------------------------------+
| Variable_name | Value                                                |
+---------------+------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE |
+---------------+------------------------------------------------------+
1 row in set (0.00 sec)

```

当前 session 中，已经启动了 ONLY_FULL_GROUP_BY 模式，所以导致了 ERROR 1055。

## 解决方法

修改变量 sql_mode，关闭 ONLY_FULL_GROUP_BY。

```shell
obclient [test]> SET @@sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.01 sec)

```

```shell
obclient [test]> SHOW VARIABLES LIKE '%sql_mode%';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| sql_mode      | STRICT_ALL_TABLES,NO_ZERO_IN_DATE |
+---------------+-----------------------------------+
1 row in set (0.00 sec)

```

```shell
obclient [test]> SELECT NAME,ID,COUNT(*) FROM TEST WHERE ID<10 GROUP BY NAME;
+------+------+----------+
| NAME | ID   | COUNT(*) |
+------+------+----------+
| a    |    1 |        1 |
| b    |    2 |        1 |
| c    |    3 |        1 |
| d    |    4 |        1 |
| e    |    5 |        1 |
+------+------+----------+
5 rows in set (0.00 sec)

```

Previous

[MySQL 数据库中 GROUP BY 的使用注意事项](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000661437)

Next

[order by clob 列报错 inconsistent datatypes](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000245505) ![有帮助](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) 咨询热线