一、异常摘要

Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'local-bdccg.da.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服务提示：

删除mysql的datadir目录下的ib_logfile0、ib_logfile1

重新启动mysql服务，提示：

mysql 配置文件my.ini的basedir和datadir目录路径单斜杠修改为双斜杠，保存后，启动mysql服务即可：