mysql 一条分页查询时查出总长度

原创

mob649e815b8ae8 2024-08-30 07:33:14 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815b8ae8的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL一条分页查询时查出总长度

在数据处理和展示中，分页是一个常见的需求。它不仅能够提高程序的性能，还能使用户体验更加流畅。通过分页，我们可以将大数据集分解为较小的部分，用户可以逐步浏览。而在进行分页查询时，尤其需要获取数据集的总长度，以便计算总页数。本文将介绍如何在MySQL中实现一条SQL语句进行分页并同时获取总长度，并附上代码示例。

分页查询的基本概念

分页查询通常至少包含两个参数——当前页码和每页展示的数据条数。假设我们要查询的表格名为users，其结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

总记录数与当前页

为了实现分页，首先要获取数据的总记录数，然后根据当前页码和每页条数计算出查询的起始位置。

SQL实现

在MySQL中，我们可以使用SQL_CALC_FOUND_ROWS和FOUND_ROWS()来实现一条查询语句获取数据及其总长度。示例代码如下：

SELECT SQL_CALC_FOUND_ROWS * 
FROM users 
LIMIT 10 OFFSET 0;

SELECT FOUND_ROWS();

在这个例子中，第一条语句查询users表中的数据，但限制结果为10条，并通过LIMIT和OFFSET来控制数据的起始位置。第二条语句FOUND_ROWS()用于获取总记录数。

代码示例

下面是一个完整的代码示例，结合Java和JDBC策略来实现分页查询及总计数获取：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class UserPagination {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_username";
    private static final String PASS = "your_password";

    public void getUserPage(int pageNumber, int pageSize) {
        String query = "SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT ? OFFSET ?";
        String countQuery = "SELECT FOUND_ROWS()";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
             
            pstmt.setInt(1, pageSize);
            pstmt.setInt(2, (pageNumber - 1) * pageSize);
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println("User: " + rs.getString("username"));
            }

            // Count total rows
            try (PreparedStatement countStmt = conn.prepareStatement(countQuery);
                 ResultSet countRs = countStmt.executeQuery()) {
                if (countRs.next()) {
                    int totalRows = countRs.getInt(1);
                    System.out.println("Total Count: " + totalRows);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

类图与序列图

以下是该类及其交互的类图和序列图。

类图

classDiagram
    class UserPagination {
        +getUserPage(int pageNumber, int pageSize)
    }
    
    class DatabaseConnection {
        +connect()
        +disconnect()
    }
    
    UserPagination ..> DatabaseConnection : uses

序列图

sequenceDiagram
    participant UserPagination
    participant DatabaseConnection
    participant MySQL

    UserPagination->>DatabaseConnection: connect()
    UserPagination->>MySQL: execute(query)
    MySQL-->>UserPagination: return users
    UserPagination->>MySQL: execute(countQuery)
    MySQL-->>UserPagination: return totalCount
    UserPagination->>DatabaseConnection: disconnect()