MySQL運維實戰系列：MySQL5.7GroupBy問題

一、環境

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