孤陋寡闻了，原来 MySQL 还能这么写？-51CTO.COM

最近给一个我从来没碰过的老系统加点儿功能，本来连测试环境的数据库一切都很顺利，但是为了保证功能在生产数据上没有问题，就准备把一部分生产数据搞到我本地环境上测一下。

结果，果然出现了问题，代码问题就是这样，总在不经意间来到。

依我我知，想要不出现代码问题，最好的方式就是——不写代码！

出现问题，咱就解决啊，我一看日志控制台一大堆红色报错，不对啊，这都不是我刚加的功能报出来的，这样的话，我就放心了。

摘出来一条错误信息是下面这样的，是 SQL 查询语句报错了，报错信息很明显，就是说GROUP BY 后面的条件没有在前面的 SELECT Columns 列表里。

一看其中有两个关键点：

GROUP BY
sql_mode=only_full_group_by

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'c.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.001000s

我把 SQL 简化了一下就是下面这个样子：

select u.id,u.age from user u GROUP BY u.province;

一看这条 SQL，我心想，这SQL 本身就有问题啊，怎么就在系统中呆了这么长时间，但是在测试和生产环境确实就是正常运行的。

我本地一直用MySQL5.7，再看测试和生产数据库，也是 5.7，就是小版本不太一样，按往常经验，小版本应该不会有这么大影响。

那肯定就是配置的问题，那肯定就是报错信息中提到的 sql_mode=only_full_group_by 这个，原谅我孤陋寡闻了，用了 MySQL 这么久，从来没听过这玩意，而且用 GROUP BY就是为了分组聚合，GROUP BY后面的条件要出现在 SELECT 列表里不是很正常吗，除非有两个列有同样的作用，比如一个名称，一个编码，用编码分组，显示名称。要不然分组的意义在哪里呢？

但是系统已经运行了很长时间了，那这 SQL 一定有他存在的意义，不管那么多了，看问题就好了。

sql_mode

然后我给自己科普了一下 sql_mode。

sql_mode 是 MySQL 的一个系统变量，用来控制 MySQL 服务器的 SQL 语法和行为的处理方式。通过配置不同的 sql_mode 值，MySQL 可以在 SQL 语法检查、数据完整性约束、以及查询处理等方面进行不同的操作。

总之，就是 MySQL 会根据这个配置的内容，来灵活的进行语法检查、数据约束等操作，加入的变量越多，控制的就越严格。

发现从 MySQL 5.7 开始，sql_mode 加了很多变量，ONLY_FULL_GROUP_BY就在其中。

使用 SELECT @@sql_mode;可以查询数据库中 sql_mode 配置的变量有哪些，这是 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

既然是变量，就是可以改的，所以，这些变量可以动态的增减，或者索性全部去掉。

那肯定就是测试和生产环境改了这个配置了，上去一查，果不其然，那叫一个干净。后来问之前的同事，了解到之前用的是更早的 MySQL 版本，后来统一升级到了 5.7，然后发现这个问题，所以改了配置。

ONLY_FULL_GROUP_BY

别的不说，只说 ONLY_FULL_GROUP_BY，当数据库中启用了 ONLY_FULL_GROUP_BY 模式后，就要求在 GROUP BY 查询中，SELECT 子句中的每一列都必须要么出现在 GROUP BY 子句中，要么应用聚合函数（如 COUNT()、SUM()、MAX()、MIN() 等）。

这个例子中就是，province这个字段没有在前面 SELECT 的字段列表中。我从刚用 MySQL 时一直都是按照 GROUP BY后面的列必须在前面的查询列中来做的，没想到这个还能改。

select u.id,u.age from user u GROUP BY u.province;

这种不行，下面这个也不行，因为 SELECT 查询列表中的 u.id 不在 GROUP BY 后面的条件中

select u.id,u.age from user u GROUP BY u.age;

改成下面这样才行

select u.id,u.age from user u GROUP BY u.age,u.id;
--  或者
select u.age from user u GROUP BY u.age;

或者，还有一种情况，可以允许 SELECT 中存在 GROUP BY 后面没有的列，就是加聚合函数。

这应该是最常规的用法了。

select max(u.id),u.age from user u GROUP BY u.age;

除了影响 GROUP BY外，还会影响 ORDER BY，看下面这条语句，当开启 ONLY_FULL_GROUP_BY后，会报错

SELECT DISTINCT
	b.title,
	b.create_time 
FROM
	b_user b 
ORDER BY
	b.create_time DESC,
	b.update_time DESC

报错信息：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b.update_time DESC' at line 24, Time: 0.001000s

因为update_time字段不在 SELECT 后面，当然这还是因为加了 DISTINCT。当关闭 ONLY_FULL_GROUP_BY后，就能正常执行了。

关闭 ONLY_FULL_GROUP_BY 模式

如果真的碰到从低版本升级上来的，系统中有很多这样不符合 ONLY_FULL_GROUP_BY规范的语句，最省事的办法就是直接关掉。

最省事儿的方法就是直接改 MySQL 配置文件，找到my.cnf配置文件，将其中的 sql_mode 改成下面这样

sql_mode = ""

然后重启就好了。

最后

建议没有特殊情况，还是打开ONLY_FULL_GROUP_BY，这样能保证你的查询结果不会因为你写错 SQL 而出现莫名奇妙的数据，数据库会及时给你抛出错误，避免你对着一串 SQL 查错时一面懵。

除非你能明确地知道你为什么需要把 ONLY_FULL_GROUP_BY关掉。