mysql随机抽取数据

Z时代
2024-01-10
分类：综合

database

-- 慢
SELECT*FROM table_name ORDERBYrand() LIMIT 5;
-- 较慢
SELECT*FROM `table`
WHERE id >= (SELECTfloor( RAND() * ((SELECTMAX(id) FROM `table`)-(SELECTMIN(id) FROM `table`)) + (SELECTMIN(id) FROM `table`)))
ORDERBY id LIMIT 1;
-- 快  `table 有 id 字段
SELECT*
FROM `table` AS t1 JOIN (SELECTROUND(RAND() * ((SELECTMAX(id) FROM `table`)-(SELECTMIN(id) FROM `table`))+(SELECTMIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDERBY t1.id LIMIT 1;
-- 快  `table 没有有 id 字段
select*from (select@rownum:=@rownum+1as id,value from `table`,(select@rownum:=0) as a) as t1 join (
selectround( rand() * ( 
    (selectmax(b.id) from (select@rownum_max:=@rownum_max+1as id,value from `table`,(select@rownum_max:=0) as a) as b ) -
    (selectmin(b.id) from (select@rownum_min:=@rownum_min+1as id,value from `table`,(select@rownum_min:=0) as a) as b )
    )) +
    (selectmin(b.id) from (select@rownum_min1:=@rownum_min1+1as id,value from `table`,(select@rownum_min1:=0) as a) as b ) as id
) as t2
on t1.id>= t2.id
orderby t1.id limit 1