内容简介:以上看到的写法,是通过子查询写的,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等等)
一、环境
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 语句到标准语法,如果出现业务问题,可以让业务修改参数快速解决问题,然后再修改语句比较与时俱进
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- Flutter 混合开发实战问题记录(四)编译运行时问题的一些总结
- 架构-稳定性建设逻辑问题实战总结
- 掌握SQL高级功能 !实战业务问题分析
- Flink 实战!Flink 常见问题定位指南
- 实战使用 Arthas 排查生产问题:实例方法接口调用
- 实战生产问题:真的别再使用 TimerTask 了
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
MySQL技术内幕
姜承尧 / 机械工业出版社 / 2013-5 / 79.00元
《MySQL技术内幕:InnoDB存储引擎(第2版)》由国内资深MySQL专家亲自执笔,国内外多位数据库专家联袂推荐。作为国内唯一一本关于InnoDB的专著,《MySQL技术内幕:InnoDB存储引擎(第2版)》的第1版广受好评,第2版不仅针对最新的MySQL 5.6对相关内容进行了全面的补充,还根据广大读者的反馈意见对第1版中存在的不足进行了完善,《MySQL技术内幕:InnoDB存储引擎(第2......一起来看看 《MySQL技术内幕》 这本书的介绍吧!
CSS 压缩/解压工具
在线压缩/解压 CSS 代码
随机密码生成器
多种字符组合密码