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...

一、環境

SQL/">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_onlinewhere 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 語句到標準語法，如果出現業務問題，可以讓業務修改引數快速解決問題，然後再修改語句比較與時俱進