Ubuntu16.04+MySQL5.7.27;
将数据从旧的数据库(MySQL5.1)迁移到新的数据库(MySQL5.7)中时,查询语句不变的情况下,报如下错;
Cause: java.sql.SQLException:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mwyq_db.news.news_time'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
修改后应该如下图所示,未修改前有ONLY_FULL_GROUP_BY
选项;
SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
SELECT @@session.sql_mode;
修改后
以root
用户进入mysql
,然后添加如下配置;
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';
SET @@session.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
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';
vim /etc/mysql/my.cnf
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
MySQL8.0中,不支持NO_AUTO_CREATE_USER
,所以在修改配置时,需要将这一项去掉;