10

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?

Hemen Ashodia
  • 202
  • 2
  • 4
  • 21
  • 2
    I just wonder why would you ever need to do this? How do gaps in the `id` column matter anyway except when your counter is about to reach the limit[well, for that you should start thinking about using a GUID]. – Fr0zenFyr Sep 13 '13 at 13:37
  • Also see: https://stackoverflow.com/a/2214209/1839439 – Dharman Oct 16 '20 at 16:23

2 Answers2

30

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;

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

ALTER TABLE table AUTO_INCREMENT = 1;

This resets the next id to be MAX(id)+1 from the docs :

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

ALTER TABLE t2 AUTO_INCREMENT = value;

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

Manse
  • 37,765
  • 10
  • 83
  • 108
  • +1 great 1 liner query. I just wonder why would anyone ever need to do this? How do gaps in the `id` column matter anyway except when your counter is about to reach the limit[well, for that you should start thinking about using a GUID]. – Fr0zenFyr Sep 13 '13 at 13:35
  • That code can be applied to other tables at the SAME TIME in case of id as foreign key, Are there other risks? the `other tables` is not an issue for me. –  Mar 27 '17 at 02:50
  • 1
    `ALTER TABLE table AUTO_INCREMENT = 1;` is not needed with INNODB. – John Mar 04 '18 at 11:19
-1

There is a simple fix for this with php, i just tested it out.

$count = 0;  
while ($row = mysqli_fetch_array($res)){
         $count++;
mysqli_query($con, "UPDATE table SET id='".$count."' WHERE id='".$row['id']."'"); 
         }
Peter Gruppelaar
  • 126
  • 1
  • 9