100万条数据里面随机取10条

错误的语句

select id,name from news_6 order by rand() limit 10

100万条数据里面随机取10条 纯SQL语句实现

select id,name from news_6 where id > (

select ROUND(RAND() * ((SELECT MAX(id) as max FROM news_6)  -  (SELECT MIN(id) as min FROM news_6))) + (SELECT MIN(id) as min FROM news_6) - 10

) order by id asc limit 10;

上面语句保持在一个小数内10000条，where 语句 遇上带有rand()函数的子语句subquery会有问题。最后用join实现。

select t.* from (

select t1.id,t1.name from news_6 t1 join (

select ROUND(RAND() * ((SELECT MAX(id) as max FROM news_6)  -  (SELECT MIN(id) as min FROM news_6))) + (SELECT MIN(id) as min FROM news_6) - 30 as id

) t2 where t1.id>t2.id order by id asc limit 30

) t order by RAND() limit 10;