解决MySQL分组查询时SELECT list is not in GROUP BY。。。
MySQL版本:5.7.30报错明细1055 - Expression #1 of SELECT list is not in GROUP BY clauseand contains nonaggregated column '***' which is not functionallydependent on columns in GROUP BY clause; this is incompa
·
MySQL版本:5.7.30
报错明细
1055 - Expression #1 of SELECT list is not in GROUP BY clause
and contains nonaggregated column '***' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by, Time: 0.000000s
原因
MySQL实现了对功能依赖的检测。如果启用了only_full_group_by则会提示SELECT列表不在GROUP BY子句,新版本是默认开启only_full_group_by。
解决办法
查询SQL模式:
select @@global.sql_mode;
-- 根据查询结果发现SQL模式确实包含了ONLY_FULL_GROUP_BY,查询结果如下:
-- 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
重新设置SQL模式(去除ONLY_FULL_GROUP_BY即可)
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`;
重新打开操作窗口即可。但是存在一个弊端,MySQL重启后需要重新设置。
永久性解决
以上方法每次重启MySQL后,配置的SQL模式会丢失,在远程服务器找到MySQL的配置文件,在配置文件底部新增以下内容重启MySQL即可:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
docker安装MySQL:可查看本人此博客
通过docker安装的MySQL发现配置文件在mysql.conf.d/mysqld.cnf,并非my.cnf,应该安装方式或MySQL版本不同配置文件地址应该不同。
更多推荐
已为社区贡献2条内容
所有评论(0)