MySQL - 选择随机记录

更新于 2025/6/18 12:07:17

在 MySQL 中选择随机记录
MySQL RAND() 函数
使用 RAND() 函数限制
使用客户端程序获取随机记录

你参加过在线考试吗?如果参加过，那么你有没有想过，这些题目的显示顺序是如何随机的?这些题目通常存储在测试应用程序的数据库中，并随机地逐一显示。

在应用程序使用数据库时，有时会需要随机选择表对象中的记录。 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 NodeJS Java Python

要通过 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)

示例

以下是程序 -

PHP NodeJS Java Python

$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)

❮ MySQL 删除重复记录 MySQL 显示进程列表 ❯

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - 选择随机记录

在 MySQL 中选择随机记录

MySQL RAND() 函数

语法

示例

输出

使用 RAND() 函数的 LIMIT 限制

语法

示例

使用客户端程序随机记录

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！