在mysql中通过sql语句获取随机数据的方式如下, 经测试,在1000W数据的表中,执行速度在10多毫秒
SELECT * FROM user1 AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM user1) – (SELECT MIN(id) FROM user1)) + (SELECT MIN(id) FROM user1)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 10;
另外一种(参考MySql 是怎样运行的 从根上理解Mysql)
mysql> select count(*) into @C from t; 获取表的数据条数
set @Y1 = floor(@C * rand()); 获取随机数
set @Y2 = floor(@C * rand()); 获取随机数
set @Y3 = floor(@C * rand()); 获取随机数
select * from t limit @Y1,1; //在应用代码里面取Y1、Y2、Y3值,拼出SQL后执行
select * from t limit @Y2,1;
select * from t limit @Y3,1;
参考: https://www.douban.com/note/331011058/