Mysql小知识 - 查找连续编号中的缺失编号

2020-04-13

全文 ≈ 557字 | 预计阅读时长 2分钟

在和小伙伴讨论问题的时候，小伙伴突然问了我这样一个小问题，数据库中如何查找连续编号中的缺失编号？

1.描述

场景大概是这样，有一份连续数据ID：1 … 27，其中ID：6，7，14的数据丢了。结构如下：

CREATE TABLE `letter`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

INSERT INTO `letter` VALUES (1, 'A');
INSERT INTO `letter` VALUES (2, 'B');
INSERT INTO `letter` VALUES (3, 'C');
INSERT INTO `letter` VALUES (4, 'D');
INSERT INTO `letter` VALUES (5, 'E');
# INSERT INTO `letter` VALUES (6, 'F');
# INSERT INTO `letter` VALUES (7, 'J');
INSERT INTO `letter` VALUES (8, 'H');
INSERT INTO `letter` VALUES (9, 'I');
INSERT INTO `letter` VALUES (10, 'J');
INSERT INTO `letter` VALUES (11, 'K');
INSERT INTO `letter` VALUES (12, 'L');
INSERT INTO `letter` VALUES (13, 'M');
# INSERT INTO `letter` VALUES (14, 'N');
INSERT INTO `letter` VALUES (15, 'O');
INSERT INTO `letter` VALUES (16, 'P');
INSERT INTO `letter` VALUES (17, 'Q');
INSERT INTO `letter` VALUES (18, 'R');
INSERT INTO `letter` VALUES (19, 'S');
INSERT INTO `letter` VALUES (20, 'T');
INSERT INTO `letter` VALUES (21, 'U');
INSERT INTO `letter` VALUES (22, 'V');
INSERT INTO `letter` VALUES (23, 'W');
INSERT INTO `letter` VALUES (24, 'X');
INSERT INTO `letter` VALUES (25, 'Y');
INSERT INTO `letter` VALUES (26, 'Z');

怎么把6，7，14这三条数据找出来？方法有很多种哈，今天我们说一下如何利用SQL快速查询出来。大概思路是把ID+1，然后查询ID+1这个值是否存在ID列表中，如果不存在那肯定就是缺失的。SQL如下：

SELECT
	id + 1 AS id 
FROM
	`letter` 
WHERE
	id + 1 NOT IN ( SELECT id FROM `letter` )

但是这样会有一个小问题，就是MAX(id)+1（27）也会被查询出来，所以：

SELECT
	id + 1 AS id 
FROM
	`letter` 
WHERE
	id + 1 NOT IN ( SELECT id FROM `letter` ) 
	AND id <(
	SELECT
		max( id ) 
	FROM
	`letter` 
	)

2.优化

SELECT
	start_id,
	( SELECT MIN(id)- 1 FROM `letter` WHERE id > start_id ) AS end_id 
FROM
	(
	SELECT
		id + 1 AS start_id 
	FROM
		`letter` 
	WHERE
		id + 1 NOT IN ( SELECT id FROM `letter` ) 
		AND id <(
		SELECT
			max(id) 
		FROM
			`letter` 
		) 
	) AS max_id 
ORDER BY
	start_id

这种情况适用于查找整数类型的连续编号，那么如果编号是string类型的呢？后面有机会再补充进来=,=。