[Mysql]从表中随机取出一条数据sql

这是前一段时间做一个需求的调研，需求就是很快的从mysql一张表中随机取出一条记录。

现在可能也用不到这段sql了。还是记录下，以后如果用的到就不用在查询了。

表说明:

表名称：test

主键id  int类型 

字段 url 字符类型（筛选条件）

字段 dmac 字符类型（筛选条件）

主要用到的是mysql的 rand() 函数。

#----------------------随机取数据------------------------
#http://www.cnblogs.com/hfww/archive/2011/07/08/2223359.html
#按消耗时间从大到小排列, 如果数据集的范围太小或者筛选后没有取到需要一个默认值
SELECT * 
FROM `test` 
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `test` ) and url like "%wifi21%"
ORDER BY id LIMIT 10;

select * from `test` where test.dmac="A8:15:4D:B1:7D:76" and test.url like "%wifi21%"
ORDER BY rand() LIMIT 10;

SELECT * 
FROM `test` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `test`)) AS id) AS t2 
WHERE t1.dmac="A8:15:4D:B1:7D:76" and t1.url like "%wifi21%" and  t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 10; 


SELECT * FROM `test`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `test`))) 
AND dmac="A8:15:4D:B1:7D:76" and url like "%wifi21%"
ORDER BY id LIMIT 10;


SELECT * FROM `test` 
AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `test`)-(SELECT MIN(id) FROM `test`))+( 
SELECT MIN(id) FROM `test`)) AS id) AS t2 
WHERE t1.id >= t2.id and t1.dmac="A8:15:4D:B1:7D:76" and t1.url like "%wifi21%" 
ORDER BY t1.id LIMIT 10;

当时测试时候大概查了这几种方式，按查询速度排序，大致顺序。

本文出自 “orangleliu笔记本” 博客，转载请务必保留此出处http://blog.csdn.net/orangleliu/article/details/40268763