MySQL - 选择随机记录



您是否参加过在线考试?如果是,您是否曾经想过这些问题显示的顺序是如何随机的?这些问题通常存储在测试应用程序的数据库中,并随机显示一个接一个。

在将数据库用于应用程序时,会出现需要随机选择表对象中的记录的情况。MySQL 没有为此提供内置的功能。

在 MySQL 中选择随机记录

为了在 MySQL 中选择随机记录,您可以使用 ORDER BY RAND() 子句。RAND() 函数与 SELECT 查询一起使用,以逐个或整体地检索存储的数据。

MySQL RAND() 函数

MySQL RAND() 函数返回一个结果集,其中包含原始表的所有记录,以完全随机的顺序排列。它通常与 ORDER BY 子句中的 SELECT 语句一起使用。

语法

以下是带 ORDER BY 子句的 RAND() 函数的基本语法:

SELECT column_name(s) FROM table_name ORDER BY RAND();

示例

以下示例演示了在与 ORDER BY 子句一起使用时 RAND() 函数的用法。这里,让我们首先创建一个名为 'CUSTOMERS' 的表,并在其中插入一些值。

CREATE TABLE CUSTOMERS( ID int NOT NULL AUTO_INCREMENT, NAME varchar(20), AGE int, PRIMARY KEY(Id) );

现在,使用 INSERT 语句将值插入此表,如下所示:

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 表如下所示:

ID NAME AGE
1 John 23
2 Larry 21
3 David 21
4 Carol 24
5 Bob 27
6 Mike 29
7 Sam 26

现在,让我们使用 RAND() 函数与 SELECT 语句一起检索 CUSTOMERS 表中的记录,并以随机顺序排列:

SELECT * FROM CUSTOMERS ORDER BY RAND();

输出

以下是上述查询的输出:

ID NAME AGE
6 Mike 29
4 Carol 24
3 David 21
1 John 23
5 Bob 27
7 Sam 26
2 Larry 21

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

使用 RAND() 函数的 LIMIT

您还可以使用 RAND() 函数和 LIMIT 子句限制随机检索的记录数。

语法

以下是使用 LIMIT 和 RAND() 函数的语法:

SELECT column_name(s) FROM table_name ORDER BY RAND() LIMIT int_value;

示例

在此示例中,我们使用以下查询从 'CUSTOMERS' 表中随机检索有限数量的记录:

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;

上述代码的输出如下所示:

ID NAME AGE
7 Sam 26

每次执行此查询时,您都会获得不同的随机记录:

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 1;

产生的结果如下所示:

ID NAME AGE
6 Mike 29

您还可以通过修改 LIMIT 值来增加要显示的记录限制,如下所示:

SELECT * FROM CUSTOMERS ORDER BY RAND() LIMIT 2;

我们得到如下所示的输出:

ID NAME AGE
1 John 23
3 David 21

使用客户端程序随机获取记录

我们还可以使用客户端程序选择随机记录。

语法

要通过 PHP 程序选择随机记录,我们需要使用 mysqli 函数 query() 执行 RAND() 函数,如下所示:

$sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()"; $mysqli->query($sql);

要通过 JavaScript 程序选择随机记录,我们需要使用 mysql2 库的 query() 函数执行 RAND() 函数,如下所示:

sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()"; con.query(sql)

要通过 Java 程序选择随机记录,我们需要使用 JDBC 函数 executeQuery() 执行 RAND() 函数,如下所示:

String sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()"; statement.executeQuery(sql);

要通过 Python 程序选择随机记录,我们需要使用 MySQL Connector/Pythonexecute() 函数执行 RAND() 函数,如下所示:

random_query = "SELECT * FROM CUSTOMERS ORDER BY RAND()" cursorObj.execute(random_query)

示例

以下是程序:

$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $db = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db); if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit(); } //printf('Connected successfully.'); //let's create a table $sql = "create table CUSTOMERS ( Id int NOT NULL AUTO_INCREMENT, Name varchar(20), Age int, PRIMARY KEY(Id) )"; if($mysqli->query($sql)){ printf("CUSTOMERS table created successfully...!\n"); } //now lets insert some records $sql = "insert into CUSTOMERS(Name,Age) values('John',23)"; if($mysqli->query($sql)){ printf("First record inserted successfully....!\n"); } $sql = "insert into CUSTOMERS(Name,Age) values('Larry',21)"; if($mysqli->query($sql)){ printf("Second record inserted successfully....!\n"); } $sql = "insert into CUSTOMERS(Name,Age) values('David',21)"; if($mysqli->query($sql)){ printf("Third record inserted successfully....!\n"); } $sql = "insert into CUSTOMERS(Name,Age) values('Carol',24)"; if($mysqli->query($sql)){ printf("Fourth record inserted successfully....!\n"); } $sql = "insert into CUSTOMERS(Name,Age) values('Bob',27)"; if($mysqli->query($sql)){ printf("Fifth record inserted successfully....!\n"); } //display table record $sql = "SELECT * FROM CUSTOMERS"; if($result = $mysqli->query($sql)){ printf("Table records: \n"); while($row = mysqli_fetch_array($result)){ printf("Id: %d, Name: %s, Age: %d", $row['Id'], $row['Name'], $row['Age']); printf("\n"); } } //lets find random record $sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()"; if($result = $mysqli->query($sql)){ printf("Table records(random record): \n"); while($row = mysqli_fetch_array($result)){ printf("Id: %d, Name: %s, Age: %d", $row['Id'], $row['Name'], $row['Age']); printf("\n"); } } if($mysqli->error){ printf("Error message: ", $mysqli->error); } $mysqli->close();

输出

获得的输出如下所示:

CUSTOMERS table created successfully...!
First record inserted successfully....!
Second record inserted successfully....!
Third record inserted successfully....!
Fourth record inserted successfully....!
Fifth record inserted successfully....!
Table records:
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 3, Name: David, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27
Table records(random record):
Id: 3, Name: David, Age: 21
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27    

var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); // Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); // Create a new database sql = "Create Database TUTORIALS"; con.query(sql); sql = "USE TUTORIALS"; con.query(sql); //Creating CUSTOMERS table sql = "create table CUSTOMERS(Id int NOT NULL AUTO_INCREMENT,Name varchar(20),Age int,PRIMARY KEY(Id));" con.query(sql); sql = "insert into CUSTOMERS(Name,Age) values('John',23),('Larry',21),('David',21),('Carol',24),('Bob',27),('Mike',29),('Sam',26);" con.query(sql); sql = "SELECT * FROM CUSTOMERS;" con.query(sql, function(err, result){ if (err) throw err console.log("**Records in CUSTOMERS Table**"); console.log(result); console.log("--------------------------"); }); sql = "SELECT * FROM CUSTOMERS ORDER BY RAND();" con.query(sql, function(err, result){ if (err) throw err console.log("**Retrieving the randomized order of records:**"); console.log(result); }); });

输出

获得的输出如下所示:

 
Connected!
--------------------------
**Records in CUSTOMERS Table**
[
  { Id: 1, Name: 'John', Age: 23 },
  { Id: 2, Name: 'Larry', Age: 21 },
  { Id: 3, Name: 'David', Age: 21 },
  { Id: 4, Name: 'Carol', Age: 24 },
  { Id: 5, Name: 'Bob', Age: 27 },
  { Id: 6, Name: 'Mike', Age: 29 },
  { Id: 7, Name: 'Sam', Age: 26 }
]
--------------------------
**Retrieving the randomized order of records:**
[
  { Id: 5, Name: 'Bob', Age: 27 },
  { Id: 4, Name: 'Carol', Age: 24 },
  { Id: 6, Name: 'Mike', Age: 29 },
  { Id: 2, Name: 'Larry', Age: 21 },
  { Id: 1, Name: 'John', Age: 23 },
  { Id: 7, Name: 'Sam', Age: 26 },
  { Id: 3, Name: 'David', Age: 21 }
]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class RandomRecords { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "create table CUSTOMERS(Id int NOT NULL AUTO_INCREMENT, Name varchar(20), Age int, PRIMARY KEY(Id))"; st.execute(sql); System.out.println("Table CUSTOMERS created successfully...!"); //let's insert some records into it... String sql1 = "INSERT INTO CUSTOMERS VALUES (NULL, 'John',23), (NULL, 'Larry',21), (NULL, 'David',21), (NULL, 'Carol',24), (NULL, 'Bob',27)"; st.execute(sql1); System.out.println("Records inserted successfully....!"); //print table records String sql2 = "SELECT * FROM CUSTOMERS"; rs = st.executeQuery(sql2); System.out.println("Table records: "); while(rs.next()) { String id = rs.getString("id"); String name = rs.getString("name"); String age = rs.getString("age"); System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age); } //lets print random records String sql4 = "SELECT * FROM CUSTOMERS ORDER BY RAND()"; rs = st.executeQuery(sql4); System.out.println("Table records(random records): "); while(rs.next()) { String id = rs.getString("id"); String name = rs.getString("name"); String age = rs.getString("age"); System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age); } }catch(Exception e) { e.printStackTrace(); } } }

输出

获得的输出如下所示:

Table CUSTOMERS created successfully...!
Records inserted successfully....!
Table records: 
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 3, Name: David, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 5, Name: Bob, Age: 27
Table records(random records): 
Id: 5, Name: Bob, Age: 27
Id: 1, Name: John, Age: 23
Id: 2, Name: Larry, Age: 21
Id: 4, Name: Carol, Age: 24
Id: 3, Name: David, Age: 21    
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) # Creating a cursor object cursorObj = connection.cursor() # Creating the table 'CUSTOMERS' create_table_query = ''' CREATE TABLE CUSTOMERS ( Id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(20), Age INT, PRIMARY KEY(Id) ); ''' cursorObj.execute(create_table_query) print("Table 'CUSTOMERS' is created successfully!") # Inserting records into 'CUSTOMERS' table sql = "INSERT INTO CUSTOMERS (Name, Age) VALUES (%s, %s);" values = [('John', 23), ('Larry', 21), ('David', 21), ('Carol', 24), ('Bob', 27), ('Mike', 29), ('Sam', 26)] cursorObj.executemany(sql, values) print("Values inserted successfully!") # Display table display_table_query = "SELECT * FROM CUSTOMERS;" cursorObj.execute(display_table_query) # Printing the table 'CUSTOMERS' results = cursorObj.fetchall() print("\nCUSTOMERS Table:") for result in results: print(result) # Retrieve the randomized order of records in the 'CUSTOMERS' table random_query = "SELECT * FROM CUSTOMERS ORDER BY RAND()"; cursorObj.execute(random_query) results = cursorObj.fetchall() print("\nRandomized CUSTOMERS Table:") for result in results: print(result) # Closing the cursor and connection cursorObj.close() connection.close()

输出

获得的输出如下所示:

Table 'CUSTOMERS' is created successfully!
Values inserted successfully!

CUSTOMERS Table:
(1, 'John', 23)
(2, 'Larry', 21)
(3, 'David', 21)
(4, 'Carol', 24)
(5, 'Bob', 27)
(6, 'Mike', 29)
(7, 'Sam', 26)

Randomized CUSTOMERS Table:
(7, 'Sam', 26)
(6, 'Mike', 29)
(3, 'David', 21)
(1, 'John', 23)
(2, 'Larry', 21)
(5, 'Bob', 27)
(4, 'Carol', 24)
广告