MySQL 5.7默认ONLY_FULL_GROUP_BY的语义

最新推荐文章于 2024-07-19 21:33:34 发布

zuozhiji

最新推荐文章于 2024-07-19 21:33:34 发布

阅读量697

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/jolly10/article/details/79804071

版权

MYSQL 专栏收录该内容

108 篇文章 3 订阅

订阅专栏

今天有一个网友提到一个问题：

第一反应就是这个语句写错了，因为按照 sql99标准来说group by 后面的列名必须是select语句所有非聚集的列才对。

不过从这个错误的信息中对看到sql_mode=only_full_group_by这个参数，那么这个参数是什么意义？如果取消这个参数会有什么变化？

首先检查一下当前数据库sql_mode的值，数据库版本是5.7：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

mysql> 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.01 sec)

建立一个测试的表：

mysql> select * from tt;
+------+-------+
| id   | count |
+------+-------+
|    1 |     1 |
|    1 |     2 |
|    2 |     3 |
|    2 |     4 |
+------+-------+
4 rows in set (0.00 sec)

mysql> select id,count,max(count) from tt group by  id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'l5m.tt.count' 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 从sql_mode中拿掉会是什么样的情形。

[root@qht131 ~]# cat /etc/my.cnf | grep sql_mode
sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[root@qht131 ~]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

mysql> select id,count,max(count) from tt group by  id;
+------+-------+------------+
| id   | count | max(count) |
+------+-------+------------+
|    1 |     1 |          2 |
|    2 |     3 |          4 |
+------+-------+------------+
2 rows in set (0.00 sec)

居然执行成功了。这个参数也可以动态改，不用重启mysql

mysql> set global 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';
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| 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)

mysql> ^DBye
[root@qht131 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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)

不过从官方文档中显示，从5.7.5开始，sql_mode默认开启only_full_group_by，也就是说默认是group by子句应该包含所有seelct语句非聚集的列。