MySQL(MariaDB)多列排序获取指定记录前后相邻记录方法

适用环境：MariaDB 10.3 / MySQL
核心排序规则：ORDER BY priority DESC, expiration_date ASC, id ASC #

方案1：条件比较法（生产环境首选，性能最优） #

该方案完全对齐排序规则的比较逻辑，可以利用联合索引实现高性能查询，不需要全表扫描。

比较逻辑拆解 #

对于任意一条记录，判断它排在目标记录之前/之后，只需要按排序优先级逐层比较：

• 前序记录（排序后在目标记录上方，离目标最近的1条）满足以下任意一个条件：
  • priority > 目标记录的priority（priority倒序，值越大越靠前）
  • priority与目标相等，且expiration_date < 目标记录的expiration_date（日期升序，值越小越靠前）
  • priority、expiration_date都与目标相等，且id < 目标记录的id（id升序，值越小越靠前）
• 后序记录（排序后在目标记录下方，离目标最近的1条）满足以下任意一个条件：
  • priority < 目标记录的priority
  • priority与目标相等，且expiration_date > 目标记录的expiration_date
  • priority、expiration_date都与目标相等，且id > 目标记录的id

对应SQL实现 #

以查询id=6的记录的相邻记录为例：

1. 单独查询前序记录

SELECT t.* FROM my_table t
JOIN my_table target ON target.id = 6
WHERE 
    t.priority > target.priority
    OR (t.priority = target.priority AND t.expiration_date < target.expiration_date)
    OR (t.priority = target.priority AND t.expiration_date = target.expiration_date AND t.id < target.id)
ORDER BY t.priority ASC, t.expiration_date DESC, t.id DESC
LIMIT 1;

2. 单独查询后序记录

SELECT t.* FROM my_table t
JOIN my_table target ON target.id = 6
WHERE 
    t.priority < target.priority
    OR (t.priority = target.priority AND t.expiration_date > target.expiration_date)
    OR (t.priority = target.priority AND t.expiration_date = target.expiration_date AND t.id > target.id)
ORDER BY t.priority DESC, t.expiration_date ASC, t.id ASC
LIMIT 1;

3. 单条SQL同时返回前序、后序记录

(
    SELECT 'prev' AS position, t.* FROM my_table t
    JOIN my_table target ON target.id = 6
    WHERE 
        t.priority > target.priority
        OR (t.priority = target.priority AND t.expiration_date < target.expiration_date)
        OR (t.priority = target.priority AND t.expiration_date = target.expiration_date AND t.id < target.id)
    ORDER BY t.priority ASC, t.expiration_date DESC, t.id DESC
    LIMIT 1
)
UNION ALL
(
    SELECT 'next' AS position, t.* FROM my_table t
    JOIN my_table target ON target.id = 6
    WHERE 
        t.priority < target.priority
        OR (t.priority = target.priority AND t.expiration_date > target.expiration_date)
        OR (t.priority = target.priority AND t.expiration_date = target.expiration_date AND t.id > target.id)
    ORDER BY t.priority DESC, t.expiration_date ASC, t.id ASC
    LIMIT 1
);

简写方式（数值类倒序字段适用） #

如果倒序字段是数值类型，可以通过取反的方式把多字段排序统一为升序规则，用元组比较简化写法，逻辑和上述长WHERE条件完全等价：

-- 前序简写
SELECT t.* FROM my_table t
JOIN my_table target ON target.id =6
WHERE (-t.priority, t.expiration_date, t.id) < (-target.priority, target.expiration_date, target.id)
ORDER BY (-t.priority, t.expiration_date, t.id) DESC
LIMIT 1;

-- 后序简写
SELECT t.* FROM my_table t
JOIN my_table target ON target.id =6
WHERE (-t.priority, t.expiration_date, t.id) > (-target.priority, target.expiration_date, target.id)
ORDER BY (-t.priority, t.expiration_date, t.id) ASC
LIMIT 1;

优化建议：创建联合索引idx_sort(priority DESC, expiration_date ASC, id ASC)，该方案的查询可以直接走索引完成，百万级数据量下也能毫秒级返回结果。如果排序字段存在NULL值，需要根据业务对NULL的排序要求，用IFNULL转换为固定值后再比较，避免逻辑偏差。

方案2：窗口函数法（适合小表/复杂排序场景） #

MariaDB 10.3已支持窗口函数，可以先按排序规则为所有记录生成全局行号，再通过行号关联找到相邻记录，写法更直观：

WITH sorted_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY priority DESC, expiration_date ASC, id ASC) AS row_num
    FROM my_table
),
target_info AS (
    SELECT row_num FROM sorted_data WHERE id = 6
)
SELECT 
    CASE WHEN s.row_num = t.row_num -1 THEN 'prev' ELSE 'next' END AS position,
    s.*
FROM sorted_data s, target_info t
WHERE s.row_num IN (t.row_num -1, t.row_num +1);

该方案的缺点是需要对全表数据排序生成行号，表数据量较大时性能较差，不适合生产环境大表使用。 #

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