Change auto increment starting number?

Question

In MySQL, I have a table, and I want to set the start?auto_increment value to 5, not 1. Is this possible and what query statement does this?

Answer

You can use ALTER TABLE to change the auto_increment initial value:

ALTER TABLE tbl AUTO_INCREMENT = 5;

See the MySQL reference for more details.

However, you need to be aware that this will cause the rebuilding of your entire table, at least with InnoDB. If you have an already existing dataset with millions of rows, it could take a very long time to complete.

In my experience, it's better to do the following:

BEGIN WORK;
-- You may also need to add other mandatory columns and values
INSERT INTO tbl(id) VALUES(42);
ROLLBACK;

In this way, even if you're rolling back the transaction, MySQL will keep the auto-increment value, and the change will be applied instantly.

You can verify this by issuing a SHOW CREATE TABLE tbl statement. You should see:

mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: tbl
Create Table: CREATE TABLE `tbl` (
...
) ENGINE=InnoDB AUTO_INCREMENT=43 ...