MySQL - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
程序员文章站
2022-03-23 17:27:09
...
--数据库中插入数据或执行sql语句时一直报下面这个错误:
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
原因:这是数据库的sql_mode设置的有问题。Mysql可以支持不同的SQL模式,不同的SQL模式会有不同的语法,执行不同的数据校验简查。
首先,可以先查看一下数据库现在的sql_mode的值,sql语句为:
select version(), @@sql_mode;
获取结果可能如下,不同的设置查询出来的有区别:
然后可以使用如下语句,去设置自己需要的sql_mode
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
尝试第二种方案
1、查看sql_mode
select @@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
2、去掉ONLY_FULL_GROUP_BY,重新设置值。
set @@sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
3、上面是改变了全局sql_mode,对于新建的数据库有效。对于已存在的数据库,则需要在对应的数据下执行:
set sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
或者在 my.cnf 里面设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
- 在sql_mode 中去掉only_full_group_by
上一篇: js数组运用之元素能否通过指定函数的测试
下一篇: php基础九之通过curl来做程序
推荐阅读
-
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 的问题 MySQL
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
-
mysql从5.6升级到5.7后出现 Expression #1 of ORDER BY clause is not in SELECT list,this is incompatible with DISTINCT
-
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
-
报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains解决
-
idea报错 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
-
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 的问题 MySQL
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated