mysql select rand_解决Mysql坑爹的随机函数rand() | 学步园

原始的sql语句是：

select content poetryDesc,author poetryAnthor,l.backImg labelImg,p.id poetryId,p.fullContent flag from dd_poetry p join dd_label l

on l.id = p.archaics

order by rand()

直接随机在数据量过大的时候，导致数据库服务器cpu瞬间100%

优化之后的sql：

-- SELECT id FROM table t1 JOIN (SELECT round(RAND() * (SELECT MAX(id) FROM table)) AS nid FROM table LIMIT n) t2 ON t1.id = t2.nid;

-- select * from dd_poetry t1 join (select round(rand() * (select max(id) from dd_poetry)) as nid from dd_poetry ) t2 on t1.id = t2.nid

select content poetryDesc,author poetryAnthor,l.backImg labelImg,p.id poetryId,p.fullContent flag from dd_poetry p join dd_label l

on l.id = p.archaics

join (select round(rand() * (select max(id) from dd_poetry)) as nid from dd_poetry ) t2

on p.id = t2.nid