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://: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)
列印頁面
上一頁
下一頁
廣告