mysql5.7 报错1055_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