我今天在创建视图的时候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