获取指定表中随机一行记录

来源：http://www.hxstrive.com/article/48.htm

 

1、随机排序后获取第一行

        获取随机记录的常见SQL做法，就是对查询结果进行随机排序，然后获取第一行。如：select * from bugs order by rand() limit 1;

        这种方法在数据据量非常小的时候，性能不会有什么大的影响。如果数据量非常庞大时，这将是致命的（因为你需要遍历整个表进行排序）。如果采用随机函数的返回值进行排序，则排序就跟记录本身没有任何关系，则每次排序后的数据是不一样的，这样数据的排序就不能被复用。使用随机函数排序，则不能使用数据库的索引进行排序，索引排序是非常快的。不使用索引排序的后果就是不得不遍历整张表（这是非常慢的）。随机排序的另一个问题就是，好不容易对整个数据进行了排序，但是只用了一条数据，太浪费了。

 

2、在表格ID最大和最小值之间选择一个随机数

        通过数据库或者程序中的随机函数，生成一个指定表ID最小值到最大值之间的随机数。然后将显示这个随机数指定的记录。如下：

select b1.* from bugs as b1 join (select ceil(rand()*(select max(bug_id) from bugs)) as rand_id) as b2 on (b1.bug_id = b2.rand_id);

 

注意：

    要求主键值必须从1开始到最大值，且是连续的。如果漏掉了某些值，则可能获取不到数据（缺点）。如Bugs表：

bug_id（Bug编号） name（Bug名称）

1 BugName01

2 BugName02

4 BugName04

6 BugName06

 

随机获取Bugs表中的一条记录：

select ceil(rand()*(select max(bug_id) from bugs));

有rand() * 6 ==>，如果rand()=0.9，则0.9*6=5.4，向下取整后等于5，从上面的表中可以看出bug_id=5的记录不存在。

 

3、随机找到的下一个有效值

        与上面的方案类似，但解决了表中在最小值和最大值之间存在缝隙的情况，这个查询会返回它随机找到的第一个有效的值。如下：

select b1.* from bugs as b1 join (select ceil(rand()*(select max(bug_id) from bugs)) as rand_id) as b2 where b1.bug_id>=b2.bug_id order by b1.bug_id limit 1;

 

        这个方法解决了没有随机数对应的主键值，即方法2的问题。但是在缝隙后面的第一列的选中几率会随着缝隙的增大而增大。如下：

bug_id（Bug编号） name（Bug名称）

1 BugName01

2 BugName02

100 BugName001

        在上面这章表中随机数字位于3到100之间的都将选中bug_id=100的这条记录，那么bug_id=100这条记录的选中概率将非常高。

 

注意：

    当表中数据向ID值之间的缝隙不大并且每个值要被等概率选中的重要性不高时可以考虑这种方案