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 ;