Very often, we want to make columns as auto increment in Database. By specifying AUTO_INCREMENT to the primary key field, we can make column value as auto incrementing in MySQL.
The below code create a simple table “Employees” with a single auto increment column named as “ID”.
CREATE TABLE Employees ( ID int NOT NULL AUTO_INCREMENT, Name varchar(255) NOT NULL, Address varchar(255), City varchar(255), PRIMARY KEY (ID) );
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
If we want to start AUTO_INCREMENT column with another value, we can use the following SQL statement.
ALTER TABLE table_name AUTO_INCREMENT= [New Number];
The AUTO INCREMENT interval value is controlled by the MySQL Server variable
auto_increment_increment and applies globally. To change this to a number different from the default of 1, we can use the following command in MySQL.
SET @@auto_increment_increment = [interval number];
To insert a new record into the “Employees” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):
INSERT INTO Employees (Name,City) VALUES ('Blue','zzzzz');
The SQL statement above will insert a new record into the “Employees” table. The “ID” column would be assigned a unique value.