先说结论
这个问题根本原因是SQL不严谨造成的,由于5.7版本之前没有严格限制所以没有报错,但5.7之后默认进行了限制,需要DBA根据实际生产情况决定是忽略该错误,还是优化SQL解决该错误。
如果是要忽略该错误,那么就把5.7版本加入的默认限制给去掉即可,即把SQL_MODE中的“ONLY_FULL_GROUP_BY”给去掉,而如果是要优化SQL来解决问题,就需要稍微花点功夫来分析你的SQL,具体分析方法下文会举个简单栗子来进行说明。
产生原因
开发中常常遇到各种各样的错误,解决办法往往也有多种,大致有“掩盖法”、“间接解决法”、“直接解决法”。使用“掩盖法”迟早会在其他时候或其他场景爆出更严重的问题,得不偿失;而“间接解决法”虽然能解决目前的问题,但却是知其然而不知其所以然,往往解决代码的可读性和可理解性都不高,甚至是引入了其他潜在的bug;所以最合适的当然是“直接解决法”,但直接解决问题需要刨根问题,深入了解根本原因,需要耗费时间和精力,所以这也是打磨好产品的一个根基,是慢慢磨出来的。
言归正传,我举个小栗子来说明原因。
CREATE TABLE tbl_a (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
a_name VARCHAR(20) NOT NULL COMMENT 'a名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='a表';
CREATE TABLE tbl_b (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`code` varchar(32) NOT NULL COMMENT '唯一编码',
b_name VARCHAR(20) NOT NULL COMMENT 'b名称',
PRIMARY KEY (id),
UNIQUE KEY uni_code (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='b表';
CREATE TABLE tbl_c (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
c_name VARCHAR(20) NOT NULL COMMENT 'c名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='c表';
CREATE TABLE tbl_d (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`code` varchar(32) NOT NULL COMMENT 'b编码',
a_id BIGINT(20) NOT NULL COMMENT 'aID',
c_name VARCHAR(20) NOT NULL COMMENT 'c名称',
d_name VARCHAR(20) NOT NULL COMMENT 'd名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='d表';
CREATE TABLE tbl_e (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
d_id BIGINT(20) NOT NULL COMMENT 'dID',
e_name VARCHAR(20) NOT NULL COMMENT 'e名称',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='e表';
创建5个表,其中a、b、c都是基础信息表,d、e表是一个综合信息表,它因为关联了基础信息表中的某个字段而建立了关联关系。
现在我们执行下面这个SQL,大家看看执行结果会怎样?
SELECT a.*, b.*, c.id, d.*, e.*
FROM tbl_d d
LEFT JOIN tbl_c c ON c.c_name = d.c_name
LEFT JOIN tbl_b b ON b.`code` = d.`code`
LEFT JOIN tbl_a a ON a.id = d.a_id
LEFT JOIN tbl_e e ON e.d_id = d.id
GROUP BY d.id;
结果是
> 1055 - Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ppay.c.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 时间: 0.042s
那这个SQL,执行结果又会怎样呢?
SELECT a.*, b.*, d.*
FROM tbl_d d
LEFT JOIN tbl_c c ON c.c_name = d.c_name
LEFT JOIN tbl_b b ON b.`code` = d.`code`
LEFT JOIN tbl_a a ON a.id = d.a_id
LEFT JOIN tbl_e e ON e.d_id = d.id
GROUP BY d.id;
结果是
> OK
> 时间: 0.043s
这个SQL,执行结果又会怎样呢?
SELECT a.*, b.*, c.id, d.*, e.*
FROM tbl_d d
LEFT JOIN tbl_c c ON c.c_name = d.c_name
LEFT JOIN tbl_b b ON b.`code` = d.`code`
LEFT JOIN tbl_a a ON a.id = d.a_id
LEFT JOIN tbl_e e ON e.d_id = d.id
GROUP BY c.id, d.id, e.id;
结果是
> OK
> 时间: 0.043s
这个SQL,执行结果又会怎样呢?
SELECT a.*, b.*, ANY_VALUE(c.id), d.*, e.*
FROM tbl_d d
LEFT JOIN tbl_c c ON c.c_name = d.c_name
LEFT JOIN tbl_b b ON b.`code` = d.`code`
LEFT JOIN tbl_a a ON a.id = d.a_id
LEFT JOIN tbl_e e ON e.d_id = d.id
GROUP BY d.id, e.id;
结果是
> OK
> 时间: 0.042s
为什么呢?
从错误的字面意思看就是SELECT的字段和GROUP BY的字段不能进行逻辑上的依赖推导,大白话就是GROUP BY分组后某些字段可能存在多条,MySQL不清楚应该取哪个,再加上SQL_MODE的限制,就直接报错了。(官方错误码手册)
那为什么后面几个SQL又能执行成功呢?其实本质上就是解决了不能进行依赖推导的问题,只是解决办法有多种。
第一种很好理解,我们把SELECT的字段中去掉会产生歧义的;第二种则是既然SELECT存在歧义,那分组的时候我就捎带上你,让你不存在歧义;第三种是明知道有歧义了,但这个歧义产生的后果我不关心,你随意返回个值就行;
接下来再说说为什么对d表的id进行分组时,a、b表不会产生歧义,而c、e表会产生歧义呢?因为d表中存储的是a表的id,即一个d.id是可以唯一推导出一个a.id的,同理,对于b表来说,存储的是含有唯一索引的code字段,通过一个d.id也可以唯一推导出一个b.code。但是对于c表来说存储的是普通字段name,一个d.id是可以推导出无数个b.name的。看到这里你可能会有疑问,e表不就是存储的d表的id吗,怎么还是存在歧义?答案很简单,因为依赖关系是反的,通过e.id是可以推导出唯一一个d.id,但是通过d.id却无法推导出唯一一个e.id。
写在最后
到这里相信聪明的你已经能根据实际生产场景来优化你的SQL了,我想再说说文章开头说的那种“忽略错误”的解决方式,即通过去掉“ONLY_FULL_GROUP_BY”来解决问题的方法。因为当时给同事说了解决办法,他说修改了但是没有生效,下面针对该方法具体说明下,以及最佳实践应该是怎么样的。
MySQL是一个多线程应用,对建立每个建立的连接都会新开启一个线程,每一个线程都会有一个会话session,里面保存了该会话特有的配置,其中就包括本文讨论的“ONLY_FULL_GROUP_BY”。那会话中的配置是从哪儿来的呢?答案是从全局配置中读取的,所以当开启一个全新会话的时候,其配置是完全来自全局配置的。那全局配置又是从哪儿来的呢?答案是MySQL配置文件(linux的*.cnf文件,windows的*.ini文件),里面有关于SQL_MODE的配置项,服务启动时就是通过该值进行初始化的。好,看到这里,相信你应该知道改哪些值需要重启哪些东西了,即:
修改全局配置,则只需要断开并重新连接,建立新的会话即可,但是MySQL服务重启后仍然会回滚(断开重新连接,对于Web服务来说,就是重启Web服务),而修改会话配置则千万不要断开重连(即对于Web服务来说,千万别重启服务),否则又使用全局配置了,最后如果是修改配置文件的话,则需要重启MySQL才能生效。
综上,最佳实践应该是修改MySQL配置文件中的配置项,并且修改全局配置项,这样只需要断开并重新连接,建立新的会话即可立即生效。
have a nice time!(原文链接)