自动递增的“id”问题柱 [英] Problem with auto-incremented "id" column

问题描述

我的数据库表看起来像这个图片。 http://prntscr.com/22z1n

My db table looks like this pic. http://prntscr.com/22z1n

最近我创建了delete.php页面。它工作正常，但当我删除第21个用户下一个注册用户获取第24的id而不是21.

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.

是否可能将新注册的用户信息放在第一个空行？ （在这种情况下第21行）

Is it possible to put newly registered users info to first empty row? (In this situation 21th row)

在我的注册表单中，新注册的用户可以写入现有用户的姓名，并在注册后与他们成为朋友。对于这种友谊，我有另一个表，关联的新注册的用户和现有用户的ID。

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.

为此目的，我在注册期间使用mysql_insert_id获取新用户的id。但是在nex注册过程中删除第21行后，mysql_insert_id给了我21号，但存储在第24行。并放入关联表21为新用户。我想解决这个问题

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

推荐答案

MySQL auto_increment 在内部，并将总是递增它，即使删除后。如果你需要填充一个空的空间，你必须自己在PHP中处理，而不是在表定义中使用 auto_increment 关键字。

A MySQL auto_increment column 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 the auto_increment keyword in the table definition.

如果你保持外键关系，滚动返回以填充空行id可能会导致各种困难，并且不建议这样做。

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.

auto_increment 可以使用SQL语句重置，但不建议这样做，因为它会导致重复的键错误。

The auto_increment can be reset using a SQL statement, but this is not advised because it will cause duplicate key errors.

-- Doing this will cause problems!
ALTER table AUTO_INCREMENT=12345;

EDIT
要强制执行您的外键关系，注释，你应该添加到你的表定义：

EDIT To enforce your foreign key relationships as described in the comments, you should add to your table definition:

FOREIGN KEY (friendid) REFERENCES registration_table (id) ON DELETE SET NULL;

填写正确的表和列名称。现在，当用户从注册中删除时，他们的朋友关联被取消。如果需要与其他用户重新关联，则必须使用PHP处理。 mysql_insert_id（）不再有用。

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.

如果您需要在数据库中找到最高编号的id删除以与朋友关联，使用以下。

If you need to find the highest numbered id still in the database after deletion to associate with friends, use the following.

SELECT MAX(id) FROM registration_table;

这篇关于自动递增的“id”问题柱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！