MySql5.7 报错 1055

我今天在创建视图的时候mysql一只报错 在5.6 的版本就没有这个问题

sql语句如下

SELECT
 `t`.*,
 count(t_q.question_id) AS c_num
FROM
 `ask_topic` AS `t`
 LEFT JOIN `ask_topic_question` AS t_q ON `t`.`topic_id` = `t_q`.`topic_id` 
GROUP BY
 `t_q`.`topic_id` 
ORDER BY
 `c_num` desc

如吧LEFT JOIN 改为 INNER JOIN 就不报错

报错信息如下

  • [Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'itbull_ask.t.topic_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

解决方案

  • 改动my.ini文件(WIndows下) my.conf(LInux下)
  • 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> select @@sql_mode ;
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | 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 |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    返回的值中 把"ONLY_FULL_GROUP_BY,"删掉
  • 重启Mysql