In MySQL, you can create an integer column that contains auto generated sequence of integer numbers in ascending order by defining the AUTO_INCREMENT attribute on a column. It automatically generates an integer number (1, 2, 3, and so forth) when a new record is inserted into the table. A column like this is a good candidate for surrogate key column which acts as a primary key in the table.

To create the auto number in MySQL, set the AUTO_INCREMENT attribute to a column.

When using the AUTO_INCREMENT attribute, consider the following guidelines.

1. Each table can only have one AUTO_INCREMENT column.

2. The AUTO_INCREMENT column must be defined as an integer type and it must be NOT NULL.

3. The AUTO_INCREMENT column must have index created on it, which is either PRIMARY KEY index or UNIQUE index, and it cannot have a DEFAULT value.

4. When writing the INSERT statement, you don't normally need to include the name of the AUTO_INCREMENT column and the next available sequence number will be generated automatically. See Practice #1.

5. If you do include the name of the AUTO_INCREMENT column in the INSERT statement, you must specify a value for it. In this case, assigning a NULL value to it in the statement will get MySQL auto generate next available sequence number, see Practice #2.

6. There are 2 ways to find out the next available integer that will be generated for an AUTO_INCREMENT column in a particular table - use SHOW TABLE STATUS command or query information_schema, see Practice #3a.

7. To find the integer that has been generated by MySQL for an AUTO_INCREMENT column after an insert query is executed, use LAST_INSERT_ID() function, see Practice #3b.

8. If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value automatically generated for the first inserted row only, see Practice #4.

9. Normally the AUTO_INCREMENT column should contain positive integer numbers so it's a good practice to define the column as unsigned integer. It forces the column to start from a positive number (normally it is 1) rather a negative number.

10. MySQL integer type has limits. When the AUTO_INCREMENT column reaches the upper limit of the integer type, the next insert that generates a sequence number will fail. When this happens, you need to redefine the column to a bigger integer type such as INT OR BIGINT. See Practice #5.

11. When the last inserted row is deleted, MySQL will not reuse the deleted sequence number if the table type is InnoDB. See Practice #6.

12. When defining an AUTO_INCREMENT column, the starting value is normally 1 and it is incremented by 1 each time a new record is inserted. However, you can arbitrarily set the starting value to an integer that you need, see Practice #7.

13. If you want to reset auto increment number to 1 for a table that already contains records, you need to drop the table and re-create it.

14. When defining the table type for the table that contains the AUTO_INCREMENT column, use InnoDB because it's transaction safe. AUTO_INCREMENT column normally acts as primary key column and it is expected that it will be referenced by some other tables for foreign keys. So enforcing transaction safe is important.

15. You can't use the AUTO_INCREMENT column name in an expression in the insert statement because the AUTO_INCREMENT value is generated after other value assignments, any reference to an AUTO_INCREMENT column in the assignment returns a 0. See Practice #8.

16. If a table contains an AUTO_INCREMENT column, when an insert query INSERT ... ON DUPLICATE KEY UPDATE is executed, the LAST_INSERT_ID() function always returns the next auto incremented value even though only update occurs.

    That is, regardless of whether or not a row is inserted or updated, the INSERT ... ON DUPLICATE KEY UPDATE always increments the auto-increment column by 1.

    See details here in tutorial about how to update record when doing insert.

Practice #1: Insert a record without specifying the AUTO_INCREMENT column.

In Shippers table, ShipperID is the AUTO_INCREMENT column. In the query below, the next available sequence number will be generated automatically for ShipperID even though we don't specify it in the query.

INSERT INTO shippers(CompanyName, Phone)
VALUES('Trans Freight','(503) 555-1234')

After adding the new shipper, the Shippers table looks as below.

Practice #2: Insert a record and specify the AUTO_INCREMENT column.

In the query below, ShipperID is specified and a NULL value is assigned in the statement which instructs MySQL to auto generate next available sequence number.

INSERT INTO shippers(ShipperID, CompanyName, Phone)
VALUES(NULL, 'Cheap Couriers Service','(503) 555-2345')

After adding the new shipper, the Shippers table looks as below.

Practice #3a: Find out the next available integer for an AUTO_INCREMENT column.

In some cases, we need to know upfront what the next available integer is that will be generated by MySQL for an AUTO_INCREMENT column in a particular table.

Below we work out what the next integer is for AUTO_INCREMENT column ShipperID in Shippers table in Northwind database.

Method 1: SHOW TABLE STATUS command

SHOW TABLE STATUS WHERE NAME = 'shippers'

Query result set - 1 row returned:

Method 2: Query information_schema

The DATABASE() function returns the name of the default database which is the database connected currently, here the database is Northwind.

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'shippers'
AND table_schema = DATABASE()

Query result set - 1 row returned:
This is the next available integer that will be generated.

Practice #3b: Find out what integer that has already been generated - LAST_INSERT_ID() function.

LAST_INSERT_ID() function is the most often used method to find out what integer that has already been generated for an AUTO_INCREMENT column after executing an insert query. For example, after a shipper is created, we need to browse the shipper in details. In this case, the ShipperID generated is retrieved and then pass to a select query to get data for the new shipper from the Shippers table.

The LAST_INSERT_ID() function takes no argument. It returns the automatically generated value for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.

First, insert a new shipper.

INSERT INTO shippers(CompanyName, Phone)
VALUES('Generation Shipping','(503) 555-4597')

After adding the new shipper, the Shippers table looks as below.

Now straight after adding the new shipper, run the LAST_INSERT_ID function as shown below.

SELECT LAST_INSERT_ID()

The LAST_INSERT_ID() function returns 6 which is generated by MySQL for the AUTO_INCREMENT column ShipperID by the most recent INSERT statement.

Query result set - 1 row returned:
This is the auto generated integer by the most recent insert query.

Practice #4: The AUTO_INCREMENT value when multiple records inserted.

The insert statement below inserts 3 records into Shippers table. The LAST_INSERT_ID() only returns the value automatically generated for the first inserted row which is 'Couriers Buddy'.

First, insert 3 new shippers.

INSERT INTO shippers(CompanyName, Phone)
VALUES('Couriers Buddy','(503) 555-5678'),
('Shipping Mate','(503) 555-5679'),
('Delivery Man','(503) 555-5670')

After adding the 3 new shippers, the Shippers table looks as below.

Now straight after adding the new shipper, run the LAST_INSERT_ID function as shown below.

The LAST_INSERT_ID() function returns 7 which is generated for shipper 'Couriers Buddy'.

Query result set - 1 row returned:
This is the auto generated integer by the first record.

Practice #5: When AUTO_INCREMENT value hits the upper limit, the next insert which generates a sequence number fails.

Suppose we have a table called Flower and the ID column is defined as unsigned TINYINT which has a maximum value of 255. The table currently contains 255 records so it has reached its upper limit. If we insert 1 more record into the table, it'll fail.

To create the Flower table, click here to see the table creation SQL script.

The maximum value in ID column is 255. The next attempt of generating a sequence number will fail.

The following table shows the storage and range for integer types supported by MySQL.

Query 1: Add a new flower 'Sneezeweed' and the insert would fail as the ID already reached 255.

INSERT INTO flower(Name) VALUES('Sneezeweed')

Query result - the query failed when MySQL tried to insert ID 255 again because the maximum value for unsigned tinyint data type is 255.

Query 2: Now in order to add more flowers to the table, we need to change the ID column to a bigger integer type, here we choose unsigned SMALLINT which has a maximum value 65535.

ALTER TABLE flower MODIFY COLUMN ID SMALLINT unsigned NOT NULL AUTO_INCREMENT

Query 3: Now add flower 'Sneezeweed' again.

INSERT INTO flower(Name) VALUES('Sneezeweed')

After adding the new flower, the Flower table looks as below. ID 256 was inserted successfully.

Query result set - 1 row inserted:

Practice #6: MySQL does not reuse the last deleted auto number.

The last insert id in the Flower table is 256, if you remove it, MySQL will not reuse ID 256 because the table type for Flower is InnoDB.

delete from flower where Name='Sneezeweed'

Now add flower 'Sneezeweed' again. MySQL generates ID 257.

INSERT INTO flower(Name) VALUES('Sneezeweed')

Query result set - 1 row inserted:

Practice #7: Reset the next sequence number for AUTO_INCREMENT column.

Here is a practical scenario. You have a table where you need to reserve 1 ~ 10000 for the AUTO_INCREMENT column in order that these IDs can be used later for migrating legacy data. But now you need to load new records into it so the AUTO_INCREMENT column will need to start from 10001. In this case, the next ID will be set to start from 10001.

ALTER TABLE books AUTO_INCREMENT = 10001

The query above changes the next value in the AUTO_INCREMENT column (i.e. the next available value in the sequence) to 10001 in table books.

In a later stage, when importing legacy data into the table, you need to pre-assign ID 1 ~ 10000 to the legacy data before loading them into table books.

Practice #8: Use AUTO_INCREMENT value in expression

Because the AUTO_INCREMENT value is generated after other value assignments, any reference to an AUTO_INCREMENT column in the query returns a 0.

As shown below, we tried to insert ID to the Name column but it inserted 0.

INSERT INTO flower(Name) VALUES(ID)

Query result set - 1 row inserted:

Happy coding!

Other tutorials in this category

1. Update Statement in MySQL

2. How to update data in a specific order in MySQL

3. How to update top N rows of data in MySQL

4. Use outer join in update

5. How to do cross-table update in MySQL - Part 1

6. How to do cross-table update in MySQL - Part 2

7. Using Bulk Insert Statement

8. Basic insert statement

9. Combine update and insert into one statement

10. Using MySQL REPLACE (INTO) to mimic DELETE + INSERT

11. Update multiple rows (distinctive by primary key) with different values in one query

12. Update multiple rows (distinctive by primary key) for multiple columns in one query

Back to Tutorial Index Page