What MySQL returns when we alter AUTO_INCREMENT value which is less than current sequence number?

When we use AUTO_INCREMENT on a MySQL column, the sequence number always increases in ascending order starting from the default value 1 or from the value we specify.

That is the reason, MySQL does not allow changing the AUTO_INCREMENT value to a value which is less than the current sequence number. It can be understood with the help of the following example −

Example

In this example suppose we have a table named ‘emp1’ and while creating the table we specify the AUTO_INCREMENT VALUE to 100. Hence after inserting the values in table, the sequence would start from 100 onwards as can be seen from the output of following query −

mysql> Select * from emp1;
+-----+---------+
| id | NAME     |
+-----+---------+
| 100 | Sohan   |
| 101 | Harshit |
+-----+---------+
2 rows in set (0.00 sec)

Now when we try to change AUTO_INCREMENT value to 90 with the help of ALTER TABLE query, MySQL returns no error and warning because the query is alright but when we insert new values in the table, MySQL compares the specified AUTO_INCREMENT value with a current sequence number. As the specified AUTO_INCREMENT value (90) is less than current sequence number (101) MySQL start accumulating new values from 102 onwards which can be observed from following queries −

mysql> ALTER TABLE EMP1 AUTO_INCREMENT = 90;
Query OK, 2 rows affected (0.31 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Insert into emp1(name) values('Aryan');
Query OK, 1 row affected (0.08 sec)

mysql> Select * from emp1;
+-----+---------+
| id  | NAME    |
+-----+---------+
| 100 | Sohan   |
| 101 | Harshit |
| 102 | Aryan   |
+-----+---------+
3 rows in set (0.00 sec)

In contrast, if we will try to change AUTO_INCREMENT to the value which is higher than the current sequence number then MySQL will start accumulating new values from the specified value.

To make it more clear in table ‘emp1’ we change AUTO_INCREMENT value to 108, higher than current sequence number, hence MySQL start accumulating the new inserted values from specified AUTO_INCREMENT value i.e. from 108 onwards.

mysql> ALTER TABLE EMP1 AUTO_INCREMENT = 108;
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Insert into emp1(name) values('Daksh');
Query OK, 1 row affected (0.04 sec)

mysql> Insert into emp1(name) values('Yashraj');
Query OK, 1 row affected (0.06 sec)

mysql> Select * from emp1;
+-----+---------+
| id  | NAME    |
+-----+---------+
| 100 | Sohan   |
| 101 | Harshit |
| 102 | Aryan   |
| 108 | Daksh   |
| 109 | Yashraj |
+-----+---------+
5 rows in set (0.00 sec)