MySQL AUTO_INCREMENT: Definition, Syntax, and Examples

In MySQL, the AUTO_INCREMENT attribute is used to automatically generate unique values for a column, typically for a primary key. It automatically increments the value by 1 for each new row inserted into the table.

Key Features of AUTO_INCREMENT:

Usually applied to a column with the PRIMARY KEY or UNIQUE constraint.
Automatically starts from 1 by default.
Can be manually set to start from a specific value.
Supports only integer types (INT, BIGINT, TINYINT, etc.).

Syntax for AUTO_INCREMENT

CREATE TABLE table_name (
  column_name INT AUTO_INCREMENT,
  other_column data_type,
  PRIMARY KEY (column_name)
);

Create a Table with AUTO_INCREMENT

The following SQL statement defines the "student_id" column to be an auto-increment primary key field in the "Persons" table:

Example

CREATE TABLE students (

student_id INT AUTO_INCREMENT,

student_name VARCHAR(50) NOT NULL,

age INT,

PRIMARY KEY (student_id)

);

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

Example

ALTER TABLE students AUTO_INCREMENT = 100;

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.