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;
  1. 关于ANY_VALUE(), 想了解更多可以查看官网
  1. 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,重新设置值

  1. 改变全局sql_mode,对新建的数据库有效
SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  1. 修改已存在数据库的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会再次生效

上一篇下一篇

猜你喜欢

热点阅读