Change the value as follows:
1. If the value of AUTO_INCREMENT is greater than the maximum value in the database, you can set it to a larger value within the specified range.
mysql> show create table tianyiyun; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun | CREATE TABLE `tianyiyun` ( `uid` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(10) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from tianyiyun; +-----+------+ | uid | user | +-----+------+ | -30 | k | | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | f | | 7 | g | | 8 | h | | 30 | j | | 99 | i | | 108 | z | | 109 | q | +-----+------+ 13 rows in set (0.00 sec) mysql> alter table tianyiyun AUTO_INCREMENT=200; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tianyiyun; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun | CREATE TABLE `tianyiyun` ( `uid` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(10) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
2. If the value of AUTO_INCREMENT is greater than the value in the table, set a specified value greater than the maximum value of the data. Otherwise, the value is set to the default maximum value of +1.
mysql> select * from tianyiyun; +-----+------+ | uid | user | +-----+------+ | -30 | k | | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | f | | 7 | g | | 8 | h | | 30 | j | | 99 | i | | 108 | z | | 109 | q | +-----+------+ 13 rows in set (0.00 sec) mysql> show create table tianyiyun; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun | CREATE TABLE `tianyiyun` ( `uid` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(10) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table tianyiyun AUTO_INCREMENT=150; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tianyiyun; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun | CREATE TABLE `tianyiyun` ( `uid` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(10) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table tianyiyun AUTO_INCREMENT=50; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tianyiyun; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun | CREATE TABLE `tianyiyun` ( `uid` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(10) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> delete from tianyiyun where uid=99; Query OK, 1 row affected (0.00 sec) mysql> select * from tianyiyun; +-----+------+ | uid | user | +-----+------+ | -30 | k | | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | f | | 7 | g | | 8 | h | | 30 | j | | 108 | z | | 109 | q | +-----+------+ 12 rows in set (0.00 sec) mysql> alter table tianyiyun AUTO_INCREMENT=50; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tianyiyun; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun | CREATE TABLE `tianyiyun` ( `uid` mediumint(9) NOT NULL AUTO_INCREMENT, `user` char(10) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
3. If the value of AUTO_INCREMENT is changed to a negative number, such change does not take effect.
mysql> alter table tianyiyun AUTO_INCREMENT=-1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1 |