上海外贸网站建,旅游电子商务网站建设规划,美词网站建设,郑州网站高端设计文章目录 一、问题报错二、ONLY_FULL_GROUP_BY模式2.1、什么是ONLY_FULL_GROUP_BY#xff1f;2.2、为什么要使用ONLY_FULL_GROUP_BY#xff1f;2.3、查看sql_mode 三、解决方法3.1、关闭only_full_group_by模式3.1.1、方法一#xff1a;关闭当前会话中的only_full_group_by3… 文章目录 一、问题报错二、ONLY_FULL_GROUP_BY模式2.1、什么是ONLY_FULL_GROUP_BY2.2、为什么要使用ONLY_FULL_GROUP_BY2.3、查看sql_mode 三、解决方法3.1、关闭only_full_group_by模式3.1.1、方法一关闭当前会话中的only_full_group_by3.1.2、方法二永久关闭only_full_group_by模式 3.2、使用ANY_VALUE()函数 四、其他4.1、无权限报错4.2、select后面的字段必须在group by后面出现当group by遇上唯一索引或主键 以下内容基于Mysql8.0进行讲解ONLY_FULL_GROUP_BY模式。
一、问题报错
Mysql5.7版本以上对group by 分组有了新需求要求group by 后的字段要与select后查询的字段一致否则就会报错报错信息如下 [2024-09-29 10:48:54] [42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.tbl_test.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by 测试用例如下
create table tbl_test(id int primary key auto_increment,name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,age int comment 年龄,address varchar(50) comment 住址,update_time datetime default null
) comment 测试表;insert into tbl_test(name,age,address,update_time) values
(zhangsan,25,杭州,now()),
(李四,21,武汉,now()),
(王五,25,杭州,now());对上述数据同时查询name与age字段并根据age字段group by 后报错如下 原因分析 MySQL5.7版本及以上默认设置了 mysql sql_mode only_full_group_by 属性导致报错。
其中ONLY_FULL_GROUP_BY就是造成这个错误的罪魁祸首了在这种严格模式下对于group by聚合操作若在select中的列没有在group by中出现那么这个SQL就是不合法的。因为开发写的sql中select列不在group by从句中在使用group by时就会报错。
接下来我们一起看一下这个模式的原理以及这种情况如何解决。
二、ONLY_FULL_GROUP_BY模式
2.1、什么是ONLY_FULL_GROUP_BY
ONLY_FULL_GROUP_BY是MySQL的一个SQL模式SQL_MODE之一它要求在使用GROUP BY语句时SELECT列表、HAVING条件或ORDER BY列表中的每个列要么是聚合函数的一部分如COUNT(), SUM(), AVG()等要么必须在GROUP BY子句中明确指定。
这一模式的设计初衷是增强查询的准确性和可预测性避免因为列的不明确引用而导致的数据错误或不一致。
2.2、为什么要使用ONLY_FULL_GROUP_BY
数据准确性确保聚合查询的结果符合预期防止因为非聚合列的不确定行为而导致的数据误导。一致性在不同的数据库系统或配置间保持查询行为的一致性减少迁移或升级时的兼容性问题。避免歧义清晰定义查询的意图减少因查询理解错误而导致的错误。
2.3、查看sql_mode
SELECT sql_mode;
或者
select GLOBAL.sql_mode;查询出来的值为 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对于GROUP BY聚合操作如果在SELECT中的列没有在GROUP BY中出现那么这个SQL是不合法的因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO该值影响自增长列的插入。默认设置下插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0而该列又是自增长的那么这个选项就有用了。
STRICT_TRANS_TABLES在该模式下如果一个值不能插入到一个事务表中则中断当前的操作对非事务表不做限制
NO_ZERO_IN_DATE在严格模式下不允许日期和月份为零
NO_ZERO_DATE设置该值mysql数据库不允许插入零日期插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO在INSERT或UPDATE过程中如果数据被零除则产生错误而非警告。如果未给出该模式那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION如果需要的存储引擎被禁用或未编译那么抛出错误。不设置此值时用默认的存储引擎替代并抛出一个异常
PIPES_AS_CONCAT将”||”视为字符串的连接操作符而非或运算符这和Oracle数据库是一样的也和字符串的拼接函数Concat相类似
ANSI_QUOTES启用ANSI_QUOTES后不能用双引号来引用字符串因为它被解释为识别符三、解决方法
对于上述示例中的报错有以下两种思路解决问题。
关闭only_full_group_by模式若没有历史数据的情况下不建议关闭该模式官方说明了You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column. 您可以在不禁 ONLY_FULL_GROUP_BY用 的情况下通过ANY_VALUE()引用非聚合列来实现相同的效果。
3.1、关闭only_full_group_by模式
3.1.1、方法一关闭当前会话中的only_full_group_by
在客户端工具中依次输入以下语句
# 查询当前的sql模式
select global.sql_mode;# 去除掉only_full_group_by后再设置到系统中
set global sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;
set session sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;注意这种方法只在当前回话中有效重启mysql后会失效
再次执行本文开头示例中的sql就不会报错了
3.1.2、方法二永久关闭only_full_group_by模式
(1) 找到配置文件/etc/my.cnf(或则关联文件夹找到mysql-server.cnf)
(2) 找到当前配置的sql_mode那行去掉ONLY_FULL_GROUP_BY
如果没有就在文件内的[mysqld]后增加配置
sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION(3)保存配置文件后重启Mysql。
如果重启后也不会生效检查下sql_mode的位置是不是不对放在最后是不会生效的
3.2、使用ANY_VALUE()函数
ANY_VALUE()将分到同一组的数据里第一条数据的指定列值作为返回数据。
SELECT any_value(name),age FROM tbl_test group by age;四、其他
4.1、无权限报错
本人执行
set global sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;报错了
[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation说明当前用户没有权限需要联系管理员执行才行
改服务器配置也同理如果没有权限联系管理员。
4.2、select后面的字段必须在group by后面出现当group by遇上唯一索引或主键
我们上面讲了ONLY_FULL_GROUP_BY模式下select中的列必须在group by中出现但是经过测试后发现 若group by 后面分组字段是唯一索引或者是主键那么select后面可以跟其他的列
可能看了这句话不太明白那么我们用示例演示下, 以下示例基于ONLY_FULL_GROUP_BY模式下
create table tbl_test(id int primary key auto_increment,-- 注意 name是唯一索引字段name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,age int comment 年龄,address varchar(50) comment 住址,update_time datetime default null
) comment 测试表;insert into tbl_test(name,age,address,update_time) values
(zhangsan,25,杭州,now()),
(李四,21,武汉,now()),
(王五,25,上海,now());对上述数据同时查询name与age字段并根据age字段group by 后报错如下 因为开启了ONLY_FULL_GROUP_BY模式分组字段是age但是查询字段里面包含了name所以报错了。
select后面的字段必须在group by后面出现吗测试中偶然发现事实并非如此看下图示例 可以看到上述示例中group by后面根据name进行分组select后面跟了age、address字段但是也没报错。
这是为什么呢难道group by后面分组字段的值不重复就能跟其他字段么示例中address字段也是不重复的那么我们用address进行分组试下结果报错如下
经多次测试后发现: 若group by后面分组字段是唯一索引或主键字段那么select 后面可以跟其他字段。
官方文档中也证实了此结论。 官网地址https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html 官方解释是当group by 后面跟上主键或者不为空唯一索引时查询是有效的因为此时的每一笔数据都具有唯一性。
