报错:使用 group by 导致错误
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘myphp.xiaofei.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决办法:
1、当前连接有效(MySQL重启后失效)
- 登录mysql后
show variables like '%sql_mode%'
我这里已经修改过了,没有 only_full_group_by
了。
- 执行以下代码,去除 only_full_group_by
# 全局
set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 当前会话
set session sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
2、全局有效(永久有效,配置后重启MySQL)
- 修改 /etc/my.cnf 配置文件
vim /etc/my.cnf
点击i进入编辑模式,在文件的 [mysql] 下,添加以下配置:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
按esc后,输入wq保存退出,然后重启数据库。