为什么需要随机查询呢?
在实际开发中,我们可能需要在某些栏目中需要随机的现实一些动态信息。例如:在博客系统文章顶部我们有随机现实一些名言警句,此时要求我们随机的查询一条名言警句。下面将介绍在MySql下面随机查询记录的几种方式。
rand()函数
在mysql中,rand()函数用于产生 0(包含)到1(不包含)的随机数,rand() 有两种形式:
(1)rand() 即无参数的,此时产生的随机数是随机的,不可重复的。如下:mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.8766326241908898 |
+--------------------+
1 row in set
对于不带参数的rand()函数很好理解。
(2)rand(n) 即有参数数,如:rand(2) 相当于指定随机数生产的种子,那么这种情况产生的随机数是可重复的。如下:mysql> select rand(2);
+--------------------+
| rand(2) |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set
针对带参数的rand()函数的情况,下面给出个例子:
1、创建表create table demo
(
i int
);
2、插入三条数据到数据库insert into demo(i) values (1),(2),(3);
3、使用带有参数的rand()执行两次查询。如下:
a、第一次查询mysql> select i, rand(2) from demo;
+---+---------------------+
| i | rand(2) |
+---+---------------------+
| 1 | 0.6555866465490187 |
| 2 | 0.12234661925802624 |
| 3 | 0.64497318737672 |
+---+---------------------+
3 rows in set
b、第二次查询mysql> select i, rand(2) from demo;
+---+---------------------+
| i | rand(2) |
+---+---------------------+
| 1 | 0.6555866465490187 |
| 2 | 0.12234661925802624 |
| 3 | 0.64497318737672 |
+---+---------------------+
3 rows in set
分析上面两次同样的查询语句,你会发现,产生的随机数是一样的,即是指定了随机数的种子,那么多次查询的结果是一样的。
直接使用rand()函数排序
该种方式实现起来很简单,就一句话就搞定。代码如下:SELECT * FROM `country` ORDER BY rand() LIMIT 1;
上面语句虽然能够实现随机查询一条,但是在MySql的官方手册,里面针对RAND()的提示大概意思就是“在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描”。 英文原文“You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.”。
注意:不推荐使用该方式,效率太低。
使用rand()计算ID
该种方法实现起来稍微复杂点。大概思路是:利用rand()乘以指定的数,将生成的随机值控制到一个指定的范围类,然后再将该随机值添加一个指定大小的值,使其表示的范围在自己可以控制的范围内。简单草图如下:
上图中,我们可以使用Max-Min来获取控制随机数生成的范围,即 (Max - Min) * rand()。然后再添加Min值,将随机数生成的范围移动到Min和Max之间。最后,获取大于随机ID的记录即可。如果只取一条,则使用Limit 1,当然你也可以获取多条。
SQL代码如下:SELECT * FROM city
WHERE id >= (
(SELECT MAX(id) FROM city)-(SELECT MIN(id) FROM city))*RAND() + (SELECT MIN(id) FROM city
) LIMIT 1
使用带参数的rand()
上面我们使用了没有带rand的函数每次都来生成一个不同的随机值,下面将使用带有参数的rand函数生成一个固定的值。例如:select rand(10);
上面SQL语句不管执行多少次,返回的结构都是一样。
我们可以利用这一特点来实现随机获取记录,大概原理“计算一个处于Min和Max之间的随机数作为rand函数的参数,生成一个固定的值。利用该值和表的ID进行比较,然后通过LIMIT获取一条记录”。SQL代码如下:SELECT * FROM city AS t1
JOIN (
SELECT ROUND(RAND()*((SELECT MAX(id) FROM city)-(SELECT MIN(id) FROM city))+(SELECT MIN(id) FROM city)) AS id
) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1
参考资料: