欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

程序员文章站 2022-03-23 23:17:51
...

数据库操作出错

操作语句:

TRUNCATE contract_ele;

错误信息:

[SQL]TRUNCATE contract_ele;
受影响的行: 0
时间: 0.156s

[Err] 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

执行显示成功,但是会报错误信息

原因:
这是数据库的sql_mode设置的有问题。Mysql可以支持不同的SQL模式,不同的SQL模式会有不同的语法,执行不同的数据校验简查。

1 查看sql_model

select @@sql_mode;

查看结果
数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column2 去掉ONLY_FULL_GROUP_BY,重新设置

set sql_mode =(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
查看是否成功

select @@sql_mode;

数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column已经成功 但是 只有新建的库会生效 接下来改变一下已经建好的库

3 下面我们看一下全局的sql_mode:

select @@global.sql_mode;

数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column发现全局并没有去掉
所以我们去一下全局的

set @@global.sql_mode =(select replace(@@global.sql_mode,'ONLY_FULL_GROUP_BY',''));

数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated columnok 这样全局就设置好了
最后重新链接一下数据库

运行一下之前的sql语句

TRUNCATE contract_ele;

结果:
数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
执行成功 ,错误信息已经不提示

参考资料:
https://www.cnblogs.com/LLBFWH/articles/10976587.html