删除行后保持id连续性

Question

删除一行后如何保持id的连续性？

例如:表中有10行，然后删除最后5行，因此下一次创建将是id 11，但是6、7、8、9和10都消失了！因此，为了保持连续性，我希望新的创建只有6个。

Answer 1

您可以使用自连接来完成此操作：

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL

此查询搜索表tab中后继者不在tab中的第一个t1.id。

示例：

SELECT id FROM tab ORDER BY id;
 id
____

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;
 id 
____
  1

INSERT INTO tab VALUES (1), (2), (3), (4), (6), (7);

SELECT id FROM tab ORDER BY id;
 id
____
  1
  2
  3
  4
  6
  7

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;
 id 
____
  5

INSERT INTO tab VALUES (5)

SELECT COALESCE(MIN(t1.id + 1), 1) AS id 
FROM tab t1 LEFT JOIN tab t2 ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;
 id 
____
  8

但请注意，这种获取主键的方法对于并行调用可能会有问题。如果有多个并行线程以这种方式生成主键，则很容易发生冲突。id列应始终具有primary key约束，如果需要，应使用适当的锁或序列化事务来保护访问。