如何在auto_increment列中找到“holes”？

when I DELETE, as example, the id 3, I have this:

当我删除,例如,id 3,我有这个:

id | name
 1 |
 2 |    
 4 |
 5 |
 ...

now, I want to search for the missing id(s), because i want to fill the id again with:

现在,我想搜索丢失的id,因为我想再次填写id:

INSERT INTO xx (id,...) VALUES (3,...)

is there a way to search for "holes" in the auto_increment index?

有没有办法在auto_increment索引中搜索“漏洞”?

thanks!

5 个解决方案

#1

You can find the top value of gaps like this:

您可以找到这样的差距的最高值:

select t1.id - 1 as missing_id
from mytable t1
left join mytable t2 on t2.id = t1.id - 1
where t2.id is null

#2

The purpose of AUTO_INCREMENT is to generate simple unique and meaningless identifiers for your rows. As soon as you plan to re-use those IDs, they're no longer unique (not at least in time) so I have the impression that you are not using the right tool for the job. If you decide to get rid of AUTO_INCREMENT, you can do all your inserts with the same algorithm.

AUTO_INCREMENT的目的是为您的行生成简单唯一且无意义的标识符。一旦您计划重新使用这些ID,它们就不再是唯一的(至少不会及时),所以我的印象是您没有使用正确的工具来完成工作。如果您决定摆脱AUTO_INCREMENT,则可以使用相同的算法完成所有插入操作。

As about the SQL code, this query will match existing rows with the rows that has the next ID:

至于SQL代码,此查询将匹配现有行与具有下一个ID的行:

SELECT a.foo_id, b.foo_id
FROM foo a
LEFT JOIN foo b ON a.foo_id=b.foo_id-1

E.g.:

 1 NULL
 4 NULL
10 NULL
12 NULL
17 NULL
19   20
20 NULL
24   25
25   26
26   27
27 NULL

So it's easy to filter out rows and get the first gap:

因此,过滤掉行并获得第一个差距很容易:

SELECT MIN(a.foo_id)+1 AS next_id
FROM foo a
LEFT JOIN foo b ON a.foo_id=b.foo_id-1
WHERE b.foo_id IS NULL

Take this as a starting point because it still needs some tweaking:

以此为出发点,因为它仍需要一些调整:

You need to consider the case where the lowest available number is the lowest possible one.

您需要考虑最低可用数量是最低可能数量的情况。

You need to lock the table to handle concurrent inserts.

您需要锁定表以处理并发插入。

In my computer it's slow as hell with big tables.

在我的电脑里,它和大桌子一样慢。

#3

I think the only way you can do this is with a loop: Any other solutions wont show gaps bigger than 1:

我认为你能做到这一点的唯一方法就是循环:任何其他解决方案都不会显示大于1的间隙:

insert into XX values (1)
insert into XX values (2)
insert into XX values (4)
insert into XX values (5)
insert into XX values (10)

declare @min int
declare @max int

select @min=MIN(ID) from xx
select @max=MAX(ID) from xx

while @min<@max begin
    if not exists(select 1 from XX where id = @min+1) BEGIN
        print 'GAP: '+ cast(@min +1 as varchar(10))
    END

    set @min=@min+1
end

result:

GAP: 3
GAP: 6
GAP: 7
GAP: 8
GAP: 9

#4

First, I agree with the comments that you shouldn't try filling in holes. You won't be able to find all the holes with a single SQL statement. You'll have to loop through all possible numbers starting with 1 until you find a hole. You could write a sql function to do this for you that could then be used in a function. So if you wrote a function called find_first_hole you could then call it in an insert like:

首先,我同意你不应该尝试填补漏洞的评论。您将无法使用单个SQL语句找到所有漏洞。你必须循环遍历从1开始的所有可能的数字,直到找到一个洞。您可以编写一个sql函数来为您执行此操作,然后可以在函数中使用它。因此,如果您编写了一个名为find_first_hole的函数,则可以在插入中调用它,如:

INSERT INTO xx (id, ...) VALUES (find_first_hole(), ...)

#5

This is a gaps&island problem, see my (and other) replies here and here. In most cases, gaps&islands problems are most elegantly solved using recursive CTE's, which are not available in mysql.

这是一个空白和岛屿问题,请参阅我和(此处)的回复。在大多数情况下,使用递归CTE可以最优雅地解决间隙和岛屿问题,这在mysql中是不可用的。

#1