MySqL (5.7 or above) only_full_group_by问题

叁歲伎倆 2022-09-04 02:55 106阅读 0赞

MySql数据库报错如下：

> ERR-CODE: \[TDDL-4614\]\[ERR\_EXECUTE\_ON\_MYSQL\] Error occurs when execute on GROUP ‘SHEBAO\_TAX\_16237\*\*\*\*\*\*\*\*\*\*\*\*\*\*FARD\_IN18\_0000’ ATOM ‘rm-2ze5d6f76yg43c2m3\_\_tax\_yfpr\_0000’: Expression \#3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tax\_yfpr\_0000.tiic.create\_time’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

从字面意思理解是select后面查询的字段没有出现在group by中。  
MySql在低版本（5.7.x以下）中允许select后面的非聚合列不出现在group by中。以下sql在低版本中是可以运行的，但是在5.7及以上版本会报错。  
MySql从5.7版本开始默认开启only\_full\_group\_by规则，规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql

1.  order by后面的列必须是在select后面存在的
2.  select、having或order by后面存在的非聚合列必须全部在group by中存在

问题解决方案  
第一种，修改sql使其遵守only\_full\_group\_by规则  
第二种，将MySql的版本降到5.7以下  
第三种，关闭only\_full\_group\_by规则。

方案评估：  
第一种方式适用于sql比较少的情况，如果程序中有大量的sql没有遵循only\_full\_group\_by规则，这种方式修改起来会很浪费时间  
第二种方式不建议使用，高版本很明显的一个好处是安全性高，如果降低版本会有一定的风险。  
第三种方式比较推荐，不过要重启数据库，步骤如下

1.  查看是否开启了only\_full\_group\_by规则校验，在数据库中执行如下命令

SELECT @@GLOBAL.sql_mode;
    SELECT @@SESSION.sql_mode;

查询结果如下，从查询结果中可以看到ONLY\_FULL\_GROUP\_BY这个关键字，说明开启了规则校验。

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY

1.  关闭only\_full\_group\_by的规则校验，关闭规则校验需要执行如下sql。以下两个sql单引号中的内容是第一步查询的结果去掉“,ONLY\_FULL\_GROUP\_BY”之后的值。

set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

有时候直接设置为这样也可以。

set @@GLOBAL.sql_mode='NO_ENGINE_SUBSTITUTION';
    set @@SESSION.sql_mode='NO_ENGINE_SUBSTITUTION';

1.  重启数据库，登录数据库所在的服务器执行如下命令  
    service mysqld start;