使自动增量填充以前删除的数字 [英] Make auto increment fill previously deleted number

问题描述

可能的重复:
使用未使用的主键

我创建了一个带有自动增量的 id 列的表现在，当我删除该表中 id 为 8,12,30 的行时.

I created a table with an id column with auto increment Now when I deleted the rows with the id's 8,12,30 in that table.

现在，当我插入一条新记录时，我想将下一条记录插入 8 然后是 12 等等.

Now when I insert a new record I want to insert the next record into 8 then 12 etc..

有没有办法让 MySQL 自动增量来为我做这件事?

Is there a way to get MySQL auto increment to do this for me?

我尝试将自动增量设置为某个数字，但我不想那样做.

I tried to set autoincrement to some number, but I don't want to do it that way.

推荐答案

那时您不想使用自动递增的列.如果要填补空白，请将其设置为 int 列并处理存储过程或插入中的逻辑.

You don't want to use an auto-incrementing column then. If you want to fill in the gaps, set it to an int column and handle the logic in stored proc or insert.

由于它是一个 int 列，您可以按数字对它们进行排序.只需执行 SELECT Ids FROM Table Order By Ids 即可获取所有 Id 并检查返回数据集中的差距.

Since it's an int column, you can order them numerically. Just do a SELECT Ids FROM Table Order By Ids to get all of the Ids and check for the gaps in the returned dataset.

可能有更流畅的方法，但您可以使用游标循环结果，并与在整个循环中递增的 INT 变量进行比较.当您发现间隙(不匹配)时 - 中断循环并使用该 INT 值作为您的 INSERT id.

There's probably a smoother way of doing this, but you could loop the results with a cursor and compare to an INT variable that increments throughout the loop. When you find a gap (no match) - break the loop and use that INT value as your INSERT id.

我不会为您编写代码，但这些是帮助您朝着正确方向前进的一些步骤.它应该是你应该能够处理的一个非常基本的编程.

I won't write your code for you, but those are some steps to get you going in the right direction. It should be a really basic bit of programming you should be able to handle.

希望这会有所帮助.

正如其他人所指出的，最好的做法是留下间隙.除非桌子上有一些长度上限并且 Id 必须是 1-30(奇怪)，否则不要管它.填补空白没有任何好处.

As others have noted, your best move is to just leave the gaps. Unless there's some cap on the table as far as length and Ids MUST be 1-30 (weird), leave it alone. There's no benefit to filling in the gaps.

要考虑的另一件事:如果您出于某种原因确实必须保留 1-30，请不要删除您的行.添加一列以将每一行标记为活动或不活动，然后只需在需要时更新非活动的行，然后将它们标记为活动.这是非常hacky，但你的要求有点hacky，所以...

One more thing to consider: if you really do have to keep 1-30 for some reason, don't delete your rows. Add a column to flag each row as active or not and then just update the rows that are inactive when you need to and then flag them as active. This is VERY hacky, but your requirement is kinda hacky, so...

这篇关于使自动增量填充以前删除的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！