随机选择记录（mysql）

以下提供三种方法以便随机选择记录，根据实际情况选择合适的方法。

1.order by rand() limit N

SELECT * FROM `user` ORDER BY RAND() LIMIT 1;

SELECT * FROM `user` ORDER BY RAND() LIMIT 3;
<!-- 随机1、3条语句 -->

这种方式不适于执行数据量多的表，因为是先给表进行排序，然后挑选随机的行数。

2.结果集关联

SELECT  ROUND(RAND() * ( SELECT MAX(id)  FROM `user`)) as id;

SELECT u.* FROM `user` AS u JOIN (SELECT  ROUND(RAND() * (SELECT  MAX(id) FROM  `user` )) AS id ) AS r WHERE  u.id >= r.id LIMIT 1;

这种方式需要多次执行查询以获取多个随机行，因为如果增加返回行数限制，查询将只提供从随机选择的行开始的顺序行。

3.变量

SELECT * FROM (SELECT  ROUND(RAND() * (SELECT MAX(id) FROM `user`)) random_num, @num:=@num + 1 FROM (SELECT @num:=0) AS a, `user` LIMIT 2) AS b,`user` AS t WHERE b.random_num = t.id;

select * from $table join (select round(RAND() * ((SELECT MAX(id) FROM $table where $cond)-(SELECT MIN(id) FROM $table where $cond))+(SELECT MIN(id) FROM $table where $cond)) as id) as t2 where $table.id >= t2.id and $cond limit 1

请注意，用户定义的变量是特定于连接的。所以这种技术方法不能用于连接池。此外，主键必须是整数类型，其值必须在值是连续没有间隙的序列中