一、什么是sql_mode=only_full_group_by
only_full_group_by是mysql5.7.x默认的新增的查询模式
通过select @@global.sql_mode
可以看到:
二、only_full_group_by的特性
那它有什么特性呢? 来测试一下:
- 1.没有聚合函数,使用GROUP BY, 必须包含所有查询的字段
1.1) 分组时,如果没有按主键,则必须包含所有查询的字段
select t.USERCODE, t.ADDRESS, t.BIRTHDAY from tbuser t GROUP BY t.USERCODE, t.ADDRESS, t.BIRTHDAY; -- ok,
select t.USERCODE, t.ADDRESS, t.BIRTHDAY from tbuser t GROUP BY t.USERCODE, t.ADDRESS; -- error
select t.USERCODE, t.ADDRESS, t.BIRTHDAY from tbuser t GROUP BY t.USERCODE; -- error
select t.USERCODE, t.ADDRESS, t.BIRTHDAY from tbuser t GROUP BY t.USERID; -- ok, 当按主键分组,成功
- 2.当查询字段有聚合函数,还有其他字段时, 必须使用GROUP BY
select t.USERCODE, count(t.USERID) from tbuser t; -- error
2.1) 当聚合函数包含了主键时,分组必须按主键,或者按主键除外的其他所有查询字段(可以增加表里的其他字段)
select t.USERCODE, count(t.USERID) from tbuser t GROUP BY t.USERID; -- ok (按主键)
select t.USERCODE, count(t.USERID) from tbuser t GROUP BY t.USERCODE; -- ok (按主键除外的其他所有查询字段)
select t.USERCODE, t.ADDRESS, count(t.USERID) from tbuser t GROUP BY t.USERCODE; -- error
select t.USERCODE, t.ADDRESS, count(t.USERID) from tbuser t GROUP BY t.USERCODE, t.ADDRESS; -- ok (按主键除外的其他所有查询字段)
select t.USERCODE, t.ADDRESS, count(t.USERID) from tbuser t GROUP BY t.USERCODE, t.ADDRESS, t.BIRTHDAY; -- ok (可以增加表里的其他字段)
2.2) 当聚合函数是非主键, 必须按主键,或者按除聚合字段的其他所有查询字段(也可以包括聚合字段和其他表里的字段)
select t.USERCODE, count(t.ADDRESS) from tbuser t GROUP BY t.USERID; -- ok(按主键分组)
select t.USERCODE, count(t.ADDRESS) from tbuser t GROUP BY t.USERCODE, t.ADDRESS; -- ok(按查询的所有字段分组)
select t.USERCODE, count(t.ADDRESS) from tbuser t GROUP BY t.USERCODE; -- ok (按除聚合函数的其他查询字段分组)
select t.USERCODE, count(t.ADDRESS) from tbuser t GROUP BY t.USERCODE,t.BIRTHDAY; -- ok (按除聚合函数的其他查询字段分组(包括表里的字段))
select t.USERCODE, count(t.ADDRESS) from tbuser t GROUP BY t.ADDRESS; -- error
- 3.当查询只有聚合函数时,可以不用GROUP BY
select count(t.USERID) from tbuser t; -- ok
三、如何取消only_full_group_by,兼容5.6呢?
只需要在mysql的配置文件中,[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
ps: 有些windows系统,使用了绿色版的mysql,默认是没有配置文件的,此时可以手动创建一个,默认名称为my.ini,添加相应的配置,下面是个参考例子:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#innodb的缓存,可以根据实际情况调整大小,我这里采取默认值
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = F:\fantsey\javaTools\mysql-5.7.23-winx64
datadir = F:\fantsey\javaTools\mysql-5.7.23-winx64\data
port = 3306
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
character_set_server=utf8mb4
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8mb4
配置好后,重启mysql服务即可。