MySQL: What is 'AUTO_INCREMENT=5' in a create table query?

The AUTO_INCREMENT=5 in a create table query tells that the first record will start from 5 i.e. not default 1. As we know if you do not set the value to AUTO_INCREMENT then MySQL starts from 1 by default.

The syntax is as follows:

CREATE TABLE yourTableName ( yourColumnName1 dataType NOT NULL AUTO_INCRMENT, . . . N, PRIMARY KEY(yourColumnName1 ) )AUTO_INCREMENT=5;

To understand the above syntax, let us create a table.

Case1 − The table starts auto increment from 1 because it is the default standard.

The query to create a table is as follows:

mysql> create table defaultAutoIncrementDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (2.19 sec)

Now you can insert some records in the table using insert command. The query is as follows:

mysql> insert into defaultAutoIncrementDemo(Name) values('John'); Query OK, 1 row affected (0.23 sec) mysql> insert into defaultAutoIncrementDemo(Name) values('James'); Query OK, 1 row affected (0.14 sec) mysql> insert into defaultAutoIncrementDemo(Name) values('Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into defaultAutoIncrementDemo(Name) values('Mike'); Query OK, 1 row affected (0.13 sec)

Now you can display all records and check the row will start from 1. The query is as follows:

mysql> select *from defaultAutoIncrementDemo;

The following is the output:

+----+--------+ | Id | Name   | +----+--------+ |  1 | John   | |  2 | James  | |  3 | Robert | |  4 | Mike   | +----+--------+ 4 rows in set (0.00 sec)

Case2 − Here is the query to start the auto_increment from 5. The query to create a table is as follows:

mysql> create table AutoIncrementStartsFrom5 -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> PRIMARY KEY(Id) -> )AUTO_INCREMENT=5; Query OK, 0 rows affected (1.00 sec)

Now insert some records in the table using insert command. The query is as follows:

mysql> insert into AutoIncrementStartsFrom5(Name) values('Larry'); Query OK, 1 row affected (0.15 sec) mysql> insert into AutoIncrementStartsFrom5(Name) values('David'); Query OK, 1 row affected (0.20 sec) mysql> insert into AutoIncrementStartsFrom5(Name) values('Bob'); Query OK, 1 row affected (0.13 sec) mysql> insert into AutoIncrementStartsFrom5(Name) values('Ricky'); 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 AutoIncrementStartsFrom5;

The following is the output:

+----+-------+ | Id | Name | +----+-------+ | 5 | Larry | | 6 | David | | 7 | Bob   | | 8 | Ricky | +----+-------+ 4 rows in set (0.00 sec)

Look at the sample output, the auto_increment starts from 5.