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 个解决方案
#1
2
definitely not the moving user ids idea - that will kill you or your mysql server at some point. lets say you have 1,000,000 users and user 2 gets deleted - you had to shift 999,999 records one down... as simple as the query would be it would still lock your db for a while. also i think that messes with your auto_increment value which is set on each insert for each table. insert -> AI+1 -> insert -> AI+1 -> delete -> AI stays the same... if you would shift all the ID's the next auto_increment value would still be 1,000,001 which would now leave 1,000,000 empty.
绝对不是移动用户id的想法——这会在某个时候杀死你或你的mysql服务器。假设您有1,000,000个用户，而user 2被删除——您必须将999,999条记录向下移动1条……查询的简单之处在于，它仍然会在一段时间内锁定您的db。另外，我认为这会与auto_increment值混淆，该值设置在每个表的每个insert上。插入-> AI+1 ->插入-> AI+1 ->删除-> AI保持不变…如果您将所有的ID转换为下一个auto_increment值，则仍然是1000001，现在将会留下1,000,000个空值。
i say unsigned BIGINT and ignore it - cause if you come even close to the limit of bigint you have many other problems to solve ;)
我说unsigned BIGINT然后忽略它——因为如果你接近BIGINT的极限你还有很多其他的问题要解决;)
#2
1
I wrote a simple PHP function to "fill" auto_increment gaps caused by "delete" queries and set the correct "next auto_increment" value.
我编写了一个简单的PHP函数来“填充”由“delete”查询引起的“auto_increment”间隔，并设置正确的“next 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");
}
and call it with:
并称之为:
mysql_fix_aigap("gapped_table_to_be_fixed","column"); //"users" and "uid" in your case.
(This script assumes that you are already connected to the server and you have selected the database!)
(此脚本假设您已经连接到服务器，并且已经选择了数据库!)
And this was the technical answer.
这就是技术上的答案。
In my honest opinion, I wouldn't recommend to assign a "variable" uid to the username, it's the non-schizophrenical way! (id=identity)
老实说，我不建议给用户名分配“变量”uid，这是一种非精神分裂的方式!(id =身份)
t.
t。
#3
0
First of all; why do you think it's a "waste" of uids? I mean, it's just an integer (or BIGINT), this aren't the 70's anymore.
首先;为什么你认为这是对uid的“浪费”?我的意思是，它只是一个整数(或BIGINT)，这已经不是70了。
Secondly, the performance loss you get if you implement one of your suggested options is far greater than the spatial loss you get from "wasting" uids. If some user deletes his profile, at worst, every user that registered after him gets a new id, so you have to update very, very much records...
其次，如果您实现了建议的选项之一，那么您的性能损失将远远大于“浪费”uid带来的空间损失。如果某个用户删除了他的个人资料，最坏的情况是，每个在他之后注册的用户都得到了一个新的id，所以你必须更新非常非常多的记录……
I must admit, when I just started programming I remember having to get used to gaps in auto-increment columns. But you will have to accept them, move on, and just let them exist...
我必须承认，当我刚开始编程时，我记得我不得不习惯自动增量列中的空白。但是你必须接受他们，继续前进，让他们存在……
#4
0
I'd just ignore the gaps and ensure you have as large a range of ids as needed. The gaps do no real harm. Trying to fix them up by updating data might introduce broken relationships that are more trouble.
我将忽略这些空白，并确保在需要的时候有大量的id。这些差距不会造成真正的伤害。试图通过更新数据来修复它们可能会导致更麻烦的关系破裂。
By the way in MySQL INT(2) the 2 specifies the maximum display width, but doesn't affect the amount of storage. INT(8) uses the same storage as INT(2) - use BIGINT as you imply.
顺便说一下，在MySQL INT(2)中，2指定了最大显示宽度，但不影响存储的数量。INT(8)使用与INT(2)相同的存储—使用BIGINT，如您所暗示的。
#5
0
The maximum value of an unsigned int is 4,294,967,295. The current population of the internet is approximately 1.8 billion people. I'd recommend using an unsigned int for your purposes and don't worry about gaps in your sequence.
无符号整数的最大值为4,294,967,295。目前的互联网人口约为18亿。出于您的目的，我建议您使用无符号int，不要担心序列中的间隔。
On a philosophical note: Donald Knuth once said "We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil".
从哲学的角度来看:Donald Knuth曾经说过“我们应该忘记小的效率，大约97%的时候是这样的:过早的优化是所有邪恶的根源”。
#1
2
definitely not the moving user ids idea - that will kill you or your mysql server at some point. lets say you have 1,000,000 users and user 2 gets deleted - you had to shift 999,999 records one down... as simple as the query would be it would still lock your db for a while. also i think that messes with your auto_increment value which is set on each insert for each table. insert -> AI+1 -> insert -> AI+1 -> delete -> AI stays the same... if you would shift all the ID's the next auto_increment value would still be 1,000,001 which would now leave 1,000,000 empty.
绝对不是移动用户id的想法——这会在某个时候杀死你或你的mysql服务器。假设您有1,000,000个用户，而user 2被删除——您必须将999,999条记录向下移动1条……查询的简单之处在于，它仍然会在一段时间内锁定您的db。另外，我认为这会与auto_increment值混淆，该值设置在每个表的每个insert上。插入-> AI+1 ->插入-> AI+1 ->删除-> AI保持不变…如果您将所有的ID转换为下一个auto_increment值，则仍然是1000001，现在将会留下1,000,000个空值。
i say unsigned BIGINT and ignore it - cause if you come even close to the limit of bigint you have many other problems to solve ;)
我说unsigned BIGINT然后忽略它——因为如果你接近BIGINT的极限你还有很多其他的问题要解决;)
#2
1
I wrote a simple PHP function to "fill" auto_increment gaps caused by "delete" queries and set the correct "next auto_increment" value.
我编写了一个简单的PHP函数来“填充”由“delete”查询引起的“auto_increment”间隔，并设置正确的“next 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");
}
and call it with:
并称之为:
mysql_fix_aigap("gapped_table_to_be_fixed","column"); //"users" and "uid" in your case.
(This script assumes that you are already connected to the server and you have selected the database!)
(此脚本假设您已经连接到服务器，并且已经选择了数据库!)
And this was the technical answer.
这就是技术上的答案。
In my honest opinion, I wouldn't recommend to assign a "variable" uid to the username, it's the non-schizophrenical way! (id=identity)
老实说，我不建议给用户名分配“变量”uid，这是一种非精神分裂的方式!(id =身份)
t.
t。
#3
0
First of all; why do you think it's a "waste" of uids? I mean, it's just an integer (or BIGINT), this aren't the 70's anymore.
首先;为什么你认为这是对uid的“浪费”?我的意思是，它只是一个整数(或BIGINT)，这已经不是70了。
Secondly, the performance loss you get if you implement one of your suggested options is far greater than the spatial loss you get from "wasting" uids. If some user deletes his profile, at worst, every user that registered after him gets a new id, so you have to update very, very much records...
其次，如果您实现了建议的选项之一，那么您的性能损失将远远大于“浪费”uid带来的空间损失。如果某个用户删除了他的个人资料，最坏的情况是，每个在他之后注册的用户都得到了一个新的id，所以你必须更新非常非常多的记录……
I must admit, when I just started programming I remember having to get used to gaps in auto-increment columns. But you will have to accept them, move on, and just let them exist...
我必须承认，当我刚开始编程时，我记得我不得不习惯自动增量列中的空白。但是你必须接受他们，继续前进，让他们存在……
#4
0
I'd just ignore the gaps and ensure you have as large a range of ids as needed. The gaps do no real harm. Trying to fix them up by updating data might introduce broken relationships that are more trouble.
我将忽略这些空白，并确保在需要的时候有大量的id。这些差距不会造成真正的伤害。试图通过更新数据来修复它们可能会导致更麻烦的关系破裂。
By the way in MySQL INT(2) the 2 specifies the maximum display width, but doesn't affect the amount of storage. INT(8) uses the same storage as INT(2) - use BIGINT as you imply.
顺便说一下，在MySQL INT(2)中，2指定了最大显示宽度，但不影响存储的数量。INT(8)使用与INT(2)相同的存储—使用BIGINT，如您所暗示的。
#5
0
The maximum value of an unsigned int is 4,294,967,295. The current population of the internet is approximately 1.8 billion people. I'd recommend using an unsigned int for your purposes and don't worry about gaps in your sequence.
无符号整数的最大值为4,294,967,295。目前的互联网人口约为18亿。出于您的目的，我建议您使用无符号int，不要担心序列中的间隔。
On a philosophical note: Donald Knuth once said "We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil".
从哲学的角度来看:Donald Knuth曾经说过“我们应该忘记小的效率，大约97%的时候是这样的:过早的优化是所有邪恶的根源”。