Changing the current count of an Auto Increment value in MySQL?

You can change the current count of an auto_increment in MySQL using ALTER command.

The syntax is as follows −

ALTER TABLE yourTableName AUTO_INCREMENT = IntegerValue;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table changeCurrentAutoIncrementValue
   −> (
   −> CurrentCount int auto_increment,
   −> PRIMARY KEY(CurrentCount)
   −> );
Query OK, 0 rows affected (0.60 sec)

Insert records in the table using select statement. The auto_increment by default starts from 1 and increments by 1. The query to insert record is as follows −

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.20 sec)

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.15 sec)

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.11 sec)

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.12 sec)

Display all records to check from where the value starts. The query is as follows:

mysql> select *from changeCurrentAutoIncrementValue;

The following is the output:

+--------------+
| CurrentCount |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
+--------------+
4 rows in set (0.00 sec)

Look at the above sample output, the auto_increment starts from 1 and next number has been generated from adding 1 of previous number.

Here is the query to change the current auto_increment value. The query is as follows −

mysql> alter table changeCurrentAutoIncrementValue auto_increment = 300;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

Look at the above query. We have changed the auto_increment value. Now it starts from 300. The new values will get added after the above values i.e. after 4.

Let us now again insert records in the table. The query is as follows −

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.15 sec)

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.17 sec)

mysql> insert into changeCurrentAutoIncrementValue values();
Query OK, 1 row affected (0.15 sec)

Display records from table to check. The query is as follows −

mysql> select *from changeCurrentAutoIncrementValue;

The following is the output −

+--------------+
| CurrentCount |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|          300 |
|          301 |
|          302 |
+--------------+
7 rows in set (0.00 sec)

Look at the above sample output, the value is starting from 300 after changing auto_increment value