MySQL - AUTO INCREMENT

The AUTO INCREMENT feature allows to create a unique numerical value for each additional record inserted into a table. Generally, it is used to create the numerical primary key field.

MySQL AUTO_INCREMENT Keyword

In MySQL, the AUTO_INCREMENT keyword is used to perform an auto-increment feature. For example - the below mentioned query creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which auto-increment is applied on column EmpID.

CREATE TABLE Employee (
  EmpID INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

Setting or Changing the AUTO_INCREMENT Value

By default, the start value of AUTO_INCREMENT is 1, and it is increased by 1 for each new record. To change the start value of auto-increment, the below query can be used.

ALTER TABLE Employee AUTO_INCREMENT=50;

Inserting Records

While inserting a new record, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

The statement mentioned above will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.

The MySQL LAST_INSERT_ID() function can be used to find the last value assigned by the AUTO_INCREMENT field.