如何找到 MySQL 记录集中的 ID 间隙？

Question

这里的问题与我遇到的另一个问题有关...

我有数百万条记录，并且每条记录的ID都是自动递增的，不幸的是有时生成的ID有时会被丢弃，因此之间存在很多差距身份证。

我想找到缺口，重新利用那些被废弃的id。

在 MySQL 中执行此操作的有效方法是什么？

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，您就可以随时再次运行它。

Answer 2

以下将为 mytab 中整数字段“n”中的每个间隙返回一行：

/* 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;

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);

Answer 4

如果您使用的是 MariaDB，您有一个更快的选项

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

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