Assigning Unique Row Numbers with auto_increment

CREATE TABLE table (   column INT AUTO_INCREMENT,  ... );

AUTO_INCREMENT columns provide a way to automatically number rows in a table. Every time a new row is inserted into a table, a value equal to one (unless another value is specified for the AUTO_INCREMENT columns) plus the largest value stored in that AUTO_INCREMENT column will be inserted into the column. Alternately, insert a NULL into the column to get the same effect. Using a NULL lets others who read the code after you know that you explicitly want the column to auto-increment.

Examine and run the following sample code for a tangible demonstration of auto-incrementing:

CREATE TEMPORARY TABLE demo (   id INT NOT NULL AUTO_INCREMENT,      PRIMARY KEY (id) ); INSERT demo () VALUES (); SELECT id FROM demo; # id contains 1 INSERT demo (id) VALUES (NULL); SELECT id FROM demo; # id contains 1 and 2 INSERT demo (id) VALUES (4); SELECT id FROM demo; # id contains 1, 2 and 4 INSERT demo (id) VALUES (NULL); SELECT id FROM demo; # id contains 1, 2, 4      and 5 

Note that there can only be one AUTO_INCREMENT column for a table and that it must be a part of the primary key.

When you delete rows in a table that has an AUTO_INCREMENT column, you will likely have gaps in the sequence of numbers in the column. Unless you are in the situation where the value inserted into the AUTO_INCREMENT column is approaching the maximum value that can be stored in the columnmore than 2 billion for an INT columndon't worry about this. It might seem ugly, but not re-using the same number for different rows helps ensure that your data remain consistent.

If you are close to running out of space on the column, change the type of the column to one that can hold larger values before renumbering the AUTO_INCREMENT sequence.