MySQL报错 this is incompatible wit
2024-06-27 本文已影响0人
饱饱想要灵感
这个报错问题, 也可以这么问: select的字段不在group by中且未使用聚合函数为啥不报错?
报错信息一般如下:
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
原因
这个问题一般出现在mysql 5.7.5 版本及以上 因为从mysql 5.7.5开始默认使用了严格模式的查询配置:sql_mode=ONLY_FULL_GROUP_BY
,这个配置严格执行了"SQL92标准"。
如果select 的字段不在 group by 中,且未使用聚合函数(SUM, AVG, MAX, MIN等),那么这条sql就会报错。
可以通过以下语句查询:
1.查询mysql版本
SELECT VERSION();
2. 查询sql_mode
select @@GLOBAL.sql_mode;
mysql8默认情况查询出来的值为:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
可以看到,sql_mode开启了only_full_group_by 属性
解决方案
方案一:使用函数ANY_VALUE()包含报错字段
例如:
SELECT USER_NAME, ANY_VALUE(rela_name),ANY_VALUE(gender
) FROM t_user GROUP BY USER_NAME;
- 关于ANY_VALUE(), 想了解更多可以查看官网
- ANY_VALUE()会返回第一条数据
方案二:修改mysql配置文件永久修改sql_mode
1、Linux环境
1)查询配置 先登录mysql, mysql -u username -p
,输入密码,然后输入SQL:
show variables like ‘%sql_mode’;
2)编辑my.cnf文件 文件地址一般在:/etc/my.cnf
,/etc/mysql/my.cnf
找到sql-mode的位置,去掉ONLY_FULL_GROUP_BY 有的my.cnf中没有sql-mode,需要追加到[mysqld]下面:
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)重启MySQL服务
service mysql restart
重启后,再次登录mysql,查询配置验证
2、window环境
1)找到mysql安装目录,打开my.ini文件
2)在[mysqld]标签下添加内容
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
3)重启mysql 服务
方案三:临时修改sql_mode
通过sql语句去掉ONLY_FULL_GROUP_BY,重新设置值
- 改变全局sql_mode,对新建的数据库有效
SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
- 修改已存在数据库的sql_mode,在对应的数据库下执行
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
注意:这种修改方式只是临时的,mysql服务重启之后,ONLY_FULL_GROUP_BY
会再次生效