Mysql case when 语句不兼容only_full_group_by问题解决记录

问题背景：

 

项目执行 case when + group by 的语句

运行报错：[2018-12-26 17:50:15] [42000][1055] Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'paydata.edu_syn_hd_ded.CUTTYPE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by   

解决：

先查询sqlmode，尝试在DataGrip中运行SQL语句，如下

use paydata 

set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

但是只在DataGrip环境中生效，通过mybatis还是报错。

需修改数据库服务器配置文件：/etc/my.cnf

添加：

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

保存，重启mysql服务后生效。

tips：

重启数据库服务后，再连接报错Public Key Retrieval is not allowed

连接url后加参数：allowPublicKeyRetrieval=true