Change auto increment starting number?

Question

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

Answer 1

Yes, you can use the ALTER TABLE t AUTO_INCREMENT = 42 statement. However, you need to be aware that this will cause the rebuilding of your entire table, at least with InnoDB and certain MySQL versions. 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 t (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 t statement. You should see:

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