MySQL AUTO INCREMENT

What is an AUTO INCREMENT Field?

Auto-increment enables the automatic generation of a unique number whenever a new record is inserted into a table.

Typically, this feature is used for creating primary key fields that are generated automatically with each new record insertion.

MySQL AUTO_INCREMENT Keyword

MySQL utilizes the AUTO_INCREMENT keyword to implement auto-increment functionality.

By default, AUTO_INCREMENT starts with a value of 1 and increments by 1 for each new record.

The following SQL statement specifies that the “Personid” column is an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

To set the starting value of the AUTO_INCREMENT sequence to another number, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

When inserting a new record into the “Persons” table, there’s no need to specify a value for the “Personid” column, as a unique value will be automatically added.

INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,‘Monsen’);

The SQL statement above inserts a new record into the “Persons” table. The “Personid” column is automatically assigned a unique value. The “FirstName” column is set to “Lars”, and the “LastName” column is set to “Monsen”