1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

来源：互联网发布：睡衣推荐知乎编辑：程序博客网时间：2024/06/03 03:47

mysql在子查询in中用了limit，然后报错：

[SQL] select distinct(p.id) poetryId,content poetryDesc,author poetryAnthor,l.backImg labelImg,p.fullContent flag,archaics,moods,scenes,hots,works,2 collectionType,l.archaicsImg,p.worksName from dd_poetry p join dd_label l on l.id = p.archaics  where 1=1 and p.id in( select id from dd_poetry order by rand()  limit 100 )[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

in (select id from table limit 100)的时候报错~~~~

解决办法：

在limit的外层在套一层：in (select id from (select id from table limit 100) as t)的时候报错~~~~

select distinct(p.id) poetryId,content poetryDesc,author poetryAnthor,l.backImg labelImg,p.fullContent flag,archaics,moods,scenes,hots,works,2 collectionType,l.archaicsImg,p.worksName from dd_poetry p join dd_label l on l.id = p.archaics  where 1=1 and p.id in(select t.id from (select id from dd_poetry order by rand()  limit 100) as t)

这里用了order by rand()耗性能的关键字，但是如果使用主键id来随机的话，就不会影响性能~

先随机100条再来查询

0 0