MySQL运维实战系列：MySQL5.7 Group By 问题

一、环境

MySQL版本：MySQL5.7.22表结构：CREATE TABLE `crm_report_accounting_income` (`id` int(10) NOT NULL AUTO_INCREMENT,`contract_id` int(10) NOT NULL,`contract_no` varchar(50) NOT NULL,`date` int(8) NOT NULL,`city_id` int(11) NOT NULL DEFAULT '0' COMMENT '城市id',`city_name` varchar(50) DEFAULT NULL,`adviser_id` int(10) NOT NULL,`adviser_name` varchar(50) DEFAULT NULL,`accounting` decimal(15,2) NOT NULL COMMENT 'xx',`receivable` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '当xx',`contract_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1:xx合同;2:xx合同;3:xx合同',PRIMARY KEY (`id`),KEY `contract_id` (`contract_id`),KEY `date` (`date`),KEY `city_id` (`city_id`) ) ENGINE=InnoDB AUTO_INCREMENT=734525 DEFAULT CHARSET=utf8

二、业务问题

* 基本信息，由于合同号太多，所以这边就以一个有重复数据的合同id为例dba:aif_db> select contract_id,contract_no,receivable,date from crm_report_accounting_income_2015_online where contract_id = 27310; +-------------+----------------------------+------------+----------+ | contract_id | contract_no | receivable | date | +-------------+----------------------------+------------+----------+ | 27310 | A00-SHEN-05-2018-06-004613 | 2941.18 | 20180628 | | 27310 | A00-SHEN-05-2018-06-004613 | 5882.36 | 20180629 | | 27310 | A00-SHEN-05-2018-06-004613 | 8823.54 | 20180630 | | 27310 | A00-SHEN-05-2018-06-004613 | 11764.72 | 20180701 | | 27310 | A00-SHEN-05-2018-06-004613 | 14705.90 | 20180702 | | 27310 | A00-SHEN-05-2018-06-004613 | 17647.08 | 20180703 | | 27310 | A00-SHEN-05-2018-06-004613 | 20588.26 | 20180704 | | 27310 | A00-SHEN-05-2018-06-004613 | 23529.44 | 20180705 | | 27310 | A00-SHEN-05-2018-06-004613 | 26470.62 | 20180706 | | 27310 | A00-SHEN-05-2018-06-004613 | 29411.80 | 20180707 | | 27310 | A00-SHEN-05-2018-06-004613 | 32352.98 | 20180708 | | 27310 | A00-SHEN-05-2018-06-004613 | 35294.16 | 20180709 | +-------------+----------------------------+------------+----------+ 12 rows in set (0.00 sec)* 查询每个最新合同的信息，由于合同号太多，所以这边就以一个有重复数据的合同id为例select contract_no, contract_id, city_name, receivable,date from (select * from crm_report_accounting_income_2015_online where contract_id = 27310 ORDER BY `date` desc) p GROUP BY contract_id+----------------------------+-------------+-----------+------------+----------+ | contract_no | contract_id | city_name | receivable | date | +----------------------------+-------------+-----------+------------+----------+ | A00-xxxx-05-2018-06-xxxxxx | xxxxx | 沈阳 | 2941.18 | 20180628 | +----------------------------+-------------+-----------+------------+----------+ 1 row in set (0.00 sec)

以上看到的写法，是通过子查询写的，5.6查询没问题，5.7就变成了以上的结果，很明显得到的答案不是业务想要的

究其原因还是因为，MySQL5.7 sql mode更加严格了，如果设置sql_mode = ONLY_FULL_GROUP_BY, 那么以上SQL就会报错

因为sql_mode = ONLY_FULL_GROUP_BY 要求符合SQL 92标准，即：select列表里只能出现分组列（即group by后面的列）和聚合函数（max，min等等）

然而为了兼容5.6，我们设置sql_mode='', 所以我们的Group by 在子查询中就跟5.6就不一致了

当然，我们应该避免不标准的SQL写法，这样的问题，我们的解法就是调整业务的SQL语句，改写成SQL 92标准的语法

那么以上SQL语句应该调整为：

selectcontract_no,e.contract_id,city_name,receivable,date fromcrm_report_accounting_income_2015_online e,( select contract_id , max(date) max_date from crm_report_accounting_income_2015_online where contract_id = 27310 group by contract_id ) t wheree.contract_id = t.contract_idand e.date = t.max_date+----------------------------+-------------+-----------+------------+----------+ | contract_no | contract_id | city_name | receivable | date | +----------------------------+-------------+-----------+------------+----------+ | A00-xxxx-05-2018-06-004613 | 27310 | xxxx | 35294.16 | 20180709 | +----------------------------+-------------+-----------+------------+----------+ 1 row in set (0.00 sec)

以上都还是需要业务代码修改，这样如果没有提前发现问题，岂不是会导致业务出错了？有没有更好的办法？

MySQL方面其实还是可以配置相关的参数的：

dba:aif_db> set optimizer_switch='derived_merge=off'; Query OK, 0 rows affected (0.00 sec)dbadmin:aifangcrm_db> select contract_no, contract_id, city_name, receivable,date from-> (select * from crm_report_accounting_income_2015_online where contract_id = 27310 ORDER BY `date` desc) p GROUP BY contract_id-> ; +----------------------------+-------------+-----------+------------+----------+ | contract_no | contract_id | city_name | receivable | date | +----------------------------+-------------+-----------+------------+----------+ | A00-xxxx-05-2018-06-004613 | 27310 | xxxx | 35294.16 | 20180709 | +----------------------------+-------------+-----------+------------+----------+ 1 row in set (0.00 sec)

三、总结

• SQL语法应该要按照标准的SQL92来写
• 数据库升级到5.7之后，应该提前监控处group by + 子查询的情况，提前告知业务修改业务代码
• 设置参数也能解决问题，但是这个参数毕竟是5.7新增的，如果关闭后，以后会不会导致其他的bug就不知晓了

最后，还是希望能够修改query 语句到标准语法，如果出现业务问题，可以让业务修改参数快速解决问题，然后再修改语句比较与时俱进

总结

以上是生活随笔为你收集整理的MySQL运维实战系列：MySQL5.7 Group By 问题的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。