对AUTO_INCREMENT的值进行修改
修改方式如下：
1.如果AUTO_INCREMENT的值大于数据库的最大值的时候，可以在这个范围内取任何一个值修改为更大的值。
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.如果AUTO_INCREMENT的值大于表中的值的时候，修好指定的值大于数据最大值，修改会成功，否则会默认最大值+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.如果AUTO_INCREMENT的值修改为负数是无法修改的。
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