Mysql 5.7.x this is incompatible with sql_mode=only_full_group_by

蔚落 2021-12-23 16:53 152阅读 0赞

# 一、什么是sql\_mode=only\_full\_group\_by #

only\_full\_group\_by是mysql5.7.x默认的新增的查询模式  
通过`select @@global.sql_mode`可以看到：  
![在这里插入图片描述][20190626111839406.png]

# 二、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服务即可。

[20190626111839406.png]: /images/20211223/e0a1ef2818a44dd384980848b3f4a1ba.png