MySQL数据库升级后遇到问题

一、问题

MySQL数据库升级后程序报错（5.7.22升级为5.7.23）：

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.u.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.u.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.u.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、原因

1、SQL语句不严谨，select字段没全在group by中包含。

2、数据库版本导致：sql_mode=only_full_group_by

用sql查询 select @@global.sql_mode，结果如下：

新库：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

旧库：
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

二、解决

1、修改SQL，使规范。

2、修改数据库配置中的sql_mode值

（1）打开终端执行：sudo vim /usr/local/mysql/my.cnf

（2）在[mysqld] 组下找到sql-mode键

（3）备份改行，再改为新值：sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

（4）重启MySQL服务：sudo service mysql restart

------------------------------------------------------------------------------------------------------------------------------------
暂时修改

select @@sql_mode;

set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 

或
set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 

=====

全局
select @@global.sql_mode;

set global sql_mode=(select replace(@@global.sql_mode,'ONLY_FULL_GROUP_BY','')); 

------------------------------------------------------------------------------------------------------------------------------------

疑问：tomcat7+jdk7环境下出现的这个问题，tomcat8+jdk8下不会出现这个问题。