简书链接:MYSQL新版问题汇总
文章字数：66,阅读全文大约需要1分钟
MYSQL版本比较新，有好几个错误问题

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

1
2
3
4
5
-- 查看SQL_MODE
SELECT @@sql_mode;

-- 修改SQL_MODE
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

[MYSQL]Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value ‘

1
2
3
4
5
select now();
show variables like "%time_zone%";
 set global time_zone = '+8:00';
 flush privileges;
 show variables like "%time_zone%"

删除数据总数遇到的问题

delete from xxxxx where id in (select t.id from xxxxx limit 0,500);
[Err] 1235 - This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

1
delete from xxxxx   where id in (select t.id from xxxxx    limit 0,500);

修改为

1
delete from xxxxx    where id in (select t.id from (select id from xxxxx    limit 0,500) as t);

查询可以这样写

1
select * from (select id from xxxxx   limit 12) as foo;