MySQL和INT自增字段

Question

MySQL和INT自增字段

3

我自己记得起来就一直在使用LAMP（Linux+Apache+MySQL+PHP）进行开发。但是有一个问题困扰了我多年。希望你能帮我找到答案并指引我正确的方向。以下是我的挑战：

假设我们正在创建一个社区网站，允许用户注册。我们存储所有用户的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;

因此，从这个片段中，您可以看到我们为每个新用户创建了一个独特且自动递增的“uid”字段。在每个良好和忠诚的社区网站上，我们需要为用户提供完全删除其个人资料的可能性，以便取消他们在我们社区的参与。

现在出现了我的问题。假设我们有3个注册用户：Alice（uid = 1），Bob（uid = 2）和Chris（uid = 3）。现在Bob想删除他的个人资料并停止使用我们的社区。如果我们从“users”表中删除Bob的个人资料，则他缺失的'uid'将会创建一个间隙，将永远不会再被填补。我认为这是uid浪费的巨大损失。我看到以下3种可能的解决方案：

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

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）编写一个脚本，在删除先前的记录后，将所有以下用户记录移位。例如，在我们的情况下，当Bob（uid = 2）决定删除他的个人资料时，我们将用Chris（uid = 3）的记录替换他的记录，以便Chris的uid变为2，并将Chris的旧记录标记为“1”为空闲状态供新用户使用。在这种情况下，我们保持uid的时间顺序按照注册时间进行排序，以便较早的用户具有较低的uid。

请告诉我现在处理auto_increment字段中间空缺的正确方法。这只是用户的一个示例，但这种情况在我的编程经验中经常发生。

提前感谢！

- PHPguy

可能是MYSQL何时修复自增间隔的重复问题的重复。 - martin clayton

5个回答

1

我编写了一个简单的PHP函数来“填充”由“删除”查询引起的auto_increment间隙，并设置正确的“下一个auto_increment”值。

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.

（此脚本假定您已连接到服务器并选择了数据库！）

这就是技术上的答案。

我个人认为，不建议将“变量”uid分配给用户名，这不是一个非精神分裂的方式！（id=identity）

t.

- telebabbo

0

首先，你为什么认为使用uid是“浪费”呢？我是说，这只是一个整数（或BIGINT），现在已经不是70年代了。

其次，如果你实施你建议的选项之一，你会遭受更大的性能损失，而从“浪费”uid中获得的空间损失要小得多。如果某个用户删除了他的个人资料，最坏的情况是，他之后注册的每个用户都会获得一个新的id，因此你必须更新非常多的记录...

我必须承认，当我刚开始编程时，我记得要习惯自动递增列中的间隙。但你必须接受它们，继续前进，让它们存在...

- user254875486

谢谢Lex的意见，正如你所说，我必须像你一样习惯auto_increment中的间隙！ - PHPguy

0

我会忽略这些间隔，确保你拥有尽可能大的ID范围。这些间隔并没有真正的危害。试图通过更新数据来修复它们可能会引入更麻烦的损坏关系。

顺便说一下，在MySQL中，INT（2）中的2指定了最大显示宽度，但不影响存储量。INT（8）使用与INT（2）相同的存储 - 使用BIGINT就像你暗示的那样。

- martin clayton

0

无符号整数的最大值为4,294,967,295。互联网当前的人口约为18亿人。我建议您在您的目的中使用无符号整数，不必担心序列中的间隙。

在哲学上：唐纳德·克努斯曾经说过：“我们应该忘记小效率，大约97%的时间：过早优化是万恶之源。”

- Mark Maunder

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Toby · Accepted Answer

绝对不要使用移动用户ID的想法 - 这将在某个时候使您或您的MySQL服务器崩溃。假设您有100万个用户，用户2被删除 - 您必须将999,999条记录向下移动...尽管查询很简单，但它仍然会锁定您的数据库一段时间。此外，我认为这会干扰您为每个表插入设置的自动递增值。插入-> AI + 1->插入-> AI + 1->删除-> AI保持不变...如果您将所有ID都移动，则下一个自动递增值仍将是1,000,001，现在将留下1,000,000个空位。

我建议使用无符号BIGINT并忽略它 - 因为如果您接近bigint的限制，您将有许多其他问题要解决 ;)