做数值主键删除记录在数据库中得到重用未来新记录?Do numerical primary keys of deleted records in a database get reused for future new records?

- 此内容更新于:2014-12-30

例如,如果我有一个auto-numbered字段,没有指定这一领域,让我添加新记录数据库引擎为我捡。 因此,它会选择删除记录的数量吗?如果是,什么时候? / / SQL Server,MySQL。/ / 后续的问题:当数据库引擎的运行数据使用主键?


For example if I have an auto-numbered field, I add new records without specifying this field and let DB engine to pick it for me.
So, will it pick the number of the deleted record? If yes, when?

// SQL Server, MySQL. //

Follow-up question: What happens when DB engine runs out of numbers to use for primary keys?


NO. numerical primary keys will not reused, except you specify them manually(you should really avoid this!)

user984003的回复:从马丁看答案b # 248;gelund。在某些情况下他们是重用。

(原文:See answer from Martin Bøgelund. They are reused in some cases.)

Peter Parker的回复:马丁斯的答案是正确的,但是它与一个db引擎和罕见的情况下,显然是一个错误(不知道他们打算修复它)

(原文:Martins answer is correct, however it is related to a single DB-engine and rare circumstances and is obviously a bug(do not know if they plan to fix it though))


AFAIK, this could happen in MySQL:

How AUTO_INCREMENT Handling Works in InnoDB:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

After a restart of server. Innodb reuse previously generated auto_increment values. :

Suggested fix: innodb table should not lose the track of next number for auto_increment column after restart.

user984003的回复:是的,测试这太多# 39;年代真正:(我删除最高的id,然后重新启动服务器。然后我做了一个插入和id被重用。

(原文:Yes, tested this and it's true :( I deleted the highest id and then restarted the server. I then did an insert and the id was reused.)


Depends on the auto-numbering system. If you're using a sequence of any kind, the numbers of deleted records will not get reused, as the sequence does not know about them.

一般来说,不,这个数字不重用。 然而,你可以在像Oracle这样的产品————指定一个数字序列发生器周期,将重用。 是否这些都是删除记录的数字是您的应用程序的问题。

Generally, no, the numbers are not reused.

However, you can -- in products like Oracle -- specify a sequence generator which cycles around and will reuse numbers.

Whether those are numbers of deleted records or not is your applications's problem.

这个问题需要更精确: …“甲骨文序列” …“与MySQL autonumber列” …等等……

This question needs to be made more precise:

... "with Oracle Sequences"

... "with MySQL autonumber columns"

... etc...

z-boss的回复:我# 39;已经指定的两个引擎,我# 39;感兴趣。

(原文:I've specified two engines that I'm interested in.)

只要你正确地创建表你不会重用数据。 但是你可以补播标识列(在该软件)使用以下: ——输入表中最后一个有效条目的数量不是下一个号码被使用 执行CHECKIDENT(表名,补播,[NumberYouWantToStartAt]) 这当然是疯了…和不应该做的:)

As long as you create the table correctly you will not reuse numbers. However you can RESEED the identity column (IN MSSQL anyway) by using the following:

-- Enter the number of the last valid entry in the table not the next number to be used

DBCC CHECKIDENT ([TableName], RESEED, [NumberYouWantToStartAt])

This is of course insane... and should never be done :)

Kevin的回复:我是不会# 39;t说它不应该做的。在正确的情况下,它是完全适当的。

(原文:I wouldn't say it should NEVER be done. In the right situation, it is entirely appropriate.)

Dining Philanderer的回复:对不起,我写到最后一行在开玩笑……你是正确的,这有时是有用的。

(原文:Sorry I wrote that last line in jest... You are correct that this is useful sometimes.)


MySQL will not reuse IDs unless you truncate the table or delete from the table with no where clause (in which case MySQL, internally, simply does a truncate).

没有特别。如果键被读取序列或自动增量序列标识列只会堵塞,产生下一个值。不过,您可以禁用这个(设置identity_insert SQL Server),把任何你想要的数量列,只要它不违反唯一性约束。

Not specifically. If the key is being read from a sequence or autoincrementing identity column the sequence will just plug along and produce the next value. However, you can deactivate this (set identity_insert on on SQL Server) and put any number you want in the column as long as it doesn't violate the uniqueness constraint.

Lieutenant Frost的回复:+ 1,与未成年人校正/ SQL Server的警告——插入值标识列,设置IDENTITY_INSERT前必须指定显式标识值插入(见msdn.microsoft.com/en-us/library/ms188059.aspx)。

(原文:+1, with a minor correction/caveat for SQL Server - to insert a value into the identity column, SET IDENTITY_INSERT ON has to be specified before the explicit identity value is inserted (see msdn.microsoft.com/en-us/library/ms188059.aspx).)


(原文:Thanks. Well spotted.)

是的,这真的取决于你生成的id。 例如如果您使用GUID主键,最实现的随机新的GUID不太可能再选择另一个GUID,但它会给予足够的时间和GUID不是表中的insert语句将会很好,但是如果已经有一个GUID,你会得到一个主键约束违反。

Yeah, it really depends on the way you generate the id.

For example if you are using a GUID as the primary key, most implementations of getting a random new Guid are not likely to pick another guid again, but it will given enough time and if the Guid is not in the table the insert statement will go fine, but if there is already a guid there you will get a primary key constraint violation.


I consider the MySQL "feature" of reusing id's a bug.

Consider something like processing of file uploads. Using the database id as a filename is a good practice : simple, no risk of exploits with user-supplied filenames, etc.

You can't really make everything transactional when the filesystem is involved... you'll have to commit the database transaction then write the file, or write the file and commit the database transaction, but if one or both fail, or you have a crash, or your network filesystem has a fit, you might have a valid record in the database and no file, or a file without a database record, since the thing is not atomic.

If such a problem happens, and the first thing the server does when coming back is overwrite the ids, and thus the files, of rolled back transactions, it sucks. Those files could have been useful.

不,你想象一下,如果你的银行决定重用account_id——arghhhh ! !

no, imagine if your bank decided to re-use your account_id - arghhhh !!


(原文:This is really a comment, not an answer to the question. Please use "add comment" to leave feedback for the author.)