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

来源:互联网 发布:商标域名注册管理局 编辑:程序博客网 时间:2024/04/30 13:17


这是前一段时间做一个需求的调研,需求就是很快的从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.idORDER 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

1 0
原创粉丝点击