MySQL 5.7+ ONLY_FULL_GROUP_BY SQL Mode 問題
MySQL 除錯 Debug: MySQL 5.7+ ONLY_FULL_GROUP_BY SQL Mode 問題
問題狀況
在 MySQL 5.7 以上的版本執行 GROUP BY 的指令時，顯示了 ONLY_FULL_GROUP_BY 的問題，無法完成執行此語法
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.web_log.user_id' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
但原本的 GROUP BY 語法在 5.6 版本以下是可以執行的，以舊有版本舉例來說，我們建立一個新的 web_log 資料表來做測試，此資料表建立了 4 個欄位分別是 id（編號） 、 page_url（網址） 、 user_id（使用者編號） 及 created_at（建立時間），然後再這個資料表塞入假資料當作測試
mysql> create table web_log ( id int auto_increment primary key, page_url varchar(100), user_id int, created_at timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into web_log(page_url , user_id, created_at) values('/index.html',1,'2019-04-17 12:21:32'),
-> ('/index.html',2,'2019-04-17 12:21:35'),('/news.php',1,'2019-04-17 12:22:11'),('/store_offers.php',3,'2019-04-17 12:22:41'),
-> ('/store_offers.php',2,'2019-04-17 12:23:04'),('/faq.html',1,'2019-04-17 12:23:22'),('/index.html',3,'2019-04-17 12:32:25'),
-> ('/news.php',2,'2019-04-17 12:32:38');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from web_log;
+----+--------------------+---------+---------------------+
| id | page_url | user_id | created_at |
+----+--------------------+---------+---------------------+
| 1 | /index.html | 1 | 2019-04-17 12:21:32 |
| 2 | /index.html | 2 | 2019-04-17 12:21:35 |
| 3 | /news.php | 1 | 2019-04-17 12:22:11 |
| 4 | /store_offers.php | 3 | 2019-04-17 12:22:41 |
| 5 | /store_offers.html | 2 | 2019-04-17 12:23:04 |
| 6 | /faq.html | 1 | 2019-04-17 12:23:22 |
| 7 | /index.html | 3 | 2019-04-17 12:32:25 |
| 8 | /news.php | 2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+
將目前連線資料庫 session 的 模式（Mode） 設定值清空，使用 GROUP BY 查詢 各個頁面網址（page_url） 的瀏覽次數是多少次
# lets turn the sql mode to "forgiving"
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT page_url, user_id, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+---------+--------+
| page_url | user_id | visits |
+-------------------+---------+--------+
| /index.html | 1 | 3 |
| /news.php | 1 | 2 |
| /store_offers.php | 3 | 2 |
| /faq.html | 1 | 1 |
+-------------------+---------+--------+
4 rows in set (0.00 sec)
這樣執行 GROUP BY 可以正常的撈取到資料，但其實這個語法對於 SQL 在處理資料來說是不太正確的，在這個查詢中可以看到將 page_url GROUP BY，所以我們可以撈到唯一值的 page_url 資料，但語法中包含 user_id，這個語法的 user_id 對於資料庫來說，會不曉得要抓哪一筆資料，因為它可以是 1、2 或 3 的值
所以當 SQL Mode 設定為 ONLY_FULL_GROUP_BY 的時候，會跑出像下方這樣的錯誤訊息
mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT page_url, user_id, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.web_log.user_id' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解決辦法
解法 1. 重新撰寫 Query
把一些不確定性因素的欄位移除 GROUP BY 的語法，像下方把 user_id 移出這段語法，就可以正常撈取資料了
mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT page_url, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------+
| page_url | visits |
+-------------------+--------+
| /index.html | 3 |
| /news.php | 2 |
| /store_offers.php | 2 |
| /faq.html | 1 |
+-------------------+--------+
解法 2. 修改 SQL Mode
不使用任何的 SQL Mode，或是將 ONLY_FULL_GROUP_BY 從 SQL Mode 中移出
#set the complete "forgiving" mode
mysql> SET GLOBAL sql_mode='';
# alternatively you can set sql mode to the following
mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
解法 3. 使用整合性的欄位函數
在欄位中使用 MAX() 、 MIN() 、 GROUP_CONCAT() 、 ANY_VALUE() 之類的函數，確保欄位值是計算後的唯一值
mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
mysql> SELECT page_url, MAX(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------+--------+
| page_url | MAX(user_id) | visits |
+-------------------+--------------+--------+
| /index.html | 3 | 3 |
| /news.php | 2 | 2 |
| /store_offers.php | 3 | 2 |
| /faq.html | 1 | 1 |
+-------------------+--------------+--------+
mysql> SELECT page_url, GROUP_CONCAT(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+-----------------------+--------+
| page_url | GROUP_CONCAT(user_id) | visits |
+-------------------+-----------------------+--------+
| /index.html | 1,2,3 | 3 |
| /news.php | 1,2 | 2 |
| /store_offers.php | 3,2 | 2 |
| /faq.html | 1 | 1 |
+-------------------+-----------------------+--------+
mysql> SELECT page_url, ANY_VALUE(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------------+--------+
| page_url | ANY_VALUE(user_id) | visits |
+-------------------+--------------------+--------+
| /index.html | 1 | 3 |
| /news.php | 1 | 2 |
| /store_offers.php | 3 | 2 |
| /faq.html | 1 | 1 |
+-------------------+--------------------+--------+
以正規的寫法，建議用 解法 1 去解決 SQL 問題，這畢竟是之後的標準規定，讓自己的語法能夠與時俱進的符合標準比較好
若系統中的語法過於複雜，用 解法 2 可以比較快去解決這樣的問題，但建議還是盡快修正這個語法讓語法能符合標準規範