取随机记录


set @a:=N+1, @b:=-1;
select * from t,
(select id from t
where if(rand()*(M - (@b:=@b+1)) < @a, @a:=@a-1, 0)
limit 10
) as tt
where t.id = tt.id;

其中M是表的行数,这比order by rand() limit N将会快很多,而且应该是均匀随机的(感觉上是,懒的去证明了)。


DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`rand_rows` $$
CREATE PROCEDURE `test`.`rand_rows` (t varchar(64), id_col varchar(64), n int)
BEGIN
set @rand_rows_i:=-1;
set @rand_rows_table_count:=0;
set @rand_rows_limit:=n+1;
set @rand_rows_sql := concat('select count(*) from ', t, ' into @rand_rows_table_count');
prepare st from @rand_rows_sql;
execute st;
deallocate prepare st;
set @rand_rows_sql := concat('select * from ', t, ' as t1, ',
'(select ', id_col,' from ', t,
' where if(rand()*(', @rand_rows_table_count, ' - (@rand_rows_i:=@rand_rows_i+1)) < @rand_rows_limit, @rand_rows_limit:=@rand_rows_limit-1, 0) limit ',
@rand_rows_limit - 1, ' ) as t2 ',
'where t1.', id_col,' = t2.', id_col);
prepare st from @rand_rows_sql;
execute st;
deallocate prepare st;
set @rand_rows_sql := null, @rand_rows_i := null, @rand_rows_table_count := null, @rand_rows_limit := null;
END $$

DELIMITER ;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值