如何在MySQL中快速查找大量数据行中缺失的FAC开头序列号

针对10万+记录的场景，核心思路是剥离序列号的固定前缀，通过数字序列对比快速定位缺失项，以下是分数据库的实现方案及优化建议：

通用逻辑 #

1. 提取所有序列号的数字部分（FAC-xxxxxx中的xxxxxx）并转为整数
2. 确定数字部分的最小/最大值，生成该范围内的连续整数序列
3. 将生成的序列与原表数字部分左连接，未匹配到的即为缺失项，最后补回前缀和补零格式

MySQL 实现（8.0+ 推荐用递归CTE） #

1. 先获取数字范围（可选，可直接整合到CTE）

SELECT
  MIN(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) AS min_num,
  MAX(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) AS max_num
FROM your_table;

2. 递归生成序列并查找缺失值

WITH RECURSIVE num_seq AS (
  SELECT MIN(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) AS num
  FROM your_table
  UNION ALL
  SELECT num + 1 FROM num_seq
  WHERE num < (SELECT MAX(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) FROM your_table)
)
SELECT CONCAT('FAC-', LPAD(num, 5, '0')) AS missing_serial
FROM num_seq
LEFT JOIN your_table 
  ON CAST(SUBSTRING(your_table.serial_number, 5) AS UNSIGNED) = num_seq.num
WHERE your_table.serial_number IS NULL;

MySQL 5.x 兼容方案：提前创建数字辅助表（存储1到足够大的整数），避免递归

-- 先创建并填充辅助表（示例填充1-150000）
CREATE TABLE num_helper (num INT UNSIGNED NOT NULL PRIMARY KEY);
DELIMITER //
CREATE PROCEDURE fill_num_helper()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 150000 DO
    INSERT INTO num_helper VALUES(i);
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;
CALL fill_num_helper();

-- 查询缺失值
SELECT CONCAT('FAC-', LPAD(n.num, 5, '0')) AS missing_serial
FROM num_helper n
LEFT JOIN your_table t 
  ON CAST(SUBSTRING(t.serial_number, 5) AS UNSIGNED) = n.num
WHERE n.num BETWEEN (SELECT MIN(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) FROM your_table)
  AND (SELECT MAX(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) FROM your_table)
  AND t.serial_number IS NULL;

PostgreSQL 实现（用generate_series快速生成序列） #

WITH serial_nums AS (
  SELECT CAST(SUBSTRING(serial_number FROM 5) AS INTEGER) AS num
  FROM your_table
),
num_range AS (
  SELECT MIN(num) AS min_num, MAX(num) AS max_num FROM serial_nums
)
SELECT CONCAT('FAC-', LPAD(num::TEXT, 5, '0')) AS missing_serial
FROM generate_series((SELECT min_num FROM num_range), (SELECT max_num FROM num_range)) AS num
LEFT JOIN serial_nums ON serial_nums.num = num
WHERE serial_nums.num IS NULL;

SQL Server 实现（递归CTE+补零） #

WITH num_seq AS (
  SELECT MIN(CAST(SUBSTRING(serial_number, 5, LEN(serial_number)-4) AS INT)) AS num
  FROM your_table
  UNION ALL
  SELECT num + 1 FROM num_seq
  WHERE num < (SELECT MAX(CAST(SUBSTRING(serial_number, 5, LEN(serial_number)-4) AS INT)) FROM your_table)
)
SELECT 'FAC-' + RIGHT('00000' + CAST(num AS VARCHAR(5)), 5) AS missing_serial
FROM num_seq
LEFT JOIN your_table 
  ON CAST(SUBSTRING(your_table.serial_number, 5, LEN(your_table.serial_number)-4) AS INT) = num_seq.num
WHERE your_table.serial_number IS NULL
OPTION (MAXRECURSION 0); -- 数字范围超过100时必须开启

性能优化建议 #

• 新增索引：如果频繁查询，建议给serial_number列建前缀索引（如INDEX idx_serial_prefix (serial_number(6))），或单独新增serial_num整数列存储提取后的数字，给该列建普通索引，能大幅提升连接查询速度
• 相邻对比法：如果序列号基本是连续生成的，可通过对比相邻记录找缺失，避免生成大序列，效率更高：

SELECT CONCAT('FAC-', LPAD(t1.num + 1, 5, '0')) AS missing_serial
FROM (
  SELECT CAST(SUBSTRING(serial_number, 5) AS UNSIGNED) AS num FROM your_table
) t1
LEFT JOIN (
  SELECT CAST(SUBSTRING(serial_number, 5) AS UNSIGNED) AS num FROM your_table
) t2 ON t2.num = t1.num + 1
WHERE t2.num IS NULL
AND t1.num < (SELECT MAX(CAST(SUBSTRING(serial_number, 5) AS UNSIGNED)) FROM your_table);

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