一文彻底说清楚MySQL报1055 - SELECT list is not in GROUP BY incompatible with sql_mode=only_full_group_by的问题

先说结论

        这个问题根本原因是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！（原文链接）