Oracle通过SQL找出ID不连续的位置

1 前言

  工作中要求我找到 ID 不连续的地方, 然后拿这个 ID , 给数据库里面 INSERT 一条数据. 比如:

ID	备注
… 省略连续的部分
23
24
25	就是想找到这里断开的地方, 下一个可以 INSERT 的 ID 就是 26
74915
75115
75120
… 省略连续的部分

2 SQL

2.1 查找ID断开一个数的位置

  假设数据库表名叫 MY_TEST_TABLE, 先执行一段 SQL, 看看它 ID 不连续的位置:

SELECT t1.id FROM MY_TEST_TABLE t1 ORDER BY t1.id ASC
;
-- 结果
ID   |
-----+
... 省略连续的部分
   23|
   24|
   25|
74915|
75115|
75120|
... 省略连续的部分

  很明显, 由上面的执行结果可知, ID 在 25 和 74915 的位置 不连续了, 那么下一个可 INSERT 的 ID 就是 26. 接下来, 我们就执行 SQL 来找到这个 26 :

-- 查找 项目成员 的 不连续的 ID 的 SQL
SELECT
/* 下一个ID */
t3.before_id + 1 next_id
FROM (
	SELECT 
	/* before_id 表示当前 ID 的上一个 真实的ID. 比如 25 */
	FIRST_VALUE(t2.ID) OVER (ORDER BY t2.id rows between 1 preceding and 1 following) before_id,
	/* before_id 表示当前 真实的ID. 比如 74915 */
	t2.id current_id,
	/* after_id 表示当前 ID 的下一个 真实的ID. 比如 75115 */
	LAST_VALUE(t2.id) OVER (ORDER BY t2.id rows between 1 preceding and 1 following) after_id 
	FROM (SELECT t1.id FROM MY_TEST_TABLE t1 ORDER BY t1.id asc) t2
) t3
WHERE 
/* 上一个ID(25) 加1 不等于当前的ID (74915) */
t3.before_id + 1 <> t3.current_id AND t3.current_id - 1 > 1
/* 只取第一个 */
AND rownum = 1
;
-- 结果
NEXT_ID|
-------+
     26|

2.1 查找ID断开指定长度的位置

  工作中, 有个需求😂, 说是要找到 ID 断开指定长度的位置. 比如下面的表格: ID 22 和 24 之间断开了一个数, ID 25 和 30 之间断开了4个数. 我们现在就是想要找到 ID 断开 4 个数的位置, 而不是断开一个数的位置, 也就是说, 我们想要的 nextId 是 26, 而不是 23.

ID	备注
… 省略连续的部分
22	22 与 24 之间断开了1个数
24
25	25 与 30 之间断开了4个数: 26 27 28 29
30
31
32
… 省略连续的部分

  使用 2.1 的SQL, 是完不成这个任务的, 得来点新花样了😉

SELECT
/* 下一个ID */
t3.current_id + 1 next_id
FROM (
	SELECT 
	/* before_id 表示当前 ID 的上一个 真实的ID. 比如 25 */
	FIRST_VALUE(t2.ID) OVER (ORDER BY t2.id rows between 1 preceding and 1 following) before_id,
	/* before_id 表示当前 真实的ID. 比如 74915 */
	t2.id current_id,
	/* after_id 表示当前 ID 的下一个 真实的ID. 比如 75115 */
	LAST_VALUE(t2.id) OVER (ORDER BY t2.id rows between 1 preceding and 1 following) after_id 
	FROM (SELECT t1.id FROM MY_TEST_TABLE t1 ORDER BY t1.id asc) t2
) t3
WHERE 
/* 当前ID(25) 加4 小于后续的ID (30)
也就是说, 下面这个4, 限制了ID断开的长度. 在 Mybatis 里面我们可以把下面这行, 换成
t3.current_id + ${size} < t3.after_id AND t3.current_id - 1 > 1
上面的 size 表示断开的长度
 */
t3.current_id + 4 < t3.after_id AND t3.current_id - 1 > 1
/* 只取第一个 */
AND rownum = 1
;
-- 结果
NEXT_ID|
-------+
     26|

3 结语

  需要注意的是, 上面的SQL 只适合 Oracle, 它不适合 MySQL, 因为 FIRST_VALUE() 和 LAST_VALUE() 函数 可能在 MySQL 上面没有.