Mysql ONLY

06-01 1624阅读

文章目录

  • 一、问题报错
  • 二、ONLY_FULL_GROUP_BY模式
    • 2.1、什么是ONLY_FULL_GROUP_BY？
    • 2.2、为什么要使用ONLY_FULL_GROUP_BY？
    • 2.3、查看sql_mode
    • 三、解决方法
      • 3.1、关闭only_full_group_by模式
        • 3.1.1、方法一：关闭当前会话中的only_full_group_by
        • 3.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_mode=only_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());

            Mysql ONLY

            对上述数据同时查询name与age字段，并根据age字段group by 后报错如下：

            Mysql ONLY

            原因分析 ：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后，不能用双引号来引用字符串，因为它被解释为识别符

              三、解决方法

              对于上述示例中的报错，有以下两种思路解决问题。

              1. 关闭only_full_group_by模式（若没有历史数据的情况下不建议关闭该模式）
              2. 官方说明了：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_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

              Mysql ONLY

              注意：这种方法只在当前回话中有效，重启mysql后会失效

              再次执行本文开头示例中的sql就不会报错了

              Mysql ONLY

              3.1.2、方法二：永久关闭only_full_group_by模式

              (1) 找到配置文件/etc/my.cnf(或则关联文件夹找到mysql-server.cnf)

              (2) 找到当前配置的sql_mode那行，去掉ONLY_FULL_GROUP_BY；

              如果没有，就在文件内的[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的位置是不是不对（放在最后是不会生效的）：

              Mysql ONLY

              3.2、使用ANY_VALUE()函数

              ANY_VALUE()：将分到同一组的数据里第一条数据的指定列值作为返回数据。

              SELECT any_value(name),age  FROM  tbl_test group by age;

              Mysql ONLY

              四、其他

              4.1、无权限报错

              本人执行

              set global sql_mode='STRICT_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());

              Mysql ONLY

              对上述数据同时查询name与age字段，并根据age字段group by 后报错如下：

              Mysql ONLY

              因为开启了ONLY_FULL_GROUP_BY模式，分组字段是age但是查询字段里面包含了name，所以报错了。

              select后面的字段必须在group by后面出现吗？测试中偶然发现事实并非如此，看下图示例：

              Mysql ONLY

              可以看到上述示例中，group by后面根据name进行分组，select后面跟了age、address字段但是也没报错。

              这是为什么呢？难道group by后面分组字段的值不重复就能跟其他字段么？示例中address字段也是不重复的，那么我们用address进行分组试下，结果报错如下：

              Mysql ONLY

              经多次测试后发现: 若group by后面分组字段是唯一索引或主键字段，那么select 后面可以跟其他字段。

              官方文档中也证实了此结论。

              官网地址：https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

              官方解释是：当group by 后面跟上主键或者不为空唯一索引时，查询是有效的，因为此时的每一笔数据都具有唯一性。

              Mysql ONLY

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。

相关阅读

目录[+]

取消
微信二维码
微信二维码
支付宝二维码