MySQL实现首页最多3个精选商品分页查询方案

需求明确 #

• 每页固定展示10个商品
• 首页规则：
  • 若存在is_featured=1的精选商品，随机展示最多3个，剩余位置补充is_featured=0的普通商品（按item_id排序）
  • 若无精选商品，直接展示10个普通商品
• 分页需支持LIMIT/OFFSET，保证无数据遗漏、无重复记录
• 非首页按item_id顺序分页，例如第二页从item_id=108开始

原查询的问题 #

1. 重复数据：第二个子查询未排除已选中的精选商品，导致同一件精选商品可能同时出现在两个子查询结果中
2. 数量不足：无精选商品时，第二个子查询固定LIMIT 7，仅返回7条数据，不符合每页10条的要求

优化后的查询语句 #

1. 首页查询（OFFSET=0） #

使用CTE先筛选出随机精选商品，再补充足够的普通商品，自动适配有无精选的场景：

WITH selected_featured AS (
    -- 随机选最多3个精选商品
    SELECT *
    FROM item
    WHERE is_featured = 1
    ORDER BY RAND()
    LIMIT 3
)
-- 先返回精选商品，再补充普通商品
SELECT * FROM selected_featured
UNION ALL
SELECT *
FROM item
WHERE is_featured = 0
-- 排除已选中的精选（避免重复，严谨性处理）
AND item_id NOT IN (SELECT item_id FROM selected_featured)
ORDER BY item_id
-- 动态计算需要补充的数量：10减去已选精选的数量
LIMIT 10 - (SELECT COUNT(*) FROM selected_featured);

2. 非首页分页查询 #

先排除首页已展示的所有商品，再按item_id排序进行常规分页：

-- 先获取首页展示的所有商品ID
WITH home_page_items AS (
    WITH selected_featured AS (
        SELECT *
        FROM item
        WHERE is_featured = 1
        ORDER BY RAND()
        LIMIT 3
    )
    SELECT item_id FROM selected_featured
    UNION ALL
    SELECT item_id
    FROM item
    WHERE is_featured = 0
    AND item_id NOT IN (SELECT item_id FROM selected_featured)
    ORDER BY item_id
    LIMIT 10 - (SELECT COUNT(*) FROM selected_featured)
)
-- 非首页查询：排除首页商品，按item_id排序分页
SELECT *
FROM item
WHERE item_id NOT IN (SELECT item_id FROM home_page_items)
ORDER BY item_id
-- 这里的OFFSET根据实际页码调整，例如第二页OFFSET 0，第三页OFFSET 10，以此类推
LIMIT 10 OFFSET 0;

说明 #

• 用CTE隔离精选商品的筛选逻辑，确保不会重复获取同一商品
• 动态计算补充商品的数量，无精选商品时自动返回10个普通商品
• 非首页查询通过排除首页已展示的商品ID，保证分页无遗漏、无重复

内容的提问来源于stack exchange，提问作者Jiri