极客笔记MySQL only_full_group_by详解
only_full_group_by详解
| order_id | customer_id | product_id | quantity |
|---|---|---|---|
| 1 | 001 | 101 | 10 |
| 2 | 001 | 102 | 20 |
| 3 | 002 | 103 | 15 |
| 4 | 002 | 101 | 5 |
现在，如果我们想按照customer_id分组，并计算每个客户所购买的产品数量之和，我们可以使用以下查询：
SELECT customer_id, SUM(quantity)
FROM orders
GROUP BY customer_id;
结果将会是：
| customer_id | SUM(quantity) |
|---|---|
| 001 | 30 |
| 002 | 20 |
然而，在使用only_full_group_by模式时，以上的查询将会导致错误，因为我们没有将非聚合列customer_id包含在GROUP BY子句中。
only_full_group_by的影响启用only_full_group_by模式会对查询的语义有所改变。在该模式下，如果我们使用了GROUP BY子句，那么SELECT列表中除聚合函数外的列，必须在GROUP BY子句中包含。
若不遵守此规则，MySQL将返回一个错误，例如：
SELECT customer_id, SUM(quantity)
FROM orders
GROUP BY customer_id;
在启用only_full_group_by模式时，以上的查询将会导致错误信息：
“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘orders.customer_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”
这个错误的原因是：尽管我们想要按照customer_id进行分组，但是在SELECT列表中的customer_id没有在GROUP BY子句中包含。
only_full_group_by使用only_full_group_by模式可以确保查询结果的准确性。如果不启用该模式，MySQL在GROUP BY查询中的非聚合列上的处理方式是不一致的。
例如，考虑以下的表orders：
| order_id | customer_id | product_id | quantity |
|---|---|---|---|
| 1 | 001 | 101 | 10 |
| 2 | 001 | 102 | 20 |
| 3 | 002 | 103 | 15 |
| 4 | 002 | 101 | 5 |
现在，假设我们想要按照customer_id分组，并计算每个客户所购买的产品数量之和，并选取其它非聚合列product_id作为结果展示，我们可以使用以下查询：
SELECT customer_id, product_id, SUM(quantity)
FROM orders
GROUP BY customer_id;
在没有启用only_full_group_by模式时，MySQL会在product_id列中选择一个任意的值，但实际上从语义角度讲，这是不正确的。因为在一个分组中，每个customer_id可能对应多个product_id，但是我们无法确定具体是哪个product_id。这样的查询结果是不可预测的，并且可能导致数据的不准确性。
如果启用了only_full_group_by模式，在上述的查询中将会返回错误，以提示用户进行正确的查询。这使得我们能够更加精确和准确地编写GROUP BY语句。
only_full_group_by启用only_full_group_by模式有以下几种方式：
my.cnf或my.ini）；[mysqld]段；sql_mode参数，添加ONLY_FULL_GROUP_BY。示例：
[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY"
可以在MySQL命令行或客户端中使用SET语句临时地修改sql_mode参数。
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
或者，如果您有相应的权限，也可以使用全局SET语句来修改持久性的sql_mode参数：
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY';
请注意，全局设置将在MySQL服务器重启后仍然有效。
可以在启动MySQL服务器时，通过命令行参数来配置sql_mode。
$ mysqld --sql_mode=ONLY_FULL_GROUP_BY
或者在my.cnf/my.ini配置文件中加入以下内容：
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY
考虑以下表orders：
| order_id | customer_id | product_id | quantity |
|---|---|---|---|
| 1 | 001 | 101 | 10 |
| 2 | 001 | 102 | 20 |
| 3 | 002 | 103 | 15 |
| 4 | 002 | 101 | 5 |
假设我们想要按照customer_id分组，并计算每个客户所购买的产品数量之和，并选取其它非聚合列product_id作为结果展示：
-- 查询语句1
SELECT customer_id, product_id, SUM(quantity)
FROM orders
GROUP BY customer_id;
查询结果，当没有启用only_full_group_by时：
| customer_id | product_id | SUM(quantity) |
|---|---|---|
| 001 | 101 | 30 |
| 002 | 103 | 20 |
查询结果，当启用only_full_group_by时：
“Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘orders.product_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”
可以看到，在启用only_full_group_by模式之后，查询1将会报错，提示我们非聚合列product_id没有在GROUP BY子句中包含，从而保证了结果的准确性。
only_full_group_by是MySQL中的一种SQL模式，要求在使用GROUP BY语句进行分组时，SELECT语句中的非聚合列必须在GROUP BY子句中出现。启用only_full_group_by模式可以确保查询