MySQL查询 之 随机查询

mysql实现随机获取几条数据的方法

对SQL语句的解释说明：
tablename--表名
m--想要获取的数据条数
t1--tablename表的别名

1：SELECT * FROM tablename ORDER BY RAND() LIMIT m；

2：SELECT * FROM tablename WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM tablename ) ORDER BY id LIMIT m;

3：SELECT * FROM tablename AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM tablename )) AS id ) AS t2 WHERE t1.id  >= t2.id
ORDER BY t1.id ASC LIMIT m;

4：SELECT * FROM tablename WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM tablename ))) ORDER BY id LIMIT m;

5：SELECT * FROM tablename WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM tablename )-(SELECT MIN(id) FROM tablename )) + (SELECT MIN(id) FROM tablename ))) ORDER BY id LIMIT m;

6：SELECT * FROM tablename AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM tablename )-(SELECT MIN(id) FROM tablename ))+(SELECT MIN(id) FROM tablename )) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT m;

1的查询时间>>2的查询时间>>5的查询时间>6的查询时间>4的查询时间>3的查询时间，也就是3的效率最高。