取随机记录

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 ;