MySQL AUTO_INCREMENT attribute explained

Let's learn about MySQL auto increment feature and how to add it to your table's column

Posted on September 26, 2021


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! 😉

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.