mysql自增主键id
在添加到最后添加的 ID 号之上之前，如何查找已使用但因不再使用而被删除的删除 ID 号
例如：
ID Name
1 Orange
5 Apple
我打开了自动增量，我想添加香蕉进入数字 2，但自动增量将其添加为 ID 6
How do you look for a delete ID number that has been used, but deleted as is no longer used, before you just append on top of the last ID number that has been added
For example:
ID Name
1 Orange
5 Apple
I have auto increment on, I want to add Banana in to number 2, but auto increment is adding it as ID 6
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这不是自动增量的工作原理。
我几乎肯定你必须找到丢失的 ID 并自己分配它们。
您可能需要重新标记您的问题以包含您使用的数据库。我假设MySql？
Thats just not how auto-increment works.
I'm almost positive you'll have to find missing ids and assign them yourself.
You may want to re-tag your question to include the database your using. I'm assuming MySql?
我认为自动增量功能通常用于在表上创建唯一键，并且重复使用唯一键并不是键的正常使用，因为删除一条记录然后插入一条记录并不一定意味着插入的记录与删除的记录相同。即，从直观意义上讲，该键实际上并不是“唯一”的，因为两行共享该键，即使从技术上讲，也不存在被打破的唯一键约束。
请注意，在您自己的实现中，您必须担心并发问题。即两个插入同时发生，所以...
1) 用户 A 想要插入，因此搜索最低的可用 ID
2) 用户 B 想要插入，因此搜索最低的可用 ID
3）用户A找到最小的可用ID 2
4) 用户B找到最小的可用ID 2
5) 用户A插入一行并使用ID 2
6) 用户 B 插入一行并尝试使用 ID 2，要么成功（这使得键不再唯一），要么失败，因为 ID 2 已被使用。.
这个的预期用途是什么？
I think that generally the auto-increment feature is used to create a unique key on tables and that re-using the unique key isn't the normal use of the key, because deleting a record and then inserting one doesn't necessarily mean that the record being inserted is the same one that was deleted. i.e. the key wouldn't actually be "unique" in the intuitive sense because two rows have shared that key, even though technically there would be no unique key constraints that are broken.
Please note that with your own implementation you have to worry about concurrency issues. i.e. two inserts are happening at once so...
1) user A wants to insert so searches for the lowest available ID
2) user B wants to insert so searches for the lowest available ID
3) user A finds the lowest available ID of 2
4) user B finds the lowest available ID of 2
5) user A inserts a row and uses ID 2
6) user B inserts a row and tries to use ID 2 and either succeeds (which makes the key no longer unique) or fails because ID 2 has already been used..
What's the intended use of this?
这就是 auto-inc 的工作原理。
如果你正在运行MySQL，我认为它有一些内置的东西可以为你做到这一点，但我可能是错的，因为我在文档中找不到它。
您也可以自己编写脚本。这将是一个在 Web 服务器和数据库服务器上都非常密集的脚本，因此不应该运行得太频繁。
Auto-inc 多年来一直以同样的方式运作。显然它正在做正确的事情。除非您有特殊原因不希望主键中有间隙（即使如此，您也应该认真重新考虑该字段的用途），否则就顺其自然吧。如果该字段是 int 10 并且无符号，那么您将有足够的回旋余地...
It's just how auto-inc works.
If you are running MySQL, I think it has something built-in which does this for you, but I could be wrong since I can't find it in the documentation.
You can also write a script yourself. It'll be a pretty intense script on both the web server and the database server so it shouldn't be ran too often.
Auto-inc has worked the same for years. It's obviously doing something right. Unless you have a particular reason for not wanting gaps in your primary keys (and even then, you should seriously reconsider what you're using the field for), just let it be. If the field is int 10 and unsigned, you will have plenty of wiggle room...
您必须编写自己的代码来识别最低的未使用标识符，并在 INSERT 查询中手动分配它。您的 AUTO_INCREMENT 列工作正常，它不会重用已删除的标识符。
You would have to write your own code to identify the lowest unused identifier and assign it manually in your INSERT query. Your AUTO_INCREMENT column is working properly, it will not reuse deleted identifiers.
Banana 的 ID 为 6 是正确的——这正在按预期工作。自动增量所做的只是允许您在不知道以前的 ID 的情况下将新行插入表中。基本上可以让你插入数据而不用担心覆盖ID，而且你可以偷懒。
自动增量的工作方式是它有一个计数器，每插入一行就会增加一个计数器。当您删除行时，计数器不会改变。
我认为您所期望的是数据库会自动向下移动行以填充缺失的 ID。虽然这在链表数据结构中可能是正确的，但在本例中并非如此。
填补这些空白的唯一方法是手动执行，或者编写一个脚本来填补空白或重新排列表或 ID。但确实没有必要这样做，因为当您检索行以供标记使用时，您可以使用有序列表或简单循环来处理此问题，并且间隙根本不相关。
Banana having an ID of 6 is correct — this is working as it was intended. All auto-increment does is allow you to insert new rows into the table without knowing the previous ID. Basically allows you to insert data without worrying about overwriting the ID, and you can be lazy.
The way auto increment works is that it has a counter that goes up for every row inserted. When you delete rows the counter doesn't change.
I think what you are expecting is that the database would automatically move down rows to fill in the missing IDs. While this might be true in a linked list data structure, it is not in this case.
The only way you can fill in those gaps is to either manually do it, or write a script that would either fill the gaps or rearrange the table or ids. There is really no need for this though, because when you retrieve the rows for markup use, you can use an ordered list or a trivial loop to handle this, and the gaps would simply be irrelevant.
好吧，我有答案了。但如果你使用它，我就会出来打败你！该子查询将找到 ID 中最低的空洞。只需将
table替换为您的表名称即可。Ok, I have an answer. But if you use it, I'm going to come out there and beat you up! This subquery will find the lowest hole in the IDs. Just replace the word
tablewith your table name.