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.62

测试数据：
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
       
修改参数：
SQL> sp_set_para_value(1,'GROUP_OPT_FLAG',1);
DMSQL 过程已成功完成
已用时间: 10.444(毫秒). 执行号:1103.

可以执行
SQL> select id from t1 where name='aaa' group by name;
行号     id         
---------- -----------
1          1

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

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