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

Anvi Jain

Published on 16-Jan-2019 08:21:51

• Related Questions & Answers
• How to auto-increment value of tables to lower value in MySQL?
• MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
• Change the Auto Increment counter in MySQL?
• How to set initial value and auto increment in MySQL?
• Set auto increment initial value for MySQL table using ALTER command
• Passing NULL to MySQL for auto increment?
• How to change auto increment number in MySQL?
• Set custom Auto Increment with ZEROFILL in MySQL
• How to get the next auto-increment id in MySQL?
• How to change auto increment number in the beginning in MySQL?
• How to handle fragmentation of auto increment ID column in MySQL?
• Truncate a MySQL table and then set a custom value to auto increment
• How can I set my auto-increment value to begin from 1 in MySQL?
• How to make MySQL table primary key auto increment?
• Two columns as primary key with auto-increment in MySQL?