SELECT list is not in GROUP BY clause_mysql执行的时候报错了

如果你一个系统,以前用的是mysql的版本是:5.6.22

然后,后来,你再安装mysql的时候版本变成5.7.32 等等了,其实就是说,以前你用的

mysql版本是5.6.xx,后来用5.7.xx了,这样在你程序没变的时候,就会报这个错误:

\### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ydy.t1.birthday' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ### The error may exist in file [D:\ALLSVN\ydy\YdyBack\target\classes\mapper\UserMapper.xml] ### The error may involve com.company.project.dao.UserMapper.loadUserByUsername-Inline ### The error occurred while setting parameters ### SQL: select IF(birthday=null,0,year(from_days( datediff(now(),birthday)))) as age, t1.*, del_flag as delFlag from user t1 where t1.username = ? group by username ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ydy.t1.birthday' 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 'ydy.t1.birthday' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

怎么解决?

1.先说原因:

MySQL5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。
比如在使用group by进行分组查询报错

2.在sql命令行中输入select @@sql_mode;这时我们能够看到自己的sql_mode配置,其中如果有ONLY_FULL_GROUP_BY,

那它就是group by查询报错的原因了

select @@sql_mode;

可以看到我这个5.6.22的机器去查询sql_mode:

可以看到,里面就没有:ONLY_FULL_GROUP_BY对吧.

3.然后我再去查看一个:5.7.32的版本去看看:

可以看到数据库版本是5.7.32

然看看查出来的sql_mode的开头是:ONLY_FULL_GROUP_BY对吧. 就是因为这个导致的上面的错误:

4.解决方法:

直接用这种办法解决就可以了,在windows上,和在linux上面是一样的:

vim
/etc/mysql/conf.d/mysql.cnf 编辑配置文件:

可以看到,其实就是把查询出来的,那一段字符串,去掉ONLY_FULL_GROUP_BY,然后又在配置文件mysql.cnf中配置了一下:

在,mysqld这个标签下加上,sql_mode = '' 或者在最下面加上下面的内容都可以的:

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION'

编辑以后退出就可以了,然后

systemctl restart mysqld

或者:

service mysql restart

重启一下mysql就好了