MySQL 5.7.x error:this is incompatible with sql_mode=only_full_group_by

错误信息

ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.tables.DATA_LENGTH' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个错误的意思:选择的列没有包含在GROUP BY子句中,而且包含了非聚合列,它在功能上不依赖于GROUP BY子句的列;这与sql mode=only_full_group_by完全不兼容。

查询版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

sql_mode查询

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

也可以使用命令 show variables like '%sql_mode%'; 查询 sql_mode。

当前数据库sql_mode确实包括了ONLY_FULL_GROUP_BY开启ONLY_FULL_GROUP_BY模式后,group by和select的字段就会严格匹配。

sql测试

mysql> select id,title from blog_article group by title;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cms_linbaby.blog_article.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决办法

重新设置sql_mode,来关闭这个选项。

临时修改

在 navicat 命令窗口,键入如下命令来修改:

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';

其实就是去掉了ONLY_FULL_GROUP_BY,在查询sql_mode可以看到设置成功。

但是这个方法如果重启了mysql,设置就会失效,最好的办法是永久关闭。

永久修改

打开 /etc/my.cnf 配置文件,在 mysqld 节点下添加如下配置:

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

然后重启mysql:

service mysql restart

再次执行sql,可以看到可以成功执行,问题解决。

MySQL 5.7.x sql错误:this is incompatible with sql_mode=only_full_group_by

  • qq_43638135
    妲己再美究为妃: 博主没有想过自己接一些私活干吗?我现在还没毕业,但是我也确实听说外挂市场自动化游戏脚本市场挺火热的,并且报酬也很丰厚,但是具体的我也不是很清楚,求解答。 (1个月前 #47楼) 查看回复(2) 举报 回复
    22