MySQL 找出斷點缺失值實戰：NOT EXISTS 定位缺口邊界再生成缺失序列

MySQL 找出斷點缺失值實戰

業務流水號、設備運行號等本應連續的序列若出現斷點 (如 00001 後直接跳到 00005)，需精準找出缺失的中間值 (00002, 00003, 00004)。本文提供兩階段方案：先定位缺口邊界，再生成缺失清單。

場景與資料

表 device_data，欄位 running_number (VARCHAR/CHAR 存儲 5 位數字字串)，device_id=13。現有資料：00001, 00005, 00006...，缺失 00002-00004。

階段一：定位缺口前邊界 (缺口起始前一個存在的值)

SELECT running_number, `time`
FROM (
  SELECT running_number, `time` 
  FROM device_data 
  WHERE device_id = 13 
  ORDER BY CAST(running_number AS UNSIGNED) ASC
) t
WHERE NOT EXISTS (
  SELECT 1 
  FROM device_data b 
  WHERE b.device_id = 13 
    AND CAST(b.running_number AS UNSIGNED) = CAST(t.running_number AS UNSIGNED) - 1
);

結果： 返回 00001 (因不存在 00000) 與 00005 (因不存在 00004) — 即每個缺口的「前一個存在值」。

階段二：生成具體缺失值 (需輔助數字表或遞歸 CTE)

MySQL 8.0+ 可用遞歸 CTE 直接展開：

WITH boundaries AS (
  -- 階段一查詢作為 CTE
  SELECT CAST(running_number AS UNSIGNED) AS val
  FROM device_data t
  WHERE device_id = 13
    AND NOT EXISTS (
      SELECT 1 FROM device_data b 
      WHERE b.device_id = 13 
        AND CAST(b.running_number AS UNSIGNED) = CAST(t.running_number AS UNSIGNED) - 1
    )
),
missing AS (
  SELECT 
    b.val + 1 AS start_missing,
    LEAD(b.val) OVER (ORDER BY b.val) - 1 AS end_missing
  FROM boundaries b
  WHERE LEAD(b.val) OVER (ORDER BY b.val) - b.val > 1
)
SELECT seq AS missing_number
FROM missing m
JOIN (
  SELECT @row := @row + 1 AS seq FROM device_data, (SELECT @row := 0) r LIMIT 10000
) nums ON nums.seq BETWEEN m.start_missing AND m.end_missing
ORDER BY missing_number;

MySQL 5.7 替代方案：輔助數字表

-- 1. 建立長期複用的數字表 (0~100000)
CREATE TABLE nums (n INT PRIMARY KEY);
INSERT INTO nums SELECT @i := @i + 1 FROM information_schema.columns, (SELECT @i := 0) t LIMIT 100000;

-- 2. 查詢缺失值
SELECT n.n AS missing_number
FROM nums n
WHERE n.n BETWEEN 1 AND (SELECT MAX(CAST(running_number AS UNSIGNED)) FROM device_data WHERE device_id=13)
  AND NOT EXISTS (
    SELECT 1 FROM device_data d 
    WHERE d.device_id=13 AND CAST(d.running_number AS UNSIGNED) = n.n
  )
ORDER BY n.n;

核心思路總結

1. 邊界定位： 利用 NOT EXISTS 找出「前一個數字不存在」的記錄，即缺口右邊界 (或左邊界)。
2. 範圍展開： 結合 LEAD() 窗口函數或輔助數字表，將邊界轉為具體缺失清單。
3. 型別處理： 字串存儲的數字**務必 CAST(... AS UNSIGNED)** 參與運算，否則字串比較會導致錯誤 (如 '5' > '10000')。

延伸應用： 同理可用於發現缺失日期、缺失自增 ID、稽核流水號完整性。