Auto Increment Column in MySql

We can create auto increment column in MySql which insert the value in the column automatically.

Syntax is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE AUTO_INCREMENT NOT NULL || CONSTRAINT TYPE,
COLUMN_NAME DATA_TYPE 
COLUMN_NAME DATA_TYPE,
....
);


While creating IDENTITY column, make sure you have specified number families data type like int, number, decimal etc.

Let's have an example:-

CREATE TABLE EMPLOYEE
(
EMPNO INTEGER AUT_INCREMENT PRIMARY KEY,
EMPNAME VARCHAR(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);

Read Also:-  Auto Increment Column in Oracle