mysql随机抽取数据
时间：2021-07-01 10:21:17
帮助过：8人阅读
慢
SELECT * FROM table_name
ORDER BY rand() LIMIT
5;
-- 较慢
SELECT * FROM `
table`
WHERE id
>= (
SELECT floor(
RAND()
* ((
SELECT MAX(id)
FROM `
table`)
-(
SELECT MIN(id)
FROM `
table`))
+ (
SELECT MIN(id)
FROM `
table`)))
ORDER BY id LIMIT
1;
-- 快 `table 有 id 字段
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;
-- 快 `table 没有有 id 字段
select * from (
select @rownum:
=@rownum + 1 as id,value
from `
table`,(
select @rownum:
=0)
as a)
as t1
join (
select round(
rand()
* (
(select max(b.id)
from (
select @rownum_max:
=@rownum_max + 1 as id,value
from `
table`,(
select @rownum_max:
=0)
as a)
as b )
-
(select min(b.id)
from (
select @rownum_min:
=@rownum_min + 1 as id,value
from `
table`,(
select @rownum_min:
=0)
as a)
as b )
)) +
(select min(b.id)
from (
select @rownum_min1:
=@rownum_min1 + 1 as id,value
from `
table`,(
select @rownum_min1:
=0)
as a)
as b )
as id
) as t2
on t1.id
>= t2.id
order by t1.id limit
1
缺点:
每次查询后会获得连续的n条数据
解决办法:
每次查一条数据,重复查询n 次
mysql随机抽取数据
标签：position 字段 abs mysq table 随机 取数据 top sele
