MySQL运维实战系列:MySQL5.7 Group By 问题
source link: http://keithlan.github.io/2018/11/29/group_by_error/?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
一、环境
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 语句到标准语法,如果出现业务问题,可以让业务修改参数快速解决问题,然后再修改语句比较与时俱进
Recommend
-
56
Mysql5.7官方文档,送给有需要的小伙伴们
-
50
centos7对mysql进行在线升级:①下载mysql源wgethttp://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm②安装mysql5.7源yumlocalinstall-ymysql57-community-release-el7-7.noarch.rpm③安装数据库服务器yuminstall-ymysql-co
-
93
mysql5.7新增的特性中主要的一方面就是极大增强了安全性,安装Mysql后默认会为root@localhost用户创建一个随机密码,这个随机密码在不同系统上需要使用不同方式查找,否则无法登录mysql并修改初始密码。以下以Centos 7为例介绍如何找到初始的随机密码。在低于Mysql...
-
60
-
24
《MySQL5.5从零开始学》主要包括MysQL的安装与配置、数据库的创建、数据表的创建、数据类型和运算符、MySQL函数、查询数据、数据表的操作(插入、更新与删除数据)、索引、存储过程和函数、视图、触发器、用户管理、数据备份与还原、日志以及性能优化。 重...
-
53
Amoeba简介Amoeba(变形虫)项目,该开源框架于2008年开始发布一款AmoebaforMysql软件。这个软件致力于MySQL的分布式数据库前端代理层,它主要在应用层访问MySQL的时候充当SQL路由功能,专注于分布式数据库代理层(DatabaseProxy)开发。座落与Client、DBServer(s)之...
-
56
-
35
部署环境yuminstall-yncurses-devellibaio-develcmakegccgcc-c++openssl-devellrzszwgetmakeperlautoconfautomakezliblibxmllibgcryptlibtoolbisonmkdir/applicationmkdir-p/home/Ricky/toolsMySQL服务启动用户useradd-s/sbin/
-
45
MySQL5.7一键安装脚本
-
1
记一次MySQL5初始化被kill的问题排查 由于测试...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK