[Error Code: 1055. Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is...];[Error 1067: Invalid default value for...]
报错详细内容
1.group by没有select的全部字段
Error Code: 1055. Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx'
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
2想设置DATETIME的字段默认值为0000-00-00 00:00:00
Error 1067: Invalid default value for ...DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
解决
其实就是把报错的sql_mode都删掉
//Error Code: 1055 GROUP BY报错解决
SET @@sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
//Error 1067 默认值设置报错解决
SET @@sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE',''));
执行 select @@sql_mode; 可查看
测试
1.设置默认值报错
执行SET @@sql_mode=(select replace(@@sql_mode,‘NO_ZERO_IN_DATE,NO_ZERO_DATE’,‘’));后就成功创建了。
2.查询报错(又不允许修改SQL文的情况)
执行SET @@sql_mode=(select replace(@@sql_mode,‘NO_ZERO_IN_DATE,NO_ZERO_DATE’,‘’));就查询成功了。