1.SELECT * FROM table
ORDER BY RAND() LIMIT n;(n为抽取的数量)
缺点:数据量大时会比较的慢
2.SELECT * FROM table
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM table
) ORDER BY id LIMIT 1;
优点:比1快
缺点:
1.id必须要是唯一
2.因为是按顺序排的,所以一次只能随机抽取1个
3.id必须是连续的且从1开始,否则概率不相等
3.SELECT *
FROM table
AS t1 JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM table
)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;
优点:比2快
缺点:
1.id必须要是唯一
2.因为是按顺序排的,所以一次只能随机抽取1个
3.id必须是连续的且从1开始,否则概率不相等
4.SELECT *
FROM table
AS t1 JOIN (SELECT FLOOR(RAND() * ((SELECT MAX(id) FROM table
)-(SELECT MIN(id) FROM table
))+(SELECT MIN(id) FROM table
)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
优点:和3相比id不需要从1开始
缺点:
1.id必须要是唯一
2.因为是按顺序排的,所以一次只能随机抽取1个
3.id必须是连续,否则概率不相等
函数解释
RAND() : 在0和1之间产生一个随机数,取值范围为[0,1)
FLOOR() : 向下取整
MAX() : 取该列的最大值
ROUND(): 对参数x进行四舍五入的操作,返回值保留小数点后面指定的y位