How to organize auto-increment numbering of a column Id of a table in Mysql?

Question

I would like after each insert or delete from my table to use an SQL script to rearrange the Ids.

It’s kind of like this now:

Id Nome
 1 João
 3 José
 5 Ricardo

I want to leave it like this:

Id Nome
 1 João
 2 José
 3 Ricardo

How to do this the best way? (I don’t have keys connected to Id)

Answer 1

You can use the following:

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

Full example: http://sqlfiddle.com/#! 2/750ce/1

(Source: https://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key)

Answer 2

There are 2 ways, but both are expensive to use in everyday life.

First I’ll use as an example your situation, you deleted id 4 in which case it would be something like this:

update tabela
set id = id - 1
where id > 4

Or, when it is a large amount and you don’t know exactly which ones were deleted, the "right" would be to save your data a temporary or auxiliary table. Drop your table, and create it again with the id of 0. But I don’t know if it’s viable. Or instead of dropping the entire table, simply delete the records and reset the auto-increment id like this:

ALTER TABLE 'tabela' AUTO_INCREMENT=0

Answer 3

My knowledge in SQL is not very advanced but I have learned a lot.

DECLARE @W INT = 1
DECLARE @ID INT = 1
DECLARE @Max INT = (SELECT MAX ([ID]) FROM [_Table])
WHILE @W Between 1 and @Max 
BEGIN
IF EXISTS (SELECT [ID] FROM [_Table] WHERE [ID] = @W )
BEGIN
UPDATE [_Table] SET [ID] = @ID WHERE [ID] = @W 
SET @ID +=1
SET @W +=1
END
ELSE
SET @W +=1
END

Answer 4

Relationship with ON UPDATE CASCADE relatives keys will be changed also:

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON UPDATE CASCADE
) ENGINE=INNODB;