mysql5.7刷脏报错_解决升级到MySQL5.7.5后SQL报错

问题

在MySQL升级到5.7.5之后，有些之前运行的好好的sql语句突然报错了，类似下面的错误，这是因为MySQL 5.7.5默认是遵循SQL99，比之前SQL92更加严格，俗称严格模式

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'db_name.news.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

快速解决方法

在my.cnf配置文件里搜索'sql-mode'，然后把sql-mode=""前的;去掉，保存重启MySQL服务器(注：此方法不适合基于Laravel 5的项目)

...

; The default SQL mode in MySQL 5.7 includes these modes:

; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION.

; no line sql-mode="" gives default SQL mode

; To clear the SQL mode explicitly, set it to an empty string using sql-mode=""

sql-mode=""

;sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

...