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：