MySQL 和 INT 自动增量字段

Question

从我记事起，我就在 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)。现在鲍勃想删除他的个人资料并停止使用我们的社区。如果我们从“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 并标记 (is_deleted = '1' ) Chris 的旧记录对于新用户来说是空缺的。在本例中，我们根据注册时间保留 uid 的时间顺序，以便较老的用户拥有较低的 uid。

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

提前致谢！

Answer 1

绝对不是移动用户 ID 的想法 - 这会在某个时候杀死你或你的 mysql 服务器。
假设您有 1,000,000 个用户，而用户 2 被删除 - 您必须将 999,999 条记录向下移动...就像查询一样简单，它仍然会锁定您的数据库一段时间。
我还认为这会扰乱在每个表的每个插入中设置的 auto_increment 值。
插入-> AI+1->插入-> AI+1->删除-> AI 保持不变...如果您移动所有 ID，则下一个 auto_increment 值仍将是 1,000,001，现在将留下 1,000,000 空。

我说 unsigned BIGINT 并忽略它 - 因为如果你接近 bigint 的极限，你还有许多其他问题需要解决；）

Answer 2

我编写了一个简单的 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=身份)

t.

Answer 3

首先;为什么你认为这是对uid的“浪费”？我的意思是，它只是一个整数（或 BIGINT），这不再是 70 年代了。

其次，如果您实施建议的选项之一，您所获得的性能损失远远大于“浪费”uid 所带来的空间损失。如果某个用户删除了他的个人资料，最坏的情况是，在他之后注册的每个用户都会获得一个新的 ID，因此您必须更新非常非常多的记录...

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

Answer 4

我会忽略这些间隙并确保您拥有所需的尽可能大的 id 范围。这些差距不会造成真正的伤害。尝试通过更新数据来修复它们可能会导致关系破裂，从而带来更多麻烦。

顺便说一句，在 MySQL INT(2) 中，2 指定 最大显示宽度，但不影响存储量。 INT(8) 使用与 INT(2) 相同的存储 - 使用 BIGINT 正如您所暗示的。

Answer 5

无符号整型的最大值为 4,294,967,295。目前互联网人口约为 18 亿。我建议您使用 unsigned int 来达到您的目的，并且不必担心序列中的间隙。

从哲学角度来说：Donald Knuth 曾经说过“我们应该忘记小效率，大约 97% 的时候说：过早的优化是万恶之源”。