Oracle、MySQL、达梦数据库GROUP BY语句对比

问题：

MySQL 数据库迁移到达梦后，XXX反馈部分GROUP BY语句执行失败，报错如下：

SQL> select id from t1 where name='aaa' group by name;
select id from t1 where name='aaa' group by name;
第1 行附近出现错误[-4080]:不是 GROUP BY 表达式.
已用时间: 0.317(毫秒). 执行号:0.

问题原因：

对于Oracle数据库，使用GROUP BY时，SELECT中的非聚合列必须出现在GROUP BY后面，否则就会报上面的错误，达梦默认也有此限制，但MySQL无此限制，这就导致了MySQL数据库迁移到达梦后，如果使用默认参数，此类SQL无法正常执行。

解决方案：

1.修改代码，将对应SQL语句改成符合达梦默认语法(此方法开发通常不会接受)；
2.修改达梦数据库参数，兼容MySQL语法(COMPATIBLE_MODE 或 GROUP_OPT_FLAG)。

测试过程如下：

Oracle数据库：11.2.0.4.0
测试数据：

SQL> select * from t1;
ID NAME
---------- ----------
1 aaa
2 aaa
100 ccc

报错：

SQL> select id from t1 where name='aaa' group by name;
select id from t1 where name='aaa' group by name
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL> select name,id from t1 where name='aaa' group by name;
select name,id from t1 where name='aaa' group by name
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

[oracle@cjc-db-01 ~]$ oerr ORA 00979
00979, 00000, "not a GROUP BY expression"
// *Cause:
// *Action:

SELECT中的非聚合列必须出现在GROUP BY后面。
MySQL数据库:8.0.32
测试数据：

mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | aaa |
| 100 | ccc |
+------+------+
3 rows in set (0.00 sec)

SELECT中的非聚合列不是必须出现在GROUP BY后面

mysql> select id from t1 where name='aaa' group by name;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

达梦数据库:8.1.3.xx
测试数据：

SQL> select * from t1;
行号 id name
---------- ----------- ----
1 1 aaa
2 2 aaa
3 100 ccc


已用时间: 0.591(毫秒). 执行号:1007.

默认报错：

SQL> select id from t1 where name='aaa' group by name;
select id from t1 where name='aaa' group by name;
第1 行附近出现错误[-4080]:不是 GROUP BY 表达式.
已用时间: 0.317(毫秒). 执行号:0.

SQL> select name,id from t1 where name='aaa' group by name;
select name,id from t1 where name='aaa' group by name;
第1 行附近出现错误[-4080]:不是 GROUP BY 表达式.
已用时间: 0.299(毫秒). 执行号:0.

查看GROUP_OPT_FLAG、COMPATIBLE_MODE参数
其中:

GROUP_OPT_FLAG 当前值为60，为动态参数，修改立即生效；
COMPATIBLE_MODE当前值为0，为静态参数，修改后需重启生效；
本次测试将GROUP_OPT_FLAG改成1:
GROUP_OPT_FLAG=1表示非 MySQL 兼容模式下（即 COMPATIBLE_MODE 不等于 4），支持查询项不是GROUP BY 的表达式。

SQL> SHOW PARAMETER GROUP_OPT_FLAG


行号 para_name para_value
---------- -------------- ----------
1 GROUP_OPT_FLAG 60




SQL> SELECT * FROM V$DM_INI WHERE PARA_NAME='GROUP_OPT_FLAG';


行号 PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE SYNC_FLAG
---------- -------------- ---------- --------- --------- ------------- ------- ---------- ---------- --------------------- --------- ---------
SYNC_LEVEL
----------
1 GROUP_OPT_FLAG 60 0 255 60 N 60 60 the flag of opt group SESSION ALL_SYNC
CAN_SYNC


已用时间: 6.839(毫秒). 执行号:1102.

[root@cjc-db-01 CJC]# cat dm.ini |grep COMPATIBLE_MODE
COMPATIBLE_MODE = 0 #Server compatible mode, 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL, 5:DM6, 6:Teradata, 7:PG

 修改参数:

执行下面语句也会自动修改dm.ini文件。

SQL> sp_set_para_value(1,'GROUP_OPT_FLAG',1);
DMSQL 过程已成功完成
已用时间: 10.444(毫秒). 执行号:1103.

再次执行GROUP BY语句，没报错了

SQL> select id from t1 where name='aaa' group by name;


行号 id
---------- -----------
1 1


已用时间: 1.345(毫秒). 执行号:1104.

###chenjuchao 20240608###
欢迎关注我的公众号《IT小Chen》