Menu
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?
449
Reset the auto increment fieldALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.
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
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
