MySQL - 选择随机记录

Table of content
上一页
下一页

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

在将数据库用于应用程序时，会出现需要随机选择表对象中的记录的情况。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

使用 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 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...!\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://127.0.0.1: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)
打印页面
上一页
下一页
广告