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