MySQL实现随机查询一条或多条数据

一、随机查询一条数据

SELECT * FROM `table` AS t1 
JOIN (
    SELECT ROUND(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;

二、随机查询多条数据

SELECT * FROM `table` AS t1 
JOIN (
    SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)
) AS id 
from `table` limit 50) AS t2 on t1.id=t2.id
ORDER BY t1.id;

 

参考：https://blog.csdn.net/weixin_44870139/article/details/113420009?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-113420009-blog-118443275.pc_relevant_3mothn_strategy_recovery&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-113420009-blog-118443275.pc_relevant_3mothn_strategy_recovery&utm_relevant_index=2