MySQL 5.7如何查询Sort字段首次增量中断前的连续有序记录

环境与需求说明 #

• 运行环境：MySQL 5.7
• 涉及表：FizzBuzz，字段为ID、Name、Sort，示例数据如下：

• 目标：传入Sort的起始筛选条件后，返回从符合条件的第一条记录开始，到第一次出现相邻Sort差值不为1的断点前，所有连续有序的记录。
  • 当筛选条件为sort >= 1时，返回Foo、Bar
  • 当筛选条件为sort > 2时，返回Baz、Quux、Xyzzy

核心逻辑 #

MySQL 5.7不支持8.0版本的窗口函数，通过自定义用户变量逐行遍历即可实现连续段判定：

1. 初始化用户变量存储上一行的Sort值，按Sort升序遍历所有符合起始筛选条件的记录
2. 逐行比对当前行Sort和上一行值的差值，标记出第一个差值不为1的断点位置
3. 外层查询返回所有符合起始条件、且Sort小于第一个断点值的记录即可

可用SQL代码 #

以sort >= 1的筛选条件为例，代码如下：

SELECT Name
FROM FizzBuzz
WHERE sort >= 1 
  AND sort < IFNULL(
    (
      SELECT MIN(t.current_sort)
      FROM (
        SELECT 
          Sort AS current_sort,
          @prev AS last_sort,
          IF(@prev IS NOT NULL AND Sort - @prev != 1, 1, 0) AS is_break,
          @prev := Sort
        FROM FizzBuzz, (SELECT @prev := NULL) AS init_var
        WHERE sort >= 1 -- 此处替换为实际的起始筛选条件
        ORDER BY Sort ASC
      ) AS t
      WHERE t.is_break = 1
    ),
  2147483647 -- 用Int类型最大值做容错，无断点时返回所有符合起始条件的记录
)
ORDER BY Sort ASC;

如果要切换为sort > 2的筛选条件，只需要把SQL中两处WHERE sort >= 1替换为WHERE sort > 2即可，其他逻辑无需修改。

注意事项 #

• 必须保证子查询内的ORDER BY Sort ASC排序逻辑生效，否则逐行比对的结果会出错
• Sort字段建议加索引，避免全表遍历影响查询性能
• 末尾的容错值2147483647是MySQL INT类型的最大值，如果Sort字段用的是BIGINT，可以替换为更大的数值适配业务场景

内容的提问来源于stack exchange，提问作者j. Doe