MySQL 如何重新编号主索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2643371/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to renumber primary index
提问by Kamil Mroczek
I have got a simple MySQL table and primary index (id) is not numbered one by one (1, 31, 35, 100 etc.). I want them to be numbered like (1, 2, 3, 4). Please tell me how to do it. I would also like to point that I am aware of possible consequences of the operation, but I just want to tidy up the table.
我有一个简单的 MySQL 表，主索引 (id) 没有一一编号（1、31、35、100 等）。我希望它们被编号为 (1, 2, 3, 4)。请告诉我怎么做。我还想指出，我知道操作可能带来的后果，但我只是想整理一下桌子。
回答by kv.
I agree other methods will work but I'm just giving a different idea. This will do without any temp table creation requirements::
我同意其他方法也行，但我只是给出一个不同的想法。这将不需要任何临时表创建要求：
SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);
回答by tareco
give a try to renumber method of dalmp (DALMP Database Abstraction Layer for MySQL using PHP.)
尝试重新编号 dalmp 的方法（使用 PHP 的 MySQL 的 DALMP 数据库抽象层。）
$db->renumber('table','uid');
basically it does this:
基本上它是这样做的：
SET @var_name = 0;
UPDATE Tablename SET ID = (@var_name := @var_name +1);
ALTER TABLE tablename AUTO_INCREMENT = 1
回答by Morfildur
The easiest Solution is:
最简单的解决方案是：
- Rename the table to tablename_temp
- Create a new table with the old name and the same structure
- INSERT INTO tablename (id, field1, field2, field3) SELECT NULL, field1, field2, field3, ... FROM tablename_temp;
- DROP tablename_temp
- 将表重命名为 tablename_temp
- 创建一个具有旧名称和相同结构的新表
- INSERT INTO tablename (id, field1, field2, field3) SELECT NULL, field1, field2, field3, ... FROM tablename_temp;
- 删除 tablename_temp
This will destroy all of your data if you are using foreign keys and i strongly suggest you leave the ids as they are. A Database is not untidy because the primary keys are not in sequence, a Database is untidy if it's referential integrity (id values pointing to the wrong or non-existant row in other tables) is broken.
如果您使用外键，这将破坏您的所有数据，我强烈建议您将 ID 保持原样。数据库不是乱七八糟的，因为主键没有顺序，如果它的引用完整性（id 值指向其他表中错误或不存在的行）被破坏，则数据库是乱七八糟的。
回答by Numenor
- Delete the "id" column.
- Create column named id with auto increment on(and do not allow nulls), it will have the new values you want.
- Assign this new "id" column as PK.
- 删除“id”列。
- 创建带有自动增量的名为 id 的列（并且不允许空值），它将具有您想要的新值。
- 将此新的“id”列指定为 PK。
edited later thanks to comments.
由于评论，稍后编辑。
回答by Tor Valamo
CREATE TABLE newtable
LIKE oldtable
SELECT NULL, col1, col2, col3, etc FROM oldtable;
Without including the primary key column in the select, but all the other columns.
在选择中不包括主键列，但包括所有其他列。
And the primary key column should be first in the table, or you'll need to set the NULLto the position of the primary key column. And the column should have a "NOT NULL" setting. Otherwise it will just be set as null, and that's kind of pointless.
并且主键列应该在表中的第一列，否则您需要将 设置NULL为主键列的位置。并且该列应具有“NOT NULL”设置。否则它只会被设置为空，这是毫无意义的。
The reason this works is because MySQL likes to ignore NULL inserts on auto_increments, and instead create a new number for it (provided that null isn't a valid value on that column).
这样做的原因是因为 MySQL 喜欢忽略 auto_increments 上的 NULL 插入，而是为其创建一个新数字（假设 null 不是该列上的有效值）。
回答by Your Common Sense
You don't want it. Primary key is not a number.It's just meaningless unique sequence.
你不想要它。主键不是数字。这只是无意义的唯一序列。
回答by PaN1C_Showt1Me
You have to define the auto-increment on your primary key (if it is a primary key)
您必须在主键上定义自动增量（如果它是主键）
Here's a link:
这是一个链接：
