做数值主键删除记录在数据库中得到重用未来新记录?Do numerical primary keys of deleted records in a database get reused for future new records?
例如,如果我有一个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!)
(原文：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:
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.
Suggested fix: innodb table should not lose the track of next number for auto_increment column after restart.
(原文：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"
(原文：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 :)
(原文：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
没有特别。如果键被读取序列或自动增量序列标识列只会堵塞,产生下一个值。不过,您可以禁用这个(设置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.
(原文：+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).)ConcernedOfTunbridgeWells的回复:谢谢。发现了。
(原文：Thanks. Well spotted.)
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.)
- 什么最好的方法访问数据库在一个PHP类?What the best way to access the database inside a class in PHP?
- MySqlCommand参数不工作MySqlCommand Parameter not Working
- 重装MYSQL(windows)——怎么去旧的数据与新安装吗?Re-installing MYSQL(windows) - How to get to the old data with the new install?
- 做一个mysql表中的ID auto_increment(事后)make an ID in a mysql table auto_increment (after the fact)
- 我如何升级mysql ?How do I upgrade mysql?
- 做数值主键删除记录在数据库中得到重用未来新记录?Do numerical primary keys of deleted records in a database get reused for future new records?
- 加入两个表没有返回的行Joining two tables without returning unwanted row
- 有干净的清理方式在MySQL重复的条目吗?Is there a clean way of cleaning up duplicate entries in MySQL?
- 社会安全号码存储Storing Social Security Numbers
- 为什么不要# 39;t。网络应用程序使用MySQL或允许使用MySQL的刀?Why don't more .NET applications use MySQL or a DAO that allows for the use of MySQL?
- mysql charset cli