Mysql 简明教程
MySQL - Select Random Records
您是否曾经参加过在线考试？如果是，那么您是否有想过这些问题显示的顺序是如何随机的？这些问题通常存储在测试应用程序的数据库中，并逐个随机显示。
Have you ever taken online examinations? If yes, then did you ever wonder how is the order, in which these questions are displayed, random? These questions are usually stored in a database of the test application and are randomly displayed one by one.
在应用程序中使用数据库时，会出现需要从表对象中随机选择记录的情况。MySQL 没有为此提供内置功能。
While using a database for an application, there arise situations where the records from a table object need to be selected randomly. MySQL does not have a built-in provision for this.
Selecting Random Records in MySQL
为了在 MySQL 中选择随机记录，可以使用 ORDER BY RAND() 子句。RAND() 函数与 SELECT 查询一起用于一个接一个或同时批量检索存储的数据。
In order to select random records in MySQL, you can use the ORDER BY RAND() clause. The RAND() function is used with the SELECT query to retrieve the stored data one by one or collectively together.
The MySQL RAND() Function
MySQL RAND() 函数返回一个结果集，其中包含原始表中的所有记录，并按完全随机顺序排列。它通常与 ORDER BY 子句中的 SELECT 语句一起使用。
The MySQL RAND() Function returns a result-set containing all records of the original table in a completely random order. It is usually used with a SELECT statement in the ORDER BY clause.
Syntax
以下是 RAND() 函数与 ORDER BY 子句一起使用的基本语法：
Following is the basic syntax of the RAND() function with ORDER BY Clause −
SELECT column_name(s) FROM table_name ORDER BY RAND();Example
以下示例演示了与 ORDER BY 子句一起使用 RAND() 函数的情况。此处，我们首先创建一个表“CUSTOMERS”并向其中插入一些值。
Following example demonstrates the usage of RAND() function when used with ORDER BY Clause. Here, let us first create a table 'CUSTOMERS' and insert some values into it.
CREATE TABLE CUSTOMERS(
ID int NOT NULL AUTO_INCREMENT,
NAME varchar(20),
AGE int,
PRIMARY KEY(Id)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('John',23);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Larry',21);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('David',21);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Carol',24);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Bob',27);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Mike',29);
INSERT INTO CUSTOMERS (NAME, AGE) VALUES ('Sam',26);获得的 CUSTOMERS 表如下：−
The CUSTOMERS table obtained is as follows −
现在，让我们使用 RAND() 函数结合 SELECT 语句以随机顺序检索 CUSTOMERS 表的记录：
Now, let us use the RAND() function with the SELECT statement to retrieve the records of the CUSTOMERS table in a randomized order −
SELECT * FROM CUSTOMERS ORDER BY RAND();LIMIT with RAND() Function
您还可以使用 RAND() 函数和 LIMIT 子句来限制随机检索记录的数量
You can also limit the number of randomly retrieved records using the LIMIT clause with the RAND() function
Syntax
以下是与 RAND() 函数一同使用 LIMIT 的语法 -
Following is the syntax to use LIMIT with RAND() function −
SELECT column_name(s) FROM table_name
ORDER BY RAND() LIMIT int_value;Example
在此示例中，使用以下查询从“CUSTOMERS”表中随机检索了数量有限的记录 -
In this example, we are retrieving a limited number of records at random from the 'CUSTOMERS' table using the following query −
SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;以上代码的输出如下所示 −
Output of the above code is as shown below −
每次执行此查询时，您将得到一个不同的随机记录 -
Each time you execute this query, you will get a different random record −
SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;产生的结果如下 −
The result produced is as follows −
您还可以通过修改 LIMIT 值来增加要显示的记录的上限，如下所示 -
You can also increase the limit of records to be displayed by modifying the LIMIT value as shown below −
SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 2;我们得到了如下输出 −
We get the output as shown below −