SQL_MODE之ONLY_FULL_GROUP_BY

前言：SQL_MODE是MySQL中的一个系统变量（variable），可由多个MODE组成，每个MODE控制一种行为，在MySQL 5.7版本中默认的模式有：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；可使用**select @@sql_mode;**来查看自己电脑中有哪些mode；

本次主要讲解ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY

官方文档的解释：Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。请看举例：

• 首先新建如下表(为了演示我已将mode中添加了ONLY_FULL_GROUP_BY)

• 运行如下sql: __select * from product __

• 运行如下sql: select * from product GROUP BY product_name,问题出现了

*代表全部字段，而group by 字段只有一个project_name,所以报错

• 运行如下sql: select price,brand_name from product GROUP BY price

• 运行如下sql:select price,brand_name from product GROUP BY price,brand_name,category_name

• 运行如下sql:select price,brand_name from product GROUP BY brand_name,category_name,price

以上可以看出，当开启ONLY_FULL_GROUP_BY时，只有满足最上方条件才可以查询成功，且默认by的是离by最近的那个字段，即：

select a,b from table group by a,b,c; (正确)
select a,b,c from table group by a,b; (错误)

再看一下之前出问题的sql

SELECT
	ymv.`YEAR_MONTH` yearMonth,
	ifnull(au.total, 0) total
FROM
	year_month_view ymv
LEFT JOIN (
	SELECT
		DATE_FORMAT(au.created_at, '%Y-%m') yearMonth,
		COUNT(id) total
	FROM
		admin_user au
	WHERE
		DATE_FORMAT(au.created_at, '%Y-%m') > DATE_FORMAT(
			date_sub(curdate(), INTERVAL 12 MONTH),
			'%Y-%m'
		)
	AND is_deleted = 0
	GROUP BY
		yearMonth
) au ON ymv. `YEAR_MONTH` = au.yearMonth
GROUP BY
	ymv.`YEAR_MONTH`

因为group by 后面只有一个字段，而select后面有两个，所以会报错

• 因此有两种修改方式
  • 第一种为补上字段，并将你希望作为分类的字段放在by的旁边
  • 第二种为修改SQL_MODE，将ONLY_FULL_GROUP_BY去掉
  • 第三种为添加any_value(field)函数

SELECT
	ymv. `YEAR_MONTH` yearMonth,
	ifnull(au.total, 0) total
FROM
	year_month_view ymv
LEFT JOIN (
	SELECT
		DATE_FORMAT(au.created_at, '%Y-%m') yearMonth,
		COUNT(id) total
	FROM
		admin_user au
	WHERE
		DATE_FORMAT(au.created_at, '%Y-%m') > DATE_FORMAT(
			date_sub(curdate(), INTERVAL 12 MONTH),
			'%Y-%m'
		)
	AND is_deleted = 0
	GROUP BY
		yearMonth
) au ON ymv. `YEAR_MONTH` = au.yearMonth
GROUP BY
	ymv.`year_month`,total

• 如何修改SQL_MODE

  • 全局级别：set global sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES’;

  • 回话级别：set session sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES’;

  • 更改MySql配置文件my.ini：默认安装位置如下：C:\ProgramData\MySQL\MySQL Server x.x

另外，在写这篇文章中发现GROUP BY其实是有很多门道的，有兴趣可以参考官方文档

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html