MySQL-V5.6以后#ERROR1055 sql_mode=only_full_group_by

解决MySQL ONLY_FULL_GROUP_BY问题

原创已于 2025-12-05 10:14:42 修改 · 165 阅读

0 ·

CC 4.0 BY-SA版权

创作源自于MyFreeIT

文章标签：

#mysql

于 2021-06-16 22:48:52 首次发布

数据库专栏收录该内容

59 篇文章

订阅专栏

在sql_mode=only_full_group_by模式下不能执行Insert，SQL Mode详见。

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 
'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause;
 this is incompatible with sql_mode=only_full_group_by

only_full_group_by的作用

MySQL在5.7版本以后，ONLY_FULL_GROUP_BY對GROUP BY合法性的檢查。当配置了此sql_mode后，select语句中要查询的字段必须严格是group by语句中的字段或者是聚合函数。

通过my.ini文件删除

MySQL的配置信息如下

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

通过命令行删除ONLY_FULL_GROUP_BY

mysql> select version(), @@sql_mode;

SELECT @@sql_mode;

+-----------+--------------------------------------------+
| version() | @@sql_mode                                 |
+-----------+--------------------------------------------+
| 5.6.49    | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+-----------+--------------------------------------------+

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected


SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));