MySQL – change next auto_increment value

Piotr Elmanowski | Published: 2 Nov, 2018

Check this situation – you want to import to existing table already prepared data in csv in eg. PhpMyAdmin, and then you can see MySQL error:

#1062 – Duplicate entry ’34’ for key ‘PRIMARY’

But you take a look in table and you can see that last “id” value is eg. 30. What went wrong? Probably last 4 rows in your table were deleted earlier, so last row’s value is 30, but the last value of AUTO_INCREMENT is exactly 34. To check it you must go in PhpMyAdmin into structure of the table and at very bottom of page is table “Rows statistics” like on image below.

Check how to change value of next auto_increment in MySQL:

So the only thing you need to change now is the value of next autoindex . It is very easy! Just use syntax like below:

ALTER TABLE table_name AUTO_INCREMENT = [number];

1	ALTER TABLE table_name AUTO_INCREMENT = [number];

In my case it will be code:

ALTER TABLE my_table AUTO_INCREMENT = 30;

1	ALTER TABLE my_table AUTO_INCREMENT = 30;

You can see the change in table structure tab, in “Rows statistics” as on image below:

… and it is finished now!

Tags: data base, mysql, sql