MySQL AUTO_INCREMENT with Examples

Let us understand how ATUO_INCREMENT works −

The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. Let us see how this statement works. Before that, consider the below query −

Query

CREATE TABLE tableName (
   id MEDIUMINT NOT NULL AUTO_INCREMENT,
   name CHAR(30) NOT NULL,
   PRIMARY KEY (id)
);
INSERT INTO tableName (name) VALUES
(‘val1’),('val2'),('val3'),
('val4');

SELECT * FROM tableName;

Output

+----+---------+
| id | name    |
+----+---------+
| 1  | val1    |
| 2  | val2    |
| 3  | val3    |
| 4  | val4    |
+----+---------+

In the above query, no value was specified for the ‘AUTO_INCREMENT’ column, hence MySQL assigned a sequence of numbers automatically to the ‘id’ column. A value of 0 can also be explicitly assigned so that the number sequence begins from 0. This can be done only if ‘NO AUTO VALUE ON ZERO’ SQL mode is not enabled.

• If a column is declared as ‘NOT NULL’, it is possible to assign NULL to that column to generate a sequence of numbers.

• When any value is inserted into an AUTO_INCREMENT column, the column gets set to that value, and the sequence also gets reset so that it generates values automatically, in the sequential range from largest column value.

• An existing ‘AUTO_INCREMENT’ column can be updated that will reset the ‘AUTO_INCREMENT’ sequence as well.

• The most recent auto-generated ‘AUTO_INCREMENT; value can be retrieved using the ‘LAST_INSERT_ID()’ function in SQL or using the ‘mysql_insert_id()’ which is a C API function.

• These functions are connection-specific, which means their return values are not affected by other connections which perform the insert operations.

• The smallest integer data type for ‘AUTO_INCREMENT’ column can be used, which would be large enough to hold the maximum sequence value which is required by the user.

• When the column reaches the upper range of the data type, another attempt is made to generate a sequence of numbers. But this fails.

Hence, use UNSIGNED attribute if it is possible so as to allow a greater range of values in the column AUTO_INCREMENT values.