sql_mode之only_full_group_by
- 2019 年 11 月 6 日
- 筆記
sql_mode之only_full_group_by
今天上班的时候，业务方问了我这样一个问题：能不能把线上的sql_mode值改为和测试环境一致？因为我们在测试环境上写的sql在线上可能会出错，原因是线上的环境设置了sql_mode=only_full_group_by。
听到这个需求的时候，我还是很震惊的，这个线上的环境竟然还有人想修改，而且是往更加宽松的程度上去修改，这我肯定不能同意啊。不过也可以理解他们，他们可能没有意识到这个参数的作用，只是从自己的角度出发，想着这样能够解决他们的问题。后面我了解了一下情况，大概的情况就是他们写了一个sql，使用了group by，然后group by里面的字段只有一个，但是select语句中查询了多个字段，导致MySQL报了类似下面的错误：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
大概就是说这个select语句中的字段了group by中的字段不一致，这在MySQL中是不被允许的。
了解了需求之后，我大概举了一个例子，对这种情况进行了一个测试，且看如下例子：
[email protected]:yeyztest 22:15:04>>select * from test; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec) [email protected]:yeyztest 22:15:13>>select @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
这个例子中创建了一个test的表，其中id是主键，其他两个字段是普通的字段，然后sql_mode包含了only_full_group_by，然后我们开始写SQL来查看是否可以通过：
[email protected]:yeyztest 22:15:21>>select * from test group by age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
可以看到，这个问题复现了，我们使用了select *的语法，然后最后group by一个age字段，这样的话，id和score字段没有用到，提示我们的是不可以执行，因为前后字段数量不一致。那么如果我们使用三个字段做group by呢？
[email protected]:yeyztest 22:16:10>>select * from test group by age,score,id; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 9 | 2 | 45 | | 5 | 5 | 25 | | 10 | 5 | 50 | | 8 | 8 | 40 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec)
可以看到，如果使用了group by三个字段，那么这个结果是可靠的，没有报错误，而且是按照group by最近的一个字段age来进行的分组，再来看单独使用id的情况以及使用id和score的情况：
[email protected]:yeyztest 22:16:25>>select * from test group by id; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec) [email protected]:yeyztest 22:17:05>>select * from test group by score,id; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec)
我们发现，都可以执行成功。虽然前面使用了select *后面的group by只使用了部分字段，但是依旧可以成功，没有报错，这里我就有一些疑问了，为什么这个也是部分字段但是却没有报错，那么如果我不使用id，使用age和score的组合呢？
[email protected]:yeyztest 22:17:08>>select * from test group by score,age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
我们可以发现，单独使用这两个字段age和score进行group by，那么结果一定是不可靠的，因为会报错。问题到底出在那里呢？把select * 改成age和score试试：
[email protected]:yeyztest 22:19:02>>select age,score from test group by age,score ; +------+-------+ | age | score | +------+-------+ | 1 | 5 | | 2 | 10 | | 2 | 45 | | 5 | 25 | | 5 | 50 | | 8 | 40 | | 8 | 55 | +------+-------+ 7 rows in set (0.00 sec)
发现这样是可以的，当然，这样符合要求，就是group by后面的字段和select 的字段一致。
以上都是有这个参数的情况，再来看看没有only_full_group_by的情况，先切换到这种非严格模式：
[email protected]:yeyztest 22:17:35>>set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) [email protected]:yeyztest 22:17:54>>select * from test group by score,age; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yeyztest.test.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [email protected]:yeyztest 22:17:58>>set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) [email protected]:yeyztest 22:18:20>>select * from test group by score,age; +----+------+-------+ | id | age | score | +----+------+-------+ | 1 | 1 | 5 | | 2 | 2 | 10 | | 5 | 5 | 25 | | 8 | 8 | 40 | | 9 | 2 | 45 | | 10 | 5 | 50 | | 11 | 8 | 55 | +----+------+-------+ 7 rows in set (0.00 sec)
上面两个测试，仅仅是set global和set session的区别，可以看到，当我们set global的时候，这个sql_mode相当于还是以前的，要使他生效，需要重新连接进来，也就是使用新会话，否则不会生效，而我们使用了set session之后，当前回话的sql_mode就直接被改掉了。也就支持select和group by的前后字段不一致了。
经过我们上面这么多实验，可以得到下面的结论：
1、当我们的sql_mode使用了only_full_group_by的时候，如果要想实现select的字段和group by的字段数量不相同而查询语句不出错，那么我们必须在group by后面添加主键字段，否则一定会出错。
2、当我们去掉sql_mode的only_full_group_by的时候，之所以前后字段数量不一致还能查询成功，实际上是mysql为我们进行了补齐。这种情况下我们不必保持前后字段数量一致，但是需要知道，这是一种不好的习惯，在我们日常写sql的时候，还是尽量要在严格模式下面写，这样mysql会为我们进行校验，如果一旦不合适，就会抛出错误，有助于提升你sql语句的健壮性。
