mysql5.7.x版本sql查询(group by)异常

321 阅读1分钟

在本地执行查询语句查询成功线上服务器就查询失败!!

异常信息

错误代码: 1055
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'like.like_blog_merge.account_id' which is not functionally dependent on columns in GROUP 
BY clause; this is incompatible with sql_mode=only_full_group_by

方式一

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';
FLUSH PRIVILEGES; //刷新数据库

就可以正常查询了、但是重启服务后还是会出现该情况!!!

方式二 找到/etc/mysql/conf.d/mysql.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

看家命令!!!

systemctl restart mysql   // 重启mysql
systemctl start mysql     // 启动mysql
systemctl stop mysql      // 停止mysql
systemctl status mysql    // 查看mysql当前状态