Mysql 升级到 5.7.16，group by 一直报home brew on columns in GROUP BY clause; this is incompatible with sql

来源：互联网发布：windows垃圾清理bat 编辑：程序博客网时间：2024/06/03 20:04

Mysql 版本

➜  /etc mysql -vWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.16 HomebrewCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

错误信息

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'txdiag.agents.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决办法
复制配置文件 my-default.cnf to /etc/my.cnf 这里特别注意文件文件名一定要是my.cnf千万别改，且一定要放到etc文件夹下面

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnfsudo vi /etc/my.cnf +31

删除掉原来的sql_mode=”…”，写入下面这句话即可

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

重启mysql

mysql.server restart

如果想要查看sql_mode是否配置成功,进入mysql执行下面命令即可show variables like '%sql_mode%'

➜  /etc mysql -u user -p passwordmysql> show variables like '%sql_mode%'    -> ;+---------------+------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value                                                                                                                  |+---------------+------------------------------------------------------------------------------------------------------------------------+| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+---------------+------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> show variables like '%sql_mode%';+---------------+------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value                                                                                                                  |+---------------+------------------------------------------------------------------------------------------------------------------------+| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+---------------+------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>

0 0