How to reset ID from auto incremment in InnoDB table

Question

I have InnoDB table that looks like this :

ID | DATA
----------
1  |   A
2  |   B
5  |   C
8  |   D
13 |   E

And I want to reset column ID to looks like this :

ID | DATA
----------
1  |   A
2  |   B
3  |   C
4  |   D
5  |   E

But unfortunatelly this does not work :

ALTER TABLE tableName AUTO_INCREMENT=1

Any solutions?

Thanks

Because I have very huges gaps such like 12345 and next number is 123456, and in near future I will be close bigint value. — arsen99, Apr 10 '13 at 18:18

score 2 · Answer 1 · edited May 23 '17 at 12:30

2

you could do those steps

ALTER TABLE `table` DROP `id`;
ALTER TABLE `table` AUTO_INCREMENT = 1;
ALTER TABLE `table` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

1- will drop the id column

2- set auto_increment start from 1

3- create column id in first place with incremented values.

then it will order again all your ids

this will help you also

edited May 23 '17 at 12:30

Community

1
1

answered Apr 10 '13 at 18:30

echo_Me

37,078
5
58
78

you welcome ! nice that it helped you :) , make sure to accept the answer to help others. – echo_Me Apr 10 '13 at 19:04

score 1 · Answer 2 · answered Apr 10 '13 at 18:23

The AUTO_INCREMENT value for a table determines what value MySQL will give to the next new record - it has not effect on existing data.

If you want to change data, you'll have to do that yourself in a custom program. Something like (pseudo-code):

set counter = 1;    
while fetch new record and set X to current id {
    update table set ID = counter where id = X;
    counter = counter + 1;
}

HOWEVER! - if you are using that ID in any other tables, you'll have to update the value there too. This could get very complex - you should try to avoid changing primary key values.

Finally, the largest value for an unsigned BIGINT is 18,446,744,073,709,551,615 - are you really going to hit that value in the near future?

How to reset ID from auto incremment in InnoDB table

2 Answers2