Mysql8.0及以上only_full_group_by以及其他关于sql_mode原
因。。。
先贴本⼈配置⽂件：为docker安装的MySQL 挂载的配置⽂件
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
mysql配置与安装过程# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# sql/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set=utf8
# Custom config should go here
!includedir /etc/mysql/conf.d/
今天我们来说下 mysql8 关于only_full_group_by的问题，相信每个⼈在使⽤group by 函数时会突然跳出⼀个报错信息：
ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'postscan.verifyDelayLog.auditor' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible withsql_mode=only_full_group_by
在mysql8.0以上的版本中，对于 group by 的这种聚合操作，如果在select 中的列，没有在group by 中出现，那么这个SQL是不合法的，因为列不在group by的从句中，所以对于设置了这个mode的数据库，在使⽤group by 的时候，就要⽤
MAX()，SUM()，ANT_VALUE()的这种聚合函数，才能完成GROUP BY 的聚合操作，那么话说回来了，如何关闭呢？
经过我们⼀番百度之后，获取的结果是关于 only_full_group_by ，但是按照教程所说，只要修改了myf，
【
在myf添加如下配置
[mysqld]
sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION
】
配置⽂件加⼊相应参数[以下会讲到]，重启的时候，依然差强⼈意，甚⾄给出教程的⼈并没有亲⼒亲为的去测试，就张贴了出来，还会带来
新的mysql的报错，或者是完全启动报错，今天我就来详细与⼤家⼀⼀说明这些参数，及解决办法。
sql_mode这个变量，很容易被忽视，默认值是空值，在这种设置下是可以允许⼀些⾮法操作的，⽐如
允许⼀些⾮法数据的插⼊。在⽣产环
境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。
sql_mode常⽤值如下：
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个SQL是不合法
的，因为列不在GROUP BY从句中。
NO_AUTO_VALUE_ON_ZERO：该值影响⾃增长列的插⼊。默认设置下，插⼊0或NULL代表⽣成下⼀个⾃增长值。如果⽤户 希望插⼊的
值为0，⽽该列⼜是⾃增长的，那么这个选项就有⽤了。
STRICT_TRANS_TABLES：在该模式下，如果⼀个值不能插⼊到⼀个事务表中，则中断当前的操作，对⾮事务表不做限制
NO_ZERO_IN_DATE：在严格模式下，不允许⽇期和⽉份为零
NO_ZERO_DATE：设置该值，mysql数据库不允许插⼊零⽇期，插⼊零⽇期会抛出错误⽽不是警告。
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产⽣错误⽽⾮警告。如 果未给出该模式，那么
数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：禁⽌GRANT创建密码为空的⽤户
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁⽤或未编译，那么抛出错误。不设置此值时，⽤默认的存储引擎替代，并抛出⼀
个异常
PIPES_AS_CONCAT：将"||"视为字符串的连接操作符⽽⾮或运算符，这和Oracle数据库是⼀样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES：启⽤ANSI_QUOTES后，不能⽤双引号来引⽤字符串，因为它被解释为识别符
说明介绍完了，但是在8.0中这么设置下依然会报错，原因如下：
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
8.0以上已经取消了NO_AUTO_CREATE_USER这个关键字，删掉sql语句中的这个关键字即可
最终，/etc/myf 配置⽂件中 修改 ：
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITU
重启mysql服务，⼤功告成！~
在此，我给出另外两种解决⽅案：
第⼆种办法不⽤修改配置⽂件，使⽤navicat修改
进⼊命令⾏界⾯
输⼊：SELECT @@GLOBAL.sql_mode;
结果：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO
然后我们来修改sql_mode
set GLOBAL sql_mode =
'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
';
再运⾏你的sql，group by就可以使⽤了，但是这个⽅法不治本 ，当重启mysql后，依然使⽤不了group by 所以，第⼀种⽅法，是最有效
的。
第三种办法不⽤修改配置⽂件，使⽤navicat修改
进⼊命令⾏界⾯
输⼊：SELECT @@sql_mode; 注意：这边缺省了session，完整的是：SELECT @@SESSION.sql_mode;
结果：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO
然后我们来修改sql_mode
输⼊：set SESSION sql_mode
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN ';
再运⾏你的sql：
*****还是报only_full_group_by错*****
>>####解决⽅法>>>####
在你查询的语句之前set sql_mode
set SESSION sql_mode
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN ';
以后再新建查询（包含group by）语句也不⽤再加set sql_mode 直到你关闭这次连接为⽌
然后你再运⾏你的sql是不是可以运⾏啦
SET GLOBAL sql_mode
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN
SELECT @@GLOBAL.sql_mode;
关于办法3的解释：
1.其实讲第三种⽅法的时候说过SELECT @@sql_mode; 其实这边缺省了session，完整的是：SELECT @@SESSION.sql_mode;
2.SESSION是当前会话的意思---->这句话就解释了直到你关闭这次连接为⽌
3.那为什么在命令⾏set sql_mode，然后再新建查询还是没⽤呢？
4.其实你只要在新建查询中输⼊SELECT @@sql_mode；你会发现你在命令⾏中的设置并没有⽣效
5.所以，我们只能重新在这次连接（会话）的新建查询中重新set sql_mode，之后这次连接（这次会话）不再需要
6.这⼀段语⾔解释了“在你查询的语句之前set sql_mode”这句话
对办法2办法3两种⽅法的说明
1.这两种set sql_mode⽅法，都会随着在我部署linux上的mysql重启⽽恢复到我指定的配置⽂件的myf⾥⾯设置的sql-mode选项中的
内容
2.这句话⽐较绕
3.意思就是：linux上的mysql重启后，你在navicat上设置的就不再有效，⽽是依据你指定的配置⽂件中的设定，就是myf这个⽂件中设定
注：现在我将myf列出来，供⼤家参考
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
Mysql配置参数说明
下一篇 »
发表评论