如何在MySQL记录集中查找ID间隙？

Question

这里的问题与我曾经提出的另一个问题有关...我有数百万条记录，每个记录的ID都是自动增加的，但不幸的是，有时会丢弃生成的ID，因此ID之间存在许多空缺。 我想找到这些空缺，并重新使用被抛弃的ID。在MySQL中有效完成这项任务的方法是什么？

- qodeninja

---

相关链接：https://dev59.com/dHA65IYBdhLWcg3wogEb - Maxime Pacary

如果您使用INT作为主键，您可以拥有20亿多条记录。为什么要费心去填补空缺呢？您的数字用完了吗？我发现知道这些数字对应记录添加的顺序是有优势的。 - minboost

也许将主键类型更改为BIGINT（如果INT提供的4亿个值太短）会比尝试在一个非常大的表上重用ID遇到更少的性能问题。 - Maxime Pacary

我还没有考虑到也许不用担心这些间隙可能更好。 - qodeninja

有些人在你之前曾经想过重复使用废弃的身份识别号码（有时是属于已故人士的公民身份号码），而这个“聪明”的决定给继承这些被重复使用的号码的人带来了无尽的问题。我绝对不建议以任何方式去做这样的事情。 - jap1968

@jap1968 再次阅读这条评论让我开怀大笑，它真的很好地说明了问题。 - qodeninja

Answer 1

首先，您试图通过重用跳过的值获得什么优势？普通的INT UNSIGNED可以让您计数到4,294,967,295。对于“数百万条记录”而言，在数据库耗尽有效ID之前，其数据库必须增长一千倍。 （然后使用BIGINT UNSIGNED将使您的值增加到18,446,744,073,709,551,615。）

试图回收MySQL跳过的值可能会浪费大量时间来补偿MySQL从一开始就不关心的问题。

话虽如此，您可以使用以下方法查找缺失的ID：

SELECT id + 1
FROM the_table
WHERE NOT EXISTS (SELECT 1 FROM the_table t2 WHERE t2.id = the_table.id + 1);

这将仅找到每个序列中的第一个缺失数字（例如，如果您有{1, 2, 3, 8, 10}，它将找到{4,9}），但很可能是高效的，并且当您填写ID后，可以随时再次运行它。

- VoteyDisciple

---

如果1是第一个空缺，它将不会被返回。 - morandi3

在我的情况下，每个缺失的数字都很重要，答案的最后一段也是如此 :) +1 赞同 - AamirR

Answer 2

以下内容将为"n"整数字段中的每个间隙返回一行，其中包含mytab：

/* cs will contain 1 row for each contiguous sequence of integers in mytab.n
   and will have the start of that chain.
   ce will contain the end of that chain */
create temporary table cs (row int auto_increment primary key, n int);
create temporary table ce like cs;
insert into cs (n) select n from mytab where n-1 not in (select n from mytab) order by n;
insert into ce (n) select n from mytab where n+1 not in (select n from mytab) order by n;
select ce.n + 1 as bgap, cs.n - 1 as egap
  from cs, ce where cs.row = ce.row + 1;

如果你想要的不是间隔而是连续的链条，那么最后的选择应该是：

select cs.n as bchain, ce.n as echain from cs,ce where cs.row=ce.row;

- ccc

---

第二个查询“select cs.n as bchain, ce.n as echain from cs,ce where cs.row=ce.row;”在显示上连接了比实际存在的更大的间隔，但第一个查询完全正常。 - magdmartin

Answer 3

这个解决方案更好，如果你需要将第一个元素包含为1：

SELECT
    1 AS gap_start,
    MIN(e.id) - 1 AS gap_end
FROM
    factura_entrada e
WHERE
    NOT EXISTS(
        SELECT
            1
        FROM
            factura_entrada
        WHERE
            id = 1
    )
LIMIT 1
UNION
    SELECT
        a.id + 1 AS gap_start,
        MIN(b.id)- 1 AS gap_end
    FROM
        factura_entrada AS a,
        factura_entrada AS b
    WHERE
        a.id < b.id
    GROUP BY
        a.id
    HAVING
        gap_start < MIN(b.id);

- jalopezsuarez

Answer 4

如果您正在使用 MariaDB，您有更快的选项。

SELECT * FROM seq_1_to_50000 where seq not in (select col from table);

文档：https://mariadb.com/kb/en/mariadb/sequence/

- Moshe L