how to reset value of auto incremented id to a specific value in mysql?

Question

i am migrating data from one db to another. A table is getting populated from combination of 4 tables. I am using stored procedures to populate new table. When i run it first time with some condition(where clause) it populates the table but when i run it second time with another condition, it skips few values in Id(auto incremented).

Answer 1

No need to ALTER table its quite overkill. I use to do a fake insert in rollbacked transaction to do the trick.

CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT);

No rows on the table:

MariaDB [mfo]> SELECT COUNT(id) FROM test;
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.01 sec)

Open a transaction and set your desired value, finally rollback it to not insert the line.

MariaDB [mfo]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mfo]> INSERT INTO test VALUES (300);
Query OK, 1 row affected (0.00 sec)

MariaDB [mfo]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

The AUTO_INCREMENT was change by that transaction so the next value will be 301:

MariaDB [mfo]> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So we change the auto_increment value without ALTER the table, note that as the transaction was rollbacked there is still 0 line in my table:

MariaDB [mfo]> SELECT COUNT(id) FROM test;
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.01 sec)

Last note, we force the auto_increment value to 300 in our transaction so the 300 is no more available, the next one will be 301 so don't forget to force it with your_value - 1.

Max.

Answer 2

The following should do what you want where x is a value greater than the maximum value that appears in the column

ALTER TABLE table_name AUTO_INCREMENT = x

The cause of the skipped values is probably either failed inserts (where the insert fails a unique key check or similar) or insert on duplicate update queries both of which increment the auto_increment without creating a row.

If this is a problem for your migration you may be better off creating two copies of your new table table_name and table_name_temp. Use your stored procedures to populate table_name_temp ignoring the gaps in the auto increment. Once table_name_temp is fully populated you can populate table_name with an insert select query from the table_name_temp of the table.

INSERT INTO table_name SELECT <all columns except the auto increment> FROM table_name_temp;

This will create a table with a continuous auto_increment