MySQL排序查询：获取匹配记录及前后相邻记录的最优方案

核心思路 #

通过排序字段的直接比较，分别查询目标记录的前一条（排序字段值小于目标值的最大记录）和后一条（排序字段值大于目标值的最小记录），配合索引实现高效查询，彻底避免全表遍历或不规范的模糊匹配。

基础场景（排序字段无重复值） #

假设表名为your_table，排序字段为sort_field（varchar类型，按字母排序），需返回的字段为needed_field，且已获取目标记录的sort_field值为$target_sort_value（PHP变量）：

1. 获取前一条记录

SELECT needed_field
FROM your_table
WHERE sort_field < ?
ORDER BY sort_field DESC
LIMIT 1;

（PHP中用预处理语句替换?为$target_sort_value，避免SQL注入）

2. 获取后一条记录

SELECT needed_field
FROM your_table
WHERE sort_field > ?
ORDER BY sort_field ASC
LIMIT 1;

进阶场景（排序字段存在重复值） #

如果sort_field可能有重复值，需结合唯一主键（如id）确保定位准确，假设目标记录的主键为$target_id：

1. 获取前一条记录

SELECT needed_field
FROM your_table
WHERE (sort_field < ?) OR (sort_field = ? AND id < ?)
ORDER BY sort_field DESC, id DESC
LIMIT 1;

2. 获取后一条记录

SELECT needed_field
FROM your_table
WHERE (sort_field > ?) OR (sort_field = ? AND id > ?)
ORDER BY sort_field ASC, id ASC
LIMIT 1;

关键优化 #

给sort_field单独添加索引；若需处理重复值，建立联合索引(sort_field, id)。MySQL会直接通过索引定位符合条件的记录，查询效率接近常数级，完全避免全表扫描。

PHP处理逻辑 #

1. 执行原精准查询，获取目标记录的sort_field和id（需处理重复值时）；
2. 分别执行上述两个查询，获取前/后记录的needed_field；
3. 自行处理前/后记录为空的首尾场景。

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