Auto-increment allows a unique number to be generated automatically whenever a new record is inserted into a table. This feature is especially useful in the primary key field so that the key can be set automatically every time a new record is inserted. Although auto incrementing can be as simple as setting and forgetting it, there are times where you may want to manage the AUTO_INCREMENT column to set the start number or perhaps skip certain values. In this article, you’ll learn how to use AUTO_INCREMENT columns in MySQL, as well as explore a few unusual use cases.
Declaring an AUTO_INCREMENT Column Type
To make a column that auto increments, simply assign the AUTO_INCREMENT attribute to it. Typically, the AUTO_INCREMENT field is a type of INTEGER and is declared as the primary key column of the table:
CREATE TABLE widgets (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
description VARCHAR(255) NULL,
PRIMARY KEY (id)
);
You should use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you anticipate accommodating. Should the column reach the upper limit of the data type, the next attempt to generate a sequence number will fail. You can always include the UNSIGNED attribute to allow a greater range. For example, if you use TINYINT, the maximum permissible sequence number is a mere 127. For TINYINT UNSIGNED, the maximum grows to 255. Integer Types include INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, and BIGINT.
The AUTO_INCREMENT column will increase by one every time a new row in inserted, so you don’t have to include it in your INSERT statements:
INSERT INTO widgets (name, description) VALUES
('widget 1', 'The first widget'),
('widget 2', 'The second widget'),
('widget 3', 'The third widget');
Selecting the contents of the widgets table will return three rows with id values of 1, 2, and 3 respectively:
< SELECT * FROM widgets;
+----+----------+-------------------+
| id | name | description |
+----+----------+-------------------+
| 1 | widget 1 | The first widget |
| 2 | widget 2 | The second widget |
| 3 | widget 3 | The third widget |
+----+----------+-------------------+
Setting the AUTO_INCREMENT Column Value
By default, the AUTO_INCREMENT column begins at 1. You can also explicitly assign 0 to the column to generate the next sequence number unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. Assigning NULL to the column will also generate the next sequence number, as long as the column was declared NOT NULL. Hence, all of the following statements are equivalent:
INSERT INTO widgets (id, name, description) VALUES('another widget', NULL);
INSERT INTO widgets (id, name, description) VALUES(0,'another widget', NULL);
INSERT INTO widgets (id, name, description) VALUES(NULL,'another widget', NULL);
Note: starting at 0 is not recommended because it can lead to problems. For instance, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters 0 values, resulting in a table with IDs which are different from the ones that were dumped.
When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the last (largest) column value, i.e.:
INSERT INTO widgets (id, name, description) VALUES(50, 'another widget', 'another descriptionription');
-- next row increments to 51...
INSERT INTO widgets (id, name, description) VALUES('another widget', 'another descriptionription');
Resetting the AUTO_INCREMENT Value
There are a couple of ways to reset an AUTO_INCREMENT value:
- You can reset it by using the ALTER TABLE statement. The syntax of the ALTER TABLE statement to reset the auto increment value is as follows:
ALTER TABLE table_name AUTO_INCREMENT = value;
You specify the table name after the ALTER TABLE clause and the value which you want to reset to in the AUTO_INCREMENT=value expression. Note that the value must be greater than or equal to the current maximum value of the auto-increment column:
ALTER TABLE widgets AUTO_INCREMENT = 5;
If you insert a new row, MySQL will assign a value of 6 to the id column of the new row.
- The TRUNCATE TABLE statement may be employed to reset the auto-increment value to zero:
TRUNCATE TABLE widgets;
Just bear in mind that this statement deletes all data from the table permanently as well, restoring your table to an empty state!
Determining the Last AUTO_INCREMENT Column Value
You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() return the AUTO_INCREMENT key from the first of the inserted rows. This is to enable multiple-row inserts to be reproduced correctly on other servers in a replication setup.
MyISAM Tables and Multiple-column Indexes
For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multi-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated using the formula: MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups:
CREATE TABLE widgets (
grp ENUM('Type 1','Type 2','Type 3') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
description VARCHAR(255) NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO widgets (grp, name) VALUES
('Type 1','widget 1'),
('Type 1','widget 2'),
('Type 2','widget 3'),
('Type 2','widget 4'),
('Type 1','widget 5'),
('Type 3','widget 6');
Here’s what’s in the widgets table:
SELECT * FROM widgets ORDER BY grp, id;
+--------+----+----------+
| grp | id | name |
+--------+----+----------+
| Type 1 | 1 | widget 1 |
| Type 1 | 1 | widget 2 |
| Type 2 | 2 | widget 3 |
| Type 3 | 3 | widget 6 |
| Type 1 | 1 | widget 5 |
| Type 2 | 2 | widget 4 |
+--------+----+----------+
Conclusion
MySQL AUTO_INCREMENT columns provide an easy way to add a numeric primary key to a table that you can set and forget. Moreover, if you employ the MyISAM engine, you can even use them as part of a multi-column index.
