MySQL Auto Increment Columns Tutorial and Examples

In MySQL, if you need a column’s value to be an ordered sequence of integers, use an auto-incrementing column.

An auto-incrementing column is a special column in MySQL whose value can be automatically generated by the MySQL server and is a sequence of positive integers that increase in ascending order. Auto-incrementing columns can be used to generate unique identifiers for new rows in a table.

MySQL AUTO_INCREMENT Syntax

To define an auto-incrementing column, use the AUTO_INCREMENT keyword. The syntax for defining an auto-increment column is as follows:

column_name data_type AUTO_INCREMENT
  ...

Here:

The AUTO_INCREMENT keyword identifies that the column is an auto-incrementing column.
Auto-increment columns cannot use all data types, it only applies to integer or floating point types, including: TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT, DECIMAL, FLOAT, DOUBLE.
An auto-incrementing column must be a primary key or a unique key.
Auto-increment columns are enabled NOT NULL constraint by default.
There can only be one auto-incrementing column in each table.
The initial value of the auto-increment column is 1. Each time a row is inserted into the table, the value of this column is automatically incremented by 1.
Unlike generated columns, you can specify a value for an auto-incrementing column when inserting a new row.

MySQL AUTO_INCREMENT Examples

Let’s look at some practical examples to understand the usage of auto-incrementing columns.

The following statement creates a table named user with an auto-incrementing column named user_id and auto-incrementing column is the primary key.

CREATE TABLE `user` (
  `user_id` INT AUTO_INCREMENT,
  `name` VARCHAR(45),
  PRIMARY KEY (`user_id`));

Insert two rows of data into the user table:
```
INSERT INTO `user` (`name`)
VALUES ('Tim'), ('Jim');
```
Note that in this statement, we are not using the user_id column. The value for this column is automatically generated.

Now, let’s look at the rows in the user table:
```
SELECT * FROM `user`;
```
```
+---------+------+
| user_id | name |
+---------+------+
|       1 | Tim  |
|       2 | Jim  |
+---------+------+
2 rows in set (0.00 sec)
```
Here, MySQL automatically generates two consecutive values for the user_id column : 1 and 2.

Deleted the row whose user_id is 2:

DELETE FROM `user`
WHERE `user_id` = 2;

Insert a new row using the fowllowing statement:

INSERT INTO `user` (`name`) VALUES ('Jim');

Now, let’s look at the rows in the user table:

SELECT * FROM `user`;

+---------+------+
| user_id | name |
+---------+------+
|       1 | Tim  |
|       3 | Jim  |
+---------+------+
2 rows in set (0.00 sec)

Note: The user_id of new row is not 2 but 3. This is because MySQL does not reuse deleted sequence values.

Let’s specify a value to the automatic column for inserting a row.

INSERT INTO `user` (`user_id`, `name`)
VALUES (20, 'Tom');

Then, insert a new row of data:

INSERT INTO `user` (`name`) VALUES ('Lucy');

Now, let’s look at the rows in the user table:

SELECT * FROM `user`;

+---------+------+
| user_id | name |
+---------+------+
|       1 | Tim  |
|       3 | Jim  |
|      20 | Tom  |
|      21 | Lucy |
+---------+------+
4 rows in set (0.00 sec)

Note that, the user_id in the new line is 21.

How to view the value of an auto-incrementing column

To see the next value of an auto-incrementing column, there are two methods:

View the value of the auto-increment column from the table definition output by the SHOW CREATE TABLE statement.

SHOW CREATE TABLE `user`\G

*************************** 1\. row ***************************
      Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The next value of the auto-increment column in the table is 22 represented by AUTO_INCREMENT=22 in the last row.

If the next value of the auto-increment column is 1, it will not be output AUTO_INCREMENT=1.

Query the value of the auto-increment column from the information table TABLES in the INFORMATION_SCHEMA database:
```
SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'testdb'
  AND TABLE_NAME = 'user';
```
```
+----------------+
| AUTO_INCREMENT |
+----------------+
|             22 |
+----------------+
1 row in set (0.00 sec)
```
This value is not always correct. Because when the value of AUTO_INCREMENT=1 column is reset or modified, it will not be updated immediately.

Custom auto-increment initial value

The initial value of the auto-increment column is 1 by default, but we can set a custom value when creating the table. as follows:

CREATE TABLE `user` (
  `user_id` INT AUTO_INCREMENT,
  `name` VARCHAR(45),
  PRIMARY KEY (`user_id`)
) AUTO_INCREMENT = 10;

Here, we have created a table with the initial value of the auto-incrementing column specified as 10.

Modify the auto increment value of the table

If you want to reset or modify the self-increment of the table, you can use the ALTER TABLE statement to modify the table definition, as follows:

ALTER TABLE `user` AUTO_INCREMENT = 30;

Notice:

If the value set here is less than or equal to the maximum value of the auto-increment column in the table, the final value is the next value of the maximum value.

For example, in the user table, we change AUTO_INCREMENT to 1, but the value of the AUTO_INCREMENT column is still 22.
After modify the AUTO_INCREMENT value, the value in the INFORMATION_SCHEMA.TABLES is not updated immediately. You can view the current value using the SHOW CREATE TABLE statement.

Conclusion

In this article, we will learn about MySQL auto-increment columns and how to use auto-increment columns to generate unique identifiers. The following summarizes the usage and working principle of auto-increment columns:

There can only be one auto-incrementing column in a table.
The data of auto-increment columns can only be integers and floating-point numbers.
The initial value of the auto-incrementing column is 1. You can set the initial value of the auto-increment column when creating the table, or you can modify the value of the auto-increment column.
After deleting some rows, the value of the deleted auto-increment column cannot be reused.
You can use the SHOW CREATE TABLE statement to view the value of an auto-incrementing column.