删除其中一些表格后，修复mysql表行ID中的空白 [英] Fixing gaps in mysql table row id after we delete some of them

问题描述

我有一个mysql表，其中有17000多行.我从其中的一部分删除了大约530行.现在，每一行都有一个顺序的AUTO-INCREAMENTED数字主键.如您所了解，现在已删除了若干行编号.所以我只想问问有没有办法以某种完美的顺序再次修复所有行?

I have a mysql table with more than 17000 rows in it. And I have deleted about 530 rows from some mid part of it. Now each row had a sequential AUTO-INCREAMENTED number primary key. As you can understand now several numbers for rows have been deleted. So i just wanted to ask that is there any way to fix all rows again in some flawless order?

推荐答案

您可以但要小心使用此主键作为外键的其他表

You can but be carefull of other tables using this primary key as a foreign key

SET @count = 0;
UPDATE table SET table.id = @count:= @count + 1;

这将更新表table的id列...然后您需要重置auto_increment:

this will update the id column of the table table ... you then need to reset the auto_increment :

ALTER TABLE table AUTO_INCREMENT = 1;

这会将文档:

更改用于新的AUTO_INCREMENT计数器的值 行，请执行以下操作:

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;  

您不能将计数器重置为小于或等于该值的值 已经被使用了.对于MyISAM，如果该值小于或等于 到当前在AUTO_INCREMENT列中的最大值，该值 重置为当前的最大值加一

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one

这篇关于删除其中一些表格后，修复mysql表行ID中的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！