1200字范文 > MySQL: 与only_full_group_by模式不兼容的两个解决办法

MySQL: 与only_full_group_by模式不兼容的两个解决办法

时间：2024-01-08 22:48:04

1. 异常现象

MySQL 官方解释

解决方法

方法1: 修改sql_mode

查询sql_mode设置，发现有ONLY_FULL_GROUP_BY

修改sql_mode设置

方法2: select非group by的字段

MySQL 5.7:sql_mode

sql_mode参数说明

参考连接

1. 异常现象

数据库在迁移后执行SQL出现了问题, 报错信息如下

SELECT list is not in GROUP BY clause and contains nonaggregated column 'cloud.exam_train_user.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以看出是因为sql_mode中设置了only_full_group_by模式引起的

在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。
Oracled等数据库都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是所说的ONLY_FULL_GROUP_BY语义。

MySQL 官方解释

MySQL 5.7.5 及更高版本默认开启了ONLY_FULL_GROUP_BY的SQL 模式

解决方法

方法1: 修改sql_mode

可以通过select @@sql_mode查出sql_mode以后去掉ONLY_FULL_GROUP_BY后复制过来

查询sql_mode设置，发现有ONLY_FULL_GROUP_BY

mysql> select version(),@@sql_mode;+------------+----------------------------------------------------------------+| version() | @@sql_mode |+------------+----------------------------------------------------------------+| 5.7.29-log | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+------------+----------------------------------------------------------------+1 row in set (0.00 sec)

修改sql_mode设置

临时修改

set sql_mode=' ' //改变已经存在的数据库sql_mode
set @@global.sql_mode=' ' //改变全局配置sql_mode

以上配置在重启服务以后失效

更改配置文件（推荐使用）

linux系统更改/etc/f文件windows下配置文件是安装目录下的my.ini文件

如果有sql_mode=...的注释就把注释打开，如果没有就加上sql_mode=...

sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL5.7: f参数速查小册子

方法2:select非group by的字段

直接修改修改sql_mode设置, 很多时候对于开发人员时没有权限的, DBA也需要很慎重地修改,因此对于开发人员, 我们可以在SQL层面进行优化

通过上文，我们知道，

如果sql_mode中设置了only_full_group_by模式
在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。

因此，此时在含有Group by子句的查询语句中，对select关键字后的目标列，存在以下规律

使用group by 时，select 涉及的列要么是参与分组的列，要么列包含在聚合函数中where将对分组前的所有数据进行筛选。having将对分组后的一组数据搞事情。

例如：

select a,b,avg(c),sum(d) from 表 group by a,b

select非group by的字段

可以把group by的结果集当作一个表，然后从这里表里取数就可以了例如：查询，订单表中订单号重复的订单信息，分组字段是orderno，select的信息不仅有orderno，还有其他信息

SELECTp.id,code,p.idserial,p.username,p.orderno,p.createtime,p.payflag,p.businessorderno,p.paytimeFROMpay_order_trade p,(SELECTorderno,count(*) 重复次数FROMpay_order_tradeGROUP BYordernoHAVINGcount(1) > 1) sWHEREs.orderno = p.ordernoORDER BYp.orderno DESC;

MySQL 5.7:sql_mode

模式会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查。
这使得在不同环境中使用 MySQL 以及与其他数据库服务器一起使用 MySQL 变得更容易。

设置 SQL 模式

在MySQL 5.7的默认SQL模式包括以下模式：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。

这些模式已添加到 MySQL 5.7 中的默认 SQL 模式：ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES模式已添加到 MySQL 5.7.5 中。该NO_AUTO_CREATE_USER模式是在 MySQL 5.7.7 中添加的。的ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE以及NO_ZERO_IN_DATE在MySQL 5.7.8添加模式。有关对默认 SQL 模式值的这些更改的其他讨论，请参阅MySQL 5.7 中的 SQL 模式更改。