sql_mode之only_full_group_by
- 2019 年 11 月 6 日
- 筆記
sql_mode之only_full_group_by
今天上班的時候，業務方問了我這樣一個問題：能不能把線上的sql_mode值改為和測試環境一致？因為我們在測試環境上寫的sql在線上可能會出錯，原因是線上的環境設置了sql_mode=only_full_group_by。
聽到這個需求的時候，我還是很震驚的，這個線上的環境竟然還有人想修改，而且是往更加寬鬆的程度上去修改，這我肯定不能同意啊。不過也可以理解他們，他們可能沒有意識到這個參數的作用，只是從自己的角度出發，想著這樣能夠解決他們的問題。後面我了解了一下情況，大概的情況就是他們寫了一個sql，使用了group by，然後group by裡面的欄位只有一個，但是select語句中查詢了多個欄位，導致MySQL報了類似下面的錯誤：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
大概就是說這個select語句中的欄位了group by中的欄位不一致，這在MySQL中是不被允許的。
了解了需求之後，我大概舉了一個例子，對這種情況進行了一個測試，且看如下例子：
mysql--dba_admin@127.0.0.1:yeyztest 22:15:04>>select * from test; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 22:15:13>>select @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
這個例子中創建了一個test的表，其中id是主鍵，其他兩個欄位是普通的欄位，然後sql_mode包含了only_full_group_by，然後我們開始寫SQL來查看是否可以通過：
mysql--dba_admin@127.0.0.1:yeyztest 22:15:21>>select * from test group by age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
可以看到，這個問題復現了，我們使用了select *的語法，然後最後group by一個age欄位，這樣的話，id和score欄位沒有用到，提示我們的是不可以執行，因為前後欄位數量不一致。那麼如果我們使用三個欄位做group by呢？
mysql--dba_admin@127.0.0.1:yeyztest 22:16:10>>select * from test group by age,score,id; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 9 | 2 | 45 | | 5 | 5 | 25 | | 10 | 5 | 50 | | 8 | 8 | 40 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec)
可以看到，如果使用了group by三個欄位，那麼這個結果是可靠的，沒有報錯誤，而且是按照group by最近的一個欄位age來進行的分組，再來看單獨使用id的情況以及使用id和score的情況：
mysql--dba_admin@127.0.0.1:yeyztest 22:16:25>>select * from test group by id; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 22:17:05>>select * from test group by score,id; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec)
我們發現，都可以執行成功。雖然前面使用了select *後面的group by只使用了部分欄位，但是依舊可以成功，沒有報錯，這裡我就有一些疑問了，為什麼這個也是部分欄位但是卻沒有報錯，那麼如果我不使用id，使用age和score的組合呢？
mysql--dba_admin@127.0.0.1:yeyztest 22:17:08>>select * from test group by score,age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
我們可以發現，單獨使用這兩個欄位age和score進行group by，那麼結果一定是不可靠的，因為會報錯。問題到底出在那裡呢？把select * 改成age和score試試：
mysql--dba_admin@127.0.0.1:yeyztest 22:19:02>>select age,score from test group by age,score ; +------+-------+ | age | score | +------+-------+ | 1 | 5 | | 2 | 10 | | 2 | 45 | | 5 | 25 | | 5 | 50 | | 8 | 40 | | 8 | 55 | +------+-------+ 7 rows in set (0.00 sec)
發現這樣是可以的，當然，這樣符合要求，就是group by後面的欄位和select 的欄位一致。
以上都是有這個參數的情況，再來看看沒有only_full_group_by的情況，先切換到這種非嚴格模式：
mysql--dba_admin@127.0.0.1:yeyztest 22:17:35>>set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 22:17:54>>select * from test group by score,age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql--dba_admin@127.0.0.1:yeyztest 22:17:58>>set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql--dba_admin@127.0.0.1:yeyztest 22:18:20>>select * from test group by score,age; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec)
上面兩個測試，僅僅是set global和set session的區別，可以看到，當我們set global的時候，這個sql_mode相當於還是以前的，要使他生效，需要重新連接進來，也就是使用新會話，否則不會生效，而我們使用了set session之後，當前回話的sql_mode就直接被改掉了。也就支援select和group by的前後欄位不一致了。
經過我們上面這麼多實驗，可以得到下面的結論：
1、當我們的sql_mode使用了only_full_group_by的時候，如果要想實現select的欄位和group by的欄位數量不相同而查詢語句不出錯，那麼我們必須在group by後面添加主鍵欄位，否則一定會出錯。
2、當我們去掉sql_mode的only_full_group_by的時候，之所以前後欄位數量不一致還能查詢成功，實際上是mysql為我們進行了補齊。這種情況下我們不必保持前後欄位數量一致，但是需要知道，這是一種不好的習慣，在我們日常寫sql的時候，還是盡量要在嚴格模式下面寫，這樣mysql會為我們進行校驗，如果一旦不合適，就會拋出錯誤，有助於提升你sql語句的健壯性。
