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 可以比较快去解决这样的问题，但建议还是尽快修正这个语法让语法能符合标准规范