MySQL - 选择随机记录
你参加过在线考试吗?如果参加过，那么你有没有想过，这些题目的显示顺序是如何随机的?这些题目通常存储在测试应用程序的数据库中，并随机地逐一显示。
在应用程序使用数据库时，有时会需要随机选择表对象中的记录。 MySQL 没有内置此功能。
在 MySQL 中选择随机记录
要在 MySQL 中选择随机记录，可以使用 ORDER BY RAND() 子句。RAND() 函数与 SELECT 查询一起使用，可以逐个或批量检索存储的数据。
MySQL RAND() 函数
MySQL RAND() 函数返回一个结果集，该结果集包含原始表中的所有记录，且顺序完全随机。它通常与 SELECT 语句中的 ORDER BY 子句一起使用。
语法
以下是 RAND() 函数与 ORDER BY 子句的基本语法 -
SELECT column_name(s) FROM table_name ORDER BY RAND();
示例
以下示例演示了 RAND() 函数与 ORDER BY 子句一起使用时的用法。首先，我们创建一个表"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 |
使用 RAND() 函数的 LIMIT 限制
您还可以使用 RAND() 函数的 LIMIT 子句来限制随机检索的记录数
语法
以下是使用 RAND() 函数的 LIMIT 限制的语法 -
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/Python 的 execute() 函数执行 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...!
");
}
//现在让我们插入一些记录
$sql = "insert into CUSTOMERS(Name,Age) values('John',23)";
if($mysqli->query($sql)){
printf("First record inserted successfully....!
");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Larry',21)";
if($mysqli->query($sql)){
printf("Second record inserted successfully....!
");
}
$sql = "insert into CUSTOMERS(Name,Age) values('David',21)";
if($mysqli->query($sql)){
printf("Third record inserted successfully....!
");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Carol',24)";
if($mysqli->query($sql)){
printf("Fourth record inserted successfully....!
");
}
$sql = "insert into CUSTOMERS(Name,Age) values('Bob',27)";
if($mysqli->query($sql)){
printf("Fifth record inserted successfully....!
");
}
//显示表记录
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
printf("Table records:
");
while($row = mysqli_fetch_array($result)){
printf("Id: %d, Name: %s, Age: %d",
$row['Id'],
$row['Name'],
$row['Age']);
printf("
");
}
}
//让我们找到随机记录
$sql = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
if($result = $mysqli->query($sql)){
printf("Table records(random record):
");
while($row = mysqli_fetch_array($result)){
printf("Id: %d, Name: %s, Age: %d",
$row['Id'],
$row['Name'],
$row['Age']);
printf("
");
}
}
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"
});
// 连接到 MySQL
con.connect(function (err) {
if (err) throw err;
console.log("Connected!");
console.log("--------------------------");
// 创建新数据库
sql = "Create Database TUTORIALS";
con.query(sql);
sql = "USE TUTORIALS";
con.query(sql);
//创建 CUSTOMERS 表
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...!");
//让我们在其中插入一些记录...
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....!");
//打印表记录
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);
}
//让我们打印随机记录
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
# 建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 创建表"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!")
# 将记录插入"CUSTOMERS"表
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_query = "SELECT * FROM CUSTOMERS;"
cursorObj.execute(display_table_query)
# 打印表"CUSTOMERS"
results = cursorObj.fetchall()
print("
CUSTOMERS Table:")
for result in results:
print(result)
# 检索"CUSTOMERS"表中记录的随机顺序
random_query = "SELECT * FROM CUSTOMERS ORDER BY RAND()";
cursorObj.execute(random_query)
results = cursorObj.fetchall()
print("
Randomized CUSTOMERS Table:")
for result in results:
print(result)
# 关闭游标和连接
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)
