MySQL 不同版本下的group by

MySQL 不同版本下的group by

    大家有没有遇到过这样的坑，在把MySQL升级到更高的版本，一些以前看上去不会出错的group by 操作在这个版本以后就会出现语法报错的情况。这里就来记录下我在使用mysqll分组查询所遇到的坑。

# 创建一张表，并插入几条数据


CREATE TABLE `student` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(8) NOT NULL,
    `sex` varchar(2) NOT NULL,
    `birthday` date DEFAULT NULL,
    `class_id` int(10) unsigned NOT NULL COMMENT '班级Id',
    PRIMARY KEY (`id`),
    KEY `class_id` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

INSERT INTO `student` (`name`, `sex`, `birthday`, `class_id`)
VALUES ('文芳', '男', '1976-02-20', '95033'),
    ('张强', '男', '1974-06-03', '95031'),
    ('赵星', '男', '1975-10-02', '95031'),
    ('王丽', '女', '1976-01-23', '95033'),
    ('曾华', '男', '1977-09-01', '95033'),
    ('王芳', '女', '1975-02-10', '95031');

      然后，我们看到表里面已经有数据：

   

select * from `student` group by `class_id`

报错信息：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

   但是，当sql改成下面这样，就能够正常进行查询，返回结果 ：

 select * from `student` group by `id`

 select id , name  from `student` group by `id`

   分析：

   1）当group by 后面这个字段是主键时，id 便与 select 后面选择的字段均产生了一种关系，即 id 可确定唯一 一条记录。此时便可进行 GROUP BY 了。因为，之所以报错是因为在进行聚合的时候有不能确定的列参与了进来。

   2）5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。MySQL 5.7.5及以上功能依赖检测功能，默认情况启用了ONLY_FULL_GROUP_BY。

   总结：

   1）由于开启了ONLY_FULL_GROUP_BY的设置，select后面跟着的字段，要全部在group by后面跟着。

   2）如果一个字段没有在select 后面和group by字段中同时出现，或者该字段不是聚合函数（AVG，COUNT，SUM，MAX和MIN）的参数的话，那么这条sql查询是被mysql认为非法的，会报错误。

   下面是当我们遇到这种报错情况时，总结的一些排查过程和解决方法。

    一、查询mysql版本

    首先，我们要知道我们之前用的MySQL和现在用的版本各是多少？

# 方式一：
select version();

# 方式二：
show variables like '%version%';

    升级前查询到的mysql版本为：5.7.32-log，升级后查询到的mysql版本为：8.0.18

    不知道大家是否像我一样有疑问，为什么后面加了-log后缀呢？

# MySQL作者Paul DuBois的解释是：
I want to know what the -log means and how it got in there.

如果在启用日志记录的情况下启动服务器,则会添加-log.

如果您正在运行调试版本的服务器,则会添加-debug.

如果服务器在演示模式下运行,则添加-demo

  在源代码 sql/mysqld.cc中,函数set_server_version()：

  ...

  if (opt_log || opt_slow_log || opt_bin_log)

  strmov(end, "-log"); // This may slow down system

  ...

  说明：“This may slow down system” 该注释的含义并不是在版本中添加四个额外字符会降低系统速度,但是日志记录可能会降低系统速度,因此值得注意的是DBA启用了日志记录

   二、 解决办法

   1.  关闭 ONLY_FULL_GROUP_BY

   1）查看sql_mode

# 执行查询语句

SELECT @@sql_mode;

# 查询结果：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

    2）方式一：通过以下脚本关闭

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

   上面这种方式数据库重启后设置会失效，只是修改了内存中的值，不能永久改变。想要永久解决需要在配置文件中修改。

   3） 方式二：配置文件修改

    Windows下MySQL的配置文件是 my.ini，一般会在安装目录的根目录。

    Linux下MySQL的配置文件是my.cnf，一般位置为 /etc/my.cnf

# 在【mysqld】下面添加：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
# 然后，重启mysql
service mysqld restart

   

     但是，因为 ONLY_FULL_GROUP_BY 更加符合 SQL 标准，所以不建议关掉。

 

 参考链接：https://www.cnblogs.com/Wayou/p/mysql_group_by_issue.html