MySQL - LIMIT 子句

MySQL - LIMIT 子句

MySQL LIMIT 子句

LIMIT 子句是 MySQL 中一个强大的工具，用于限制 SELECT 查询返回的行数。在处理大型数据集时，它至关重要，因为一次性获取数千或数百万条记录可能会很慢并消耗大量内存。LIMIT 允许你检索数据的特定“切片”或“页面”。

该子句接受一个或两个非负整数参数：count（计数）和可选的 offset（偏移量）。

语法

LIMIT 子句的基本语法有两种常见形式：

SELECT column1, column2, ...
FROM table_name
LIMIT count;

-- 或带偏移量：
SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

-- 使用 OFFSET 关键字的替代（更清晰）语法 (MySQL 8.0+)：
SELECT column1, column2, ...
FROM table_name
LIMIT count OFFSET offset;

• count：指定要返回的最大行数。
• offset：指定在开始返回行之前要跳过的行数。第一行的偏移量是 0，而不是 1。如果省略 offset，则默认为 0。

初始设置

让我们设置一个 CUSTOMERS 表以用于示例。此表将存储客户信息。

-- 创建表
CREATE TABLE CUSTOMERS(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(18, 2),
    PRIMARY KEY(ID)
);

-- 插入示例数据
INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

如果我们查询所有记录，表格看起来像这样：

SELECT * FROM CUSTOMERS;

ID	姓名	年龄	地址	薪水
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

示例：限制行数

要从表中获取前 4 条客户记录，我们使用 LIMIT 4。

SELECT * FROM CUSTOMERS LIMIT 4;

输出

ID	姓名	年龄	地址	薪水
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00

示例：带偏移量的限制

为了实现分页，我们需要跳过一些行。要从第 3 行（偏移量为 2）开始获取 4 位客户，我们使用 LIMIT 2, 4。

SELECT * FROM CUSTOMERS LIMIT 2, 4;

输出

该查询跳过前两条记录（ID 1 和 2），并返回接下来的四条记录。

ID	姓名	年龄	地址	薪水
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

实际用例：分页

分页是 LIMIT 最常见的用途。想象你有一个网页显示客户列表，每页 10 条。下面是你如何计算每页的 LIMIT 和 OFFSET：

• 第 1 页：LIMIT 10 OFFSET 0 (或 LIMIT 0, 10)
• 第 2 页：LIMIT 10 OFFSET 10 (或 LIMIT 10, 10)
• 第 3 页：LIMIT 10 OFFSET 20 (或 LIMIT 20, 10)
• 通用公式：offset = (页码 - 1) * 每页项数，count = 每页项数

将 LIMIT 与 WHERE 子句结合使用

你可以将 LIMIT 与 WHERE 子句结合使用，以限制从过滤后的结果集中返回的行数。WHERE 子句首先被应用，然后 LIMIT 应用于过滤后的结果。

示例

让我们找出前两位年龄大于 25 岁的客户。

SELECT * FROM CUSTOMERS WHERE AGE > 25 LIMIT 2;

输出

ID	姓名	年龄	地址	薪水
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

将 LIMIT 与 ORDER BY 子句结合使用

在没有 ORDER BY 的情况下使用 LIMIT 可能会产生不可预测的结果，因为除非明确指定，否则数据库不保证特定的行顺序。为了获得一致的结果，特别是对于“收入最高的 5 位”或“10 篇最新帖子”等功能，你必须使用 ORDER BY。

示例：查找收入最高的前 3 名

要找出薪水最高的前 3 位客户，我们按 SALARY 降序排序，然后取前 3 条记录。

SELECT * FROM CUSTOMERS
ORDER BY SALARY DESC
LIMIT 3;

输出

ID	姓名	年龄	地址	薪水
7	Muffy	24	Indore	10000.00
5	Hardik	27	Bhopal	8500.00
4	Chaitali	25	Mumbai	6500.00

性能考量

尽管 LIMIT 对性能至关重要，但它在处理大偏移量时存在一个主要缺陷。像 LIMIT 1000000, 10 这样的查询效率非常低。数据库仍然必须生成并扫描所有 1,000,010 行，然后才能丢弃前一百万行并返回最终的 10 行。这被称为大偏移量分页。

专业提示： 对于具有“无限滚动”或深度分页的应用程序，请考虑使用“键集分页”（或“查找方法”）。你不再使用偏移量，而是基于你看到的最后一个值使用 WHERE 子句。例如：WHERE id > [last_seen_id] ORDER BY id ASC LIMIT 10。这种方法快得多，因为它可以使用索引直接跳到起始点。

在应用程序代码中使用 LIMIT

在实际应用中，你将从后端代码执行这些查询。至关重要的是使用参数化查询（预处理语句）来防止 SQL 注入漏洞。切勿将用户输入直接格式化到 SQL 字符串中。

设置说明

在运行以下示例之前，请确保你已为你的语言安装了相应的数据库驱动程序：

• PHP： mysqli 扩展通常默认启用。
• Node.js： npm install mysql2
• Java： 通过 Maven 或 Gradle 添加 MySQL Connector/J 依赖项。
• Python： pip install mysql-connector-python

PHP
NodeJS
Java
Python



// 假设 $dbhost, $dbuser, $dbpass, $dbname 已定义
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    // 在生产环境中，记录此错误，不要向用户显示
    die("Connection failed: " . $mysqli->connect_error);
}

$limit = 4;
$offset = 2;

// 使用预处理语句以防止 SQL 注入
$stmt = $mysqli->prepare("SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT ?, ?");

// 'ii' 表示两个整数参数
$stmt->bind_param('ii', $offset, $limit);

$stmt->execute();
$result = $stmt->get_result();

$customers = $result->fetch_all(MYSQLI_ASSOC);

print_r($customers);

$stmt->close();
$mysqli->close();



// 使用 mysql2/promise 的现代 async/await 语法
const mysql = require('mysql2/promise');

async function getCustomers() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const limit = 4;
    const offset = 2;

    const sql = "SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT ?, ?";
    const [rows] = await connection.execute(sql, [offset, limit]);

    console.log('Fetched customers:');
    console.log(rows);

  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

getCustomers();



import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LimitQueryExample {
    // JDBC 驱动程序名称和数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";

    // 数据库凭据
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        String sql = "SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT ?, ?";
        int limit = 4;
        int offset = 2;

        // 使用 try-with-resources 进行自动资源管理
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, offset);
            pstmt.setInt(2, limit);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("ID: %d, Name: %s, Age: %d, Salary: %.2f\n",
                            rs.getInt("ID"),
                            rs.getString("NAME"),
                            rs.getInt("AGE"),
                            rs.getBigDecimal("SALARY"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



import mysql.connector
from mysql.connector import errorcode

def get_paginated_customers():
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS'
    }

    limit = 4
    offset = 2
    query = "SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT %s, %s"

    try:
        # 使用 'with' 语句进行适当的资源管理
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, (offset, limit))
                customers = cursor.fetchall()

                print("Fetched customers:")
                for customer in customers:
                    print(customer)

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)

if __name__ == "__main__":
    get_paginated_customers()