关于分页查询获取查询结果的总条数

目录

1. 1️⃣ 通用 SQL 模板（支持分页 + 总条数）
2. 2️⃣ 数据库差异
3. 3️⃣ 如果不支持窗口函数
4. 📎 参考文章

1️⃣ 通用 SQL 模板（支持分页 + 总条数）#

SELECT
    ai.id AS id,
    ai.title AS title,
    CAST(ai.update_count AS INTEGER) AS update_count,   -- SQLite: CAST, MySQL/PostgreSQL 可去掉
    CASE WHEN awh.id IS NOT NULL THEN 1 ELSE 0 END AS is_watched,  -- PostgreSQL 可以改 TRUE/FALSE
    COALESCE(awh.user_id, '') AS user_id,
    {convert_update_time} AS update_time,               -- 不同数据库时间函数不同
    {convert_watch_time} AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count                      -- 窗口函数返回总条数
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = :user_id
ORDER BY ai.update_time DESC
LIMIT :page_size OFFSET (:page_index - 1) * :page_size;

---

2️⃣ 数据库差异#

例：SQLite 具体写法

SELECT
    ai.id AS id,
    ai.title AS title,
    CAST(ai.update_count AS INTEGER) AS update_count,
    CASE WHEN awh.id IS NOT NULL THEN 1 ELSE 0 END AS is_watched,
    COALESCE(awh.user_id, '') AS user_id,
    datetime(ai.update_time, 'unixepoch', 'localtime') AS update_time,
    CASE WHEN awh.watched_time IS NOT NULL THEN datetime(awh.watched_time, 'unixepoch', 'localtime') ELSE '' END AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
ORDER BY ai.update_time DESC
LIMIT 20 OFFSET 20;

MySQL 8+ 具体写法

SELECT
    ai.id AS id,
    ai.title AS title,
    ai.update_count AS update_count,
    CASE WHEN awh.id IS NOT NULL THEN 1 ELSE 0 END AS is_watched,
    COALESCE(awh.user_id, '') AS user_id,
    FROM_UNIXTIME(ai.update_time) AS update_time,
    CASE WHEN awh.watched_time IS NOT NULL THEN FROM_UNIXTIME(awh.watched_time) ELSE NULL END AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
ORDER BY ai.update_time DESC
LIMIT 20 OFFSET 20;

PostgreSQL 具体写法

SELECT
    ai.id AS id,
    ai.title AS title,
    ai.update_count AS update_count,
    CASE WHEN awh.id IS NOT NULL THEN TRUE ELSE FALSE END AS is_watched,
    COALESCE(awh.user_id, '') AS user_id,
    to_timestamp(ai.update_time) AS update_time,
    CASE WHEN awh.watched_time IS NOT NULL THEN to_timestamp(awh.watched_time) ELSE NULL END AS watch_time,
    ai.platform AS platform,
    COUNT(*) OVER() AS total_count
FROM ani_info ai
LEFT JOIN ani_watch_history awh
       ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
ORDER BY ai.update_time DESC
LIMIT 20 OFFSET 20;

---

3️⃣ 如果不支持窗口函数#

对 SQLite <3.25 或 MySQL <8：

SELECT p.*, t.total_count
FROM (
    -- 分页数据
    SELECT ...
    FROM ani_info ai
    LEFT JOIN ani_watch_history awh
           ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
    ORDER BY ai.update_time DESC
    LIMIT 20 OFFSET 20
) AS p
CROSS JOIN (
    -- 总条数
    SELECT COUNT(*) AS total_count
    FROM ani_info ai
    LEFT JOIN ani_watch_history awh
           ON ai.id = awh.ani_item_id AND awh.user_id = 'USER123'
) AS t;

• 这种方式兼容旧版本数据库。
• 每行都会带上 total_count。

---

✅ 总结要点

1. 优先使用 窗口函数 ****COUNT(*) OVER()，一条 SQL 返回分页 + 总条数，高效且简单。
2. 不支持窗口函数的数据库，用 分页子查询 + CROSS JOIN 总条数 实现。
3. 时间戳格式化函数和布尔类型，根据数据库不同选择对应函数。
4. 对大表分页，如果数据量很大，可考虑 Keyset Pagination（基于最后一条 update_time）避免 OFFSET 扫描大量数据。

---

📎 参考文章#

• 具体的代码和sql脚本见代码仓库

  未支持的 Notion 内容：bookmark 在 Notion 中打开