MySQL AUTO_INCREMENT attribute explained

The auto increment mechanism is a feature in SQL type database that allows you to automatically generate a unique number as a value for your column when you perform an INSERT statement.

The mechanism can be added to your MySQL table column by adding the AUTO_INCREMENT attribute to the column definition of the CREATE TABLE statement.

In the following example, AUTO_INCREMENT attribute is added to the student_id column of the students table:

CREATE TABLE students (
  student_id MEDIUMINT NOT NULL AUTO_INCREMENT,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (student_id)
);

The AUTO_INCREMENT attribute is added next to the NOT NULL attribute in the example above.

Please note that the AUTO_INCREMENT attribute only works for integer data type. Adding the auto increment feature to columns with other data types will cause the Incorrect column specifier error as shown below:

mysql> CREATE TABLE students (
    ->      student_id CHAR(30) NOT NULL AUTO_INCREMENT,
    ->      first_name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (student_id)
    -> );
ERROR 1063 (42000): Incorrect column specifier for column 'student_id'

Each time you INSERT a new value to the students table, the student_id column value will be incremented automatically.

Let’s execute some INSERT statements as an example:

INSERT INTO students (first_name) VALUE ("Chris"); 
INSERT INTO students (first_name) VALUE ("Jack"); 
INSERT INTO students (first_name) VALUE ("Sarah"); 

Now run a SELECT statement to retrieve the records stored in students table.

The table content would be as follows:

+-------------+------------+
| student_id | first_name |
+-------------+------------+
|           1 | Chris      |
|           2 | Jack       |
|           3 | Sarah      |
+-------------+------------+

As you can see, the student_id value is automatically generated by MySQL. It starts from the number 1 and incremented by 1 each time you insert a new row.

You can also put NULL value for the auto increment column as shown below:

INSERT INTO students (student_id, first_name) VALUE (NULL, "Helen"); 

The student_id column will automatically adjust the value from NULL to the next sequential value in your table.

You can change the initial value from 1 into any other number by adding the AUTO_INCREMENT = syntax by the end of your CREATE TABLE statement as shown below:

CREATE TABLE students (
  student_id MEDIUMINT NOT NULL AUTO_INCREMENT,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (student_id)
) AUTO_INCREMENT=1000;

Or if you already have the table, you can use the ALTER TABLE statement to change the initial value:

ALTER TABLE students AUTO_INCREMENT = 1000;

Now run the INSERT statements again:

INSERT INTO students (first_name) VALUE ("Eric"); 
INSERT INTO students (first_name) VALUE ("Anna"); 
INSERT INTO students (first_name) VALUE ("Mary"); 

This time the student_id value will start from 1000 as you can see below:

+-------------+------------+
| student_id | first_name |
+-------------+------------+
|           1 | Chris      |
|           2 | Jack       |
|           3 | Sarah      |
|        1000 | Eric       |
|        1001 | Anna       |
|        1002 | Mary       |
+-------------+------------+

And that’s how you can add the AUTO_INCREMENT attribute to a MySQL column. Next, let’s see how you can increase the increment value for each new record.

Increasing AUTO_INCREMENT incremental value

The increment value of a column with AUTO_INCREMENT attribute is controlled by MySQL through the auto_increment_increment global variable.

You can find the variable in your MySQL database by executing SHOW VARIABLES query as follows:

SHOW VARIABLES LIKE 'auto_increment_increment';

By default, the auto_increment_increment value should be 1, which means your AUTO_INCREMENT column value should go up by 1 each time you perform an INSERT statement.

Here’s the result on my local server:

mysql> SHOW VARIABLES LIKE 'auto_increment_increment';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
+--------------------------+-------+
1 row in set (0.01 sec)

Let’s set the auto_increment_increment value to 10 using the SET GLOBAL VARIABLE statement as shown below:

SET GLOBAL auto_increment_increment=10;

Now each INSERT statement will cause the column with AUTO_INCREMENT attribute to leap by 10 instead of 1.

In the example below, the student_id column jumped from 1 to 11:

+-------------+------------+
| student_id | first_name |
+-------------+------------+
|           1 | Joan       |
|          11 | Eddy       |
+-------------+------------+

You’ve just learned about MySQL’s auto increment feature and how you can change the increment value by changing the auto_increment_increment variable value. Nice work! 😉