MySQL 5.7 group by查询异常 this is incompatible with sql_mode=only_full_group_by
一、异常摘要
Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
二、原因分析
sql_mode=only_full_group_by：group by聚合操作，select中的列，需要在group by中出现，或者来源于聚合函数的值，否则异常
三、解决办法
3.1 方法一
1 # 查看当前的sql_mode 2 SELECT @@global.sql_mode 3 SHOW VARIABLES LIKE '%sql_mode%'; 4 5 # 修改sql_mode 6 SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
3.2 方法二
修改my.ini配置文件
1 [client] 2 port=3306 3 default-character-set=utf8 4 5 [mysqld] 6 # 设置为自己MYSQL的安装目录 7 basedir=D:\\software\\mysql-5.7.39 8 # 设置为MYSQL的数据目录 9 datadir=D:\\software\\mysql-5.7.39\\data 10 port=3306 11 character_set_server=utf8 12 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 13 #开启查询缓存 14 explicit_defaults_for_timestamp=true 15 skip-grant-tables 16 #默认引擎 17 default-storage-engine=InnoDB
启动mysql服务提示：The innodb_system data file ‘ibdata1’ must be writable：
删除mysql的datadir目录下的ib_logfile0、ib_logfile1，这两个文件是用于故障数据恢复时的日志文件，也就是redo log。
重新启动mysql服务，提示Can't find error-message file 'D:\software\mysql-5.7.39\share\errmsg.sys'. Check error-message file location and 'lc-message-dir' configuration directive：
mysql 配置文件my.ini的basedir和datadir目录路径单斜杠修改为双斜杠，保存后，启动mysql服务即可：
