mysql中随机取出一条数据_[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