DockerMysql5.7及以上版本ONLY_FULL_GROUP_BY报错的解决方法

发布网友 发布时间：2024-09-17 08:03

我来回答

共1个回答

热心网友 时间：2024-09-29 04:40

背景

最近开发的时候，需要使用MySQL的数据库，在使用groupby的时候，生产环境使用的是5.6版本，但是开发机上面装的docker版本是5.7，在调用接口的时候，发现报错了，通过查询对应的资料，是因为MySQL5.7版本，默认开启了ONLY_FULL_GROUP_BY，所以在使用groupby的时候，不能存在多余的字段信息。

现象回顾表结构准备CREATETABLE`user`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(255)DEFAULTNULL,`sex`varchar(255)DEFAULTNULL,`age`intDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciidnamesexage1张三男212李四男203小花女21

执行SQL

SELECT*FROM`user`GROUPBYage

出现报错1055-Expression#1ofSELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn'test.user.id'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by,Time:0.064000s

原因分析

MySQL的官方文档，给出如下的解释:

MySQL5.7.5andlaterimplementsdetectionoffunctionaldependence.IftheONLY_FULL_GROUP_BYSQLmodeisenabled(whichitisbydefault),MySQLrejectsqueriesforwhichtheselectlist,HAVINGcondition,orORDERBYlistrefertononaggregatedcolumnsthatareneithernamedintheGROUPBYclausenorarefunctionallydependentonthem.(Before5.7.5,MySQLdoesnotdetectfunctionaldependencyandONLY_FULL_GROUP_BYisnotenabledbydefault.Foradescriptionofpre-5.7.5behavior,seetheMySQL5.6ReferenceManual.)

官方大致的意思是说，在5.7.5版本之后，将会开启ONLY_FULL_GROUP_BY，开启此配置之后，在select、having或者orderby的时候，将拒绝使用非聚合列的查询。

针对上述的SQL，也就是在select+groupby的时候，只能查询与groupby相关列的查询。

解决办法方法一：优化SQL

其实个人觉得，最好的办法，就是优化SQL，剔除掉无关的查询操作，将与groupby相关的查询去掉:

SELECTcount(1),ageFROMuserGROUPBYage

方法二：更改配置文件

将容器的内的配置文件，拷贝到宿主机，挂接映射关系，然后在mysqld下增加sql_mode的配置sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

docker-compose.yml

version:'3.1'services:db:image:mysqlcommand:--default-authentication-plugin=mysql_native_passwordrestart:alwaysvolumes:-/root/docker-mysql/conf/mysql:/etc/mysql-/root/docker-mysql/mysql:/var/lib/mysqlenvironment:MYSQL_ROOT_PASSWORD:rootcontainer_name:test-mysqlports:-3307:3306

my.cnf

[mysqld]pid-file=/var/run/mysqld/mysqld.pidsocket=/var/run/mysqld/mysqld.sockdatadir=/var/lib/mysqlsql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONsecure-file-priv=NULL#Customconfigshouldgohere!includedir/etc/mysql/conf.d/

重启容器，查看效果

mysql>SELECT@@sql_mode;+----------------------------------------------------------------------------------------------------+|@@sql_mode|+----------------------------------------------------------------------------------------------------+|STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|+----------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>SELECT*FROMuserGROUPBYage;+----+--------+------+------+|id|name|sex|age|+----+--------+------+------+|1|张三|男|21||2|李四|男|20|+----+--------+------+------+2rowsinset(0.00sec)方法三：更改启动命令

docker-compose.yml

version:'3.1'services:db:image:mysqlcommand:mysqld--sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"--default-authentication-plugin=mysql_native_passwordrestart:alwaysvolumes:-/root/docker-mysql/conf/mysql:/etc/mysql-/root/docker-mysql/mysql:/var/lib/mysqlenvironment:MYSQL_ROOT_PASSWORD:rootcontainer_name:test-mysqlports:-3307:3306

销毁容器：docker-composedown重启容器：docker-composeup-d

查看效果：

mysql>SELECT@@sql_mode;+----------------------------------------------------------------------------------------------------+|@@sql_mode|+----------------------------------------------------------------------------------------------------+|STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|+----------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>setnamesutf8;QueryOK,0rowsaffected,1warning(0.00sec)mysql>SELECT*FROMuserGROUPBYage;+----+--------+------+------+|id|name|sex|age|+----+--------+------+------+|1|张三|男|21||2|李四|男|20|+----+--------+------+------+2rowsinset(0.00sec)