数据库报错: 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;
查看结果
2 去掉ONLY_FULL_GROUP_BY,重新设置
set sql_mode =(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
查看是否成功
select @@sql_mode;
已经成功 但是 只有新建的库会生效 接下来改变一下已经建好的库
3 下面我们看一下全局的sql_mode:
select @@global.sql_mode;
发现全局并没有去掉
所以我们去一下全局的
set @@global.sql_mode =(select replace(@@global.sql_mode,'ONLY_FULL_GROUP_BY',''));
ok 这样全局就设置好了
最后重新链接一下数据库
运行一下之前的sql语句
TRUNCATE contract_ele;
结果:
执行成功 ,错误信息已经不提示
参考资料:
https://www.cnblogs.com/LLBFWH/articles/10976587.html
上一篇: php怎么知道当前是第几周
下一篇: PHP5.3中的静态变量
推荐阅读
-
解决大于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.
-
报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
idea报错 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
【问题解决】Expression #30 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zxj
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
-
数据库报错: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
-
1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema
-
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column