Mysql 5.7.x this is incompatible with sql_mode=only_full_group_by

一、什么是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服务即可。