What does MySQL return when we change an AUTO_INCRMENT value that is less than the 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 of 1 or the value we specify.

This is why MySQL does not allow changing the AUTO_INCREMENT value to a value smaller than the current sequence number. It can be understood through the following example:

Example

In this example, suppose we have a table named 'emp1' and we specify the AUTO_INCREMENT value as 100 while creating the table. So, after inserting the values ​​in the table, the sequence will start from 100 as shown in the output of the query below:

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

Now, when we try to change the AUTO_INCRMENT value to 90 with the help of ALTER TABLE query, MySQL does not Errors and warnings are returned because the query works fine, but when we insert a new value into the table, MySQL compares the specified AUTO_INCRMENT value with the current sequence number. Since the specified AUTO_INCRMENT value (90) is less than the current sequence number (101), MySQL starts accumulating new values ​​from 102, which can be observed from the following query -

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)

On the contrary, if we try to change the AUTO_INCRMENT to If the value is higher than the current sequence number, MySQL will accumulate new values ​​starting from the specified value.

To display 'emp1' more clearly in the table we change the AUTO_INCRMENT value to 108, which is higher than the current sequence number, so MySQL starts accumulating newly inserted values ​​from the specified AUTO_INCRMENT value (i.e. starting from 108) .

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)

The above is the detailed content of What does MySQL return when we change an AUTO_INCRMENT value that is less than the current sequence number?. For more information, please follow other related articles on the PHP Chinese website!