How do I begin auto increment from a specific point in MySQL?

To begin auto increment from a specific point, use ALTER command. The syntax is as follows −

ALTER TABLE yourTableName auto_increment = anySpecificPoint;

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

mysql> create table AutoIncrementSpecificPoint −> ( −> BookId int auto_increment not null, −> Primary key(BookId) −> ); Query OK, 0 rows affected (0.56 sec)

Now you can insert records using insert command.

The query is as follows −

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

The above insert command begins the value from 1 and adds 1 to the next value. Now you can check all records from table using select statement.

The query is as follows −

mysql> select *from AutoIncrementSpecificPoint;

The following is the output −

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

Look at the above sample output, the auto_increment starts from 1.

Now to change the auto_increment to begin from a specific point, you can use ALTER command. The query is as follows −

mysql> alter table AutoIncrementSpecificPoint auto_increment = 100; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0

In the above query I have set the auto increment to 100. Let us now insert records once again in the table using insert command. The query is as follows −

mysql> insert into AutoIncrementSpecificPoint values(); Query OK, 1 row affected (0.25 sec) mysql> insert into AutoIncrementSpecificPoint values(); Query OK, 1 row affected (0.18 sec) mysql> insert into AutoIncrementSpecificPoint values(); Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from AutoIncrementSpecificPoint;

The following is the output displaying the other values set for auto increment i.e. beginning from 100 −

+--------+ | BookId | +--------+ | 1 | | 2 | | 3 | | 4 | | 100 | | 101 | | 102 | +--------+ 7 rows in set (0.00 sec)