MYSQL5.7版本sql_mode=only_full_group_by问题

  • Cate: MySQL
  • 本文发布较早，其内容可能过时，阅读时请注意甄别。

错误信息
`SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column
'iicityYii.opportunity_conditions.money' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by`

  1. 查看 sql_mode
    select @@global.sql_mode
  2. 去掉 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';

或者：
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

或者，修改mysql配置文件，新增
[mysqld]
sql-mode=""

Tags: none

添加新评论