Reset AUTO INCREMENT and organize item ID

Question

Reset AUTO INCREMENT and organize item ID

Asked 6 years, 7 months ago

Viewed 2,571 times

0

What command can I use to rearrange the ID of all items in a table?

The table name is produtos and the column is id.

Currently they are this way:

id: 1 - 3 - 7 - 8 - 9 - 11...

That is, they are not in correct sequence.

I need to leave them that way:

id: 1 - 2 - 3 - 4 - 5 - 6 - 7...

However it is important that it follows the correct sequence not to mix, e.g.:

1 = 1
3 = 2
7 = 3
8 = 4
9 = 5
11 = 6

The server is Mysql.

@Guillhermecostamilam MySQL is a database server.

– Roberto de Campos

2018/04/19 at 22:33
@Wendler if I’m not mistaken, this will only be possible if you truncar the table, but when you perform an exclusion, the MySQL will not reorder, even because the id cannot be changed since it can be referenced in other tables.

– Roberto de Campos

2018/04/19 at 22:35
@Robertodecampos... Yes, it would have to be through a UPDATE following some conditions, or is it not possible that way?

– Wendler

2018/04/19 at 22:37
@Sveen... Excellent friend, had not seen this question there, really solved my problem.

– Wendler

2018/04/19 at 23:21
I had also researched it these days hahah

– Sveen

2018/04/19 at 23:22

1 answer

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.

by rLinhares • **7,797** points · Answer 1 · 2018-04-20T13:16:37+00:00

To regularize your database, you need to update the data already registered, as well suggested @Sveen.

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

Done this, you need to upgrade your "accountant" so that the new inserted items follow from the last index.

ALTER TABLE tablename AUTO_INCREMENT = (SELECT MAX(id) FROM tabela) + 1

Detail: if there is the possibility of physical deletion (delete the records from the base), I suggest you do not use the auto_increment, rather a numerical column that can be controlled more easily via code.