MySQL运维实战系列:MySQL5.7 Group By 问题-阿里云开发者社区

开发者社区> 兰春> 正文

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语句应该调整为:

select
    contract_no,
    e.contract_id,
    city_name,
    receivable,
    date
from
    crm_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
where
    e.contract_id = t.contract_id
    and 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 语句到标准语法,如果出现业务问题,可以让业务修改参数快速解决问题,然后再修改语句比较与时俱进

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server自动化运维系列——监控跑批Job运行状态(Power Shell)
原文:SQL Server自动化运维系列——监控跑批Job运行状态(Power Shell) 需求描述 在我们的生产环境中,大部分情况下需要有自己的运维体制,包括自己健康状态的检测等。如果发生异常,需要提前预警的,通知形式一般为发邮件告知。
1283 0
SQL Server自动化运维系列——关于邮件通知那点事(.Net开发人员的福利)
原文:SQL Server自动化运维系列——关于邮件通知那点事(.Net开发人员的福利) 需求描述 在我们的生产环境中,大部分情况下需要有自己的运维体制,包括自己健康状态的检测等。如果发生异常,需要提前预警的,通知形式一般为发邮件告知。
1813 0
《VMware vSphere企业运维实战》——1.7 存储的选择
在规划存储时,还要考虑存储的接口数量及接口的速度。通常来说,在规划一个具有4主机、1个存储的系统中,采用具有2个接口器、4个SAS接口的存储服务器是比较合适的。如果有更多的主机,或者主机需要冗余的接口,则可以考虑配FC接口的存储,并采用光纤交换机连接存储与服务器。
1389 0
(二十四)mongodb中group的问题二
<span style="font-size:18px">    今天的工作还是继续昨天没有完成的,由于对mongodb数据库的不熟悉,导致昨天的思路上也出了一点问题,我需要查询出同一个ruleID中不同的processingID的条数,然后根据条数来排列先后顺序,如果条数相等的情况下,就根据updateTime再排序。<br>     昨天的思路是先按数量排序,然后再根据ruleID查
1160 0
【Linux】MySQL 运维常用脚本
【Linux】MySQL 运维常用脚本目录 操作规范1、数据操作,必须谨慎,线上尤甚!!!2、线上数据操作,必须备份!备份需完整、可用,备份使用自己最容易操作回滚的方式。3、数据操作,能修改数据解决的决不删除4、不确定的操作不做,不确定的命令不敲5、如果出现问题,例如数据丢失、数据文件损坏,必须首先完整备份当前环境,保证后续操作不会造成更大的影响,以便留给能解决问题的人解决。
8494 0
(二十三)mongodb中group的问题
  <span style="font-size:18px"> 今天的工作中我需要从mongodb数据库中查出一定的数据,并排序后返回给前台,数据库表中包含了ruleID,processingID,userID,updateTime等字段。<br>    同一个ruleID可以对应多条processingID,我要做的是查出同一个ruleID中不同的processingID的条数,然后根
1048 0
+关注
兰春
数据库技术爱好者,专注于MySQL领域的运维与运营,擅长性能调优,系统瓶颈分析,热爱数据领域的一切
65
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载