MySQL AUTO INCREMENT
What is an AUTO INCREMENT Field?
When a new record is added to a table, auto-increment enables the creation of a unique integer.
This is frequently the main key field that we want to be generated automatically each time a new record is added.
MySQL AUTO_INCREMENT Keyword
MySQL’s auto-increment capability is implemented via the AUTO_INCREMENT keyword.
AUTO_INCREMENT has a beginning value of 1 by default and increases by 1 with each new record.
The “Personid” column in the “Persons” database is defined as an auto-increment primary key field using the SQL statement that follows:
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
Use the following SQL query to change the value at which the AUTO_INCREMENT sequence begins:
ALTER TABLE Persons AUTO_INCREMENT=100;
The “Personid” column does not need to have a value specified when a new record is inserted into the “Persons” table; instead, a unique value is added automatically:
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');
A new record would be inserted into the “Persons” database with the aforementioned SQL query. An automated unique value would be assigned to the “Personid” column. “Lars” would be entered in the “FirstName” field and “Monsen” in the “LastName” column.
