自动递增“id”的问题柱子
我的数据库表看起来像这张照片。 http://prntscr.com/22z1n
最近我创建了delete.php页面。它工作正常，但是当我删除第 21 个用户时，下一个注册用户获得第 24 个 ID，而不是 21。
是否可以将新注册用户信息放入第一个空行？ （本例为第21行）
在我的注册表中，新注册的用户可以写现有用户的名字，注册后就可以和他们成为朋友。对于这种友谊，我有另一个表将新注册用户的 ID 与现有用户关联起来。
为此，我在注册期间使用 mysql_insert_id 来获取新用户的 ID。但是在nex注册过程中删除第21行后，mysql_insert_id给了我数字21。但存储在第24行。并放入新用户的关联表21中。我想解决这个问题
My db table looks like this pic. http://prntscr.com/22z1n
Recently I've created delete.php page. it works properly but when i deleted 21th user next registered user gets 24th id instead of 21.
Is it possible to put newly registered users info to first empty row? (In this situation 21th row)
In my registration form, newly registering user can write names of existing users, and be friends with them after registration. For this friendship i have another table that associates id of newly registered user and existing user.
For this purpose i'm using mysql_insert_id during registration to get id for new user. But after deletion of 21th row during nex registration process mysql_insert_id gave me number 21. but stored in 24th row. And put to associations table 21 for new user. I wanna solve this problem
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
当您使用自动增量 id 列时，下一个条目将分配的值不会因删除条目而减少。这不是自动增量列的用途。数据库引擎将始终在新插入时增加该数字，而不会在删除时减少该数字。
When you use an autoincrement id column, the value that the next entry will be assigned will not be reduced by deleting an entry. That is not what an autoincrement column is used for. The database engine will always increment that number on a new insert and never decrement that number on a delete.
MySQL
auto_increment列在内部维护一个数字，并且始终会递增该数字，即使在删除之后也是如此。如果需要填充空格，则必须在 PHP 中自行处理，而不是在表定义中使用auto_increment关键字。如果您需要维护外键关系，则回滚以填充空行 ID 可能会导致各种困难，而且确实不建议这样做。
可以使用SQL语句重置
auto_increment，但不建议这样做，因为这会导致重复键错误。编辑
要按照注释中的描述强制执行外键关系，您应该在表定义中添加：
填写正确的表和列名称。现在，当用户从注册中删除时，他们的好友关联将被清空。如果您需要与不同的用户重新关联，则必须使用 PHP 来处理。
mysql_insert_id()不再有帮助。如果你需要找到删除后数据库中仍然存在的最高编号的id来与好友关联，请使用以下命令。
A MySQL
auto_incrementcolumn maintains a number internally, and will always increment it, even after deletions. If you need to fill in an empty space, you have to handle it yourself in PHP, rather than use theauto_incrementkeyword in the table definition.Rolling back to fill in empty row ids can cause all sorts of difficulty if you have foreign key relationships to maintain, and it really isn't advised.
The
auto_incrementcan be reset using a SQL statement, but this is not advised because it will cause duplicate key errors.EDIT
To enforce your foreign key relationships as described in the comments, you should add to your table definition:
Fill in the correct table and column names. Now, when a user is deleted from the registration, their friend association is nulled. If you need to reassociate with a different user, that has to be handled with PHP.
mysql_insert_id()is no longer helpful.If you need to find the highest numbered id still in the database after deletion to associate with friends, use the following.
自动增量是作为表的一部分进行跟踪的序列键。当您删除一行时，它不会返回。
Auto increment is a sequence key that's tracked as part of the table. It does not go back when you delete a row.
很容易，不。您可以做的（但我不建议这样做）是创建一个 SQL 函数来确定当前未占用的最小数字。或者您可以创建一个已删除 ID 的表，并从中获取最小的数字。或者，这是最好的想法，忽略差距并意识到数据库很好。
Easily, no. What you can do (but I don't suggest doing) is making an SQL function to determine the lowest number that isn't currently occupied. Or you can create a table of IDs that were deleted, and get the smallest number from there. Or, and this is the best idea, ignore the gaps and realize the database is fine.
您想要做的可以通过在表中添加一个名为 user_order 的额外列来实现。然后，您可以编写代码来管理插入和删除，以便该列始终是连续的，没有间隙。
这样您就可以避免使用 auto_increment 列时可能遇到的问题。
What you want to do is achievable by adding an extra column to your table called something like user_order. You can then write code to manage inserts and deletions so that this column is always sequential with no gaps.
This way you avoid the problems you could have messing around with an auto_increment column.
重置 auto_increment 值不是一个好习惯，但如果您确实需要这样做，那么您可以：
在每次删除后运行此查询。 Auto_increment 值不会设置为
1，这将自动设置尽可能低的值。It's not a good practice to reset auto_increment value, but if you really need to do it, so you can:
Run this query after every delete. Auto_increment value will not be set to
1, this will set the lowest possible value automatically.