mysql随机显示记录_mysql随机提取记录数的方法

通常情况下我们使用如下的语句随机返回一条记录数

SELECT * FROM tb_dic_class ORDER BY RAND() LIMIT 1,测试发现这个方法并不是最优化的，下面列举了一个效率更好的例子:

1.表记录数

mysql> SELECT count(1) FROM tb_dic_class;

+----------+

| count(1) |

+----------+

|  1000000 |

+----------+

1 row in set (0.37 sec)

2.常用的返回随机记录的方法

mysql>

SELECT * FROM tb_dic_class ORDER BY RAND() LIMIT 1 \G;

*************************** 1. row ***************************

class_id: 839335

class_name: 839335班

createtime: 2014-08-18 14:27:23

modifytime: 2014-08-18 14:27:23

1 row in set (8.69 sec)

ERROR:

No query specified

3.效率更好的方法

mysql> SELECT * FROM tb_dic_class AS t1

->   JOIN (SELECT ROUND(RAND() *

->           (SELECT MAX(class_id) FROM tb_dic_class)) AS id) AS t2 WHERE t1.class_id >= t2.id ORDER BY t1.class_id ASC LIMIT 1 \G;

*************************** 1. row ***************************

class_id: 400243

class_name: 400243班

createtime: 2014-08-18 14:25:23

modifytime: 2014-08-18 14:25:23

id: 400243

1 row in set (1.18 sec)

ERROR:

No query specified

4.下面这个查询效果更佳

mysql> SELECT *

-> FROM tb_dic_class AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(class_id) FROM tb_dic_class)-(SELECT MIN(class_id) FROM tb_dic_class))+(SELECT MIN(class_id) FROM tb_dic_class)) AS id) AS t2

-> WHERE t1.class_id >= t2.id

-> ORDER BY t1.class_id LIMIT 1;

+----------+------------+---------------------+---------------------+--------+

| class_id | class_name | createtime          | modifytime          | id     |

+----------+------------+---------------------+---------------------+--------+

|   448629 | 448629班   | 2014-08-18 14:25:36 | 2014-08-18 14:25:36 | 448629 |

+----------+------------+---------------------+---------------------+--------+

1 row in set (0.20 sec)

--The End --