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

最新推荐文章于 2023-05-13 12:32:50 发布

xb12369

最新推荐文章于 2023-05-13 12:32:50 发布

阅读量7.1k

点赞数

分类专栏： MySQL 文章标签： in limit

本文链接：https://blog.csdn.net/xb12369/article/details/45152777

版权

MySQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

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条再来查询