MySQL和INTauto_increment字段-MySQLandINTauto_incrementfields

I'm developing in LAMP (Linux+Apache+MySQL+PHP) since I remember myself. But one question was bugging me for years now. I hope you can help me to find an answer and point me into the right direction. Here is my challenge:

我正在开发LAMP (Linux+Apache+MySQL+PHP)，因为我记得自己。但是有一个问题困扰了我很多年。我希望你能帮我找到一个答案，给我指明正确的方向。这是我的挑战:

Say, we are creating a community website, where we allow our users to register. The MySQL table where we store all users would look then like this:

比如，我们正在创建一个社区网站，我们允许用户注册。我们存储所有用户的MySQL表是这样的:

CREATE TABLE `users` (
  `uid` int(2) unsigned NOT NULL auto_increment COMMENT 'User ID',
  `name` varchar(20) NOT NULL,
  `password` varchar(32) NOT NULL COMMENT 'Password is saved as a 32-bytes hash, never in plain text',
  `email` varchar(64) NOT NULL,
  `created` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of registration',
  `updated` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of profile update, e.g. change of email',
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

So, from this snippet you can see that we have a unique and automatically incrementing for every new user 'uid' field. As on every good and loyal community website we need to provide users with possibility to completely delete their profile if they want to cancel their participation in our community.

因此，从这段代码中，您可以看到，对于每个新用户“uid”字段，我们都有一个惟一的并自动递增的值。在每一个好的和忠诚的社区网站上，我们需要为用户提供完全删除他们的个人资料的可能性，如果他们想取消他们对我们社区的参与。

Here comes my problem. Let's say we have 3 registered users: Alice (uid = 1), Bob (uid = 2) and Chris (uid = 3). Now Bob want to delete his profile and stop using our community. If we delete Bob's profile from the 'users' table then his missing 'uid' will create a gap which will be never filled again. In my opinion it's a huge waste of uid's. I see 3 possible solutions here:

我的问题来了。假设我们有3个注册用户:Alice (uid = 1)、Bob (uid = 2)和Chris (uid = 3)，现在Bob想删除他的个人资料，停止使用我们的社区。如果我们从“用户”表中删除Bob的个人资料，那么他丢失的“uid”将产生一个永远不会被再次填充的空白。在我看来，这是对uid的巨大浪费。我看到了3个可能的解决方案:

1) Increase the capacity of the 'uid' field in our table from SMALLINT (int(2)) to, for example, BIGINT (int(8)) and ignore the fact that some of the uid's will be wasted.

1)将我们表中“uid”字段的容量从SMALLINT (int(2))增加到，例如，BIGINT (int(8))，忽略一些uid的将被浪费的事实。

2) introduce the new field 'is_deleted', which will be used to mark deleted profiles (but keep them in the table, instead of deleting them) to re-utilize their uid's for newly registered users. The table will look then like this:

2)引入新的字段“is_deleted”，该字段将用于标记已删除的概要文件(但是要将它们保存在表中，而不是删除它们)，以便重新利用它们的uid来针对新注册的用户。这个表格看起来是这样的:

CREATE TABLE `users` (
  `uid` int(2) unsigned NOT NULL auto_increment COMMENT 'User ID',
  `name` varchar(20) NOT NULL,
  `password` varchar(32) NOT NULL COMMENT 'Password is saved as a 32-bytes hash, never in plain text',
  `email` varchar(64) NOT NULL,
  `is_deleted` int(1) unsigned NOT NULL default '0' COMMENT 'If equal to "1" then the profile has been deleted and will be re-used for new registrations',
  `created` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of registration',
  `updated` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of profile update, e.g. change of email',
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

3) Write a script to shift all following user records once a previous record has been deleted. E.g. in our case when Bob (uid = 2) decides to remove his profile, we would replace his record with the record of Chris (uid = 3), so that uid of Chris becomes qual to 2 and mark (is_deleted = '1') the old record of Chris as vacant for the new users. In this case we keep the chronological order of uid's according to the registration time, so that the older users have lower uid's.

3)编写一个脚本，以便在删除之前的记录之后转移所有跟随的用户记录。如在我们的示例中,当鲍勃(uid = 2)决定取消他的形象,我们将取代他的记录与记录的克里斯(uid = 3),所以克里斯就等于2的uid和马克(is_deleted = ' 1 ')老克里斯作为新用户空的记录。在这种情况下，我们根据注册时间来保持uid的时间顺序，以便老用户的uid更少。

Please, advice me now which way is the right way to handle the gaps in the auto_increment fields. This is just one example with users, but such cases occur very often in my programming experience.

现在请建议我用哪种方式来处理auto_increment字段中的间隔。这只是用户的一个例子，但是这种情况经常发生在我的编程经验中。

Thanks in advance!

提前谢谢!

5 个解决方案

function mysql_fix_aigap($table,$column){

$fix_aigap=1;

$query_results=mysql_query("select * from $table");

while($row=mysql_fetch_array($query_results)){

mysql_query("update $table set `$column`='$fix_aigap' where `$column` like {$row[$column]};");

$fix_aigap=$fix_aigap+1;

  }

mysql_query("alter table `$table` AUTO_INCREMENT =$fix_aigap");

}

mysql_fix_aigap("gapped_table_to_be_fixed","column"); //"users" and "uid" in your case.