Auto-increment is used to allow a unique number when a new record inserted into a table. Generally we use Auto-increment to create value for primary key field automatically when a new record is inserted into a table.
The below statement creates a table named "user" with the column "id" that will be auto-increment primary key field:
CREATE TABLE user
id int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
The AUTO_INCREMENT keyword is used to increment value in MySQL, by default it's starting value is 1 and increments by 1 every time when a new record is inserted.
We can start Auto-increment with another value apart from 1 by using the below statement:
ALTER TABLE user AUTO_INCREMENT=10;
Suppose you want to insert a new record into "user" table then you don't need to define value of id field, it will auto-increment "id" column automatically (a unique value will be assigned to id column) as below:
INSERT INTO Persons (first_name,last_name)
id first_name last_name
1 John Simp