修改表的自增AUTO_INCREMENT值

AUTO_INCREMENT修改时，遵循如下约束限制：

1. 当AUTO_INCREMENT大于表中数据的最大值时，可以在取值范围内任意修改为更大的值。

   show create table animals; 
   +---------+------------------------------------------------------------------+ 
   | Table   | Create Table                                                     | 
   +---------+------------------------------------------------------------------+ 
   | animals | CREATE TABLE `animals` (   
   `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8   | 
   +---------+------------------------------------------------------------------+ 
   1 row in set (0.00 sec)  
   mysql> select * from animals; 
   +-----+-----------+ 
   | id  | name      | 
   +-----+-----------+ 
   | -50 | -middle   | 
   |   1 | dog       | 
   |   2 | cat       | 
   |  50 | middle    | 
   | 100 | rabbit    | 
   +-----+-----------+ 
   11 rows in set (0.00 sec)  
   alter table animals AUTO_INCREMENT=200; 
   Query OK, 0 rows affected (0.22 sec) 
   Records: 0  Duplicates: 0  Warnings: 0  
    show create table animals; 
   +---------+-------------------------------------------------------+ 
   | Table   | Create Table                                          | 
   +---------+-------------------------------------------------------+ 
   | animals | CREATE TABLE `animals` (   
   `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8 | 
   +---------+-------------------------------------------------------+

2. 当AUTO_INCREMENT大于表中数据的最大值时，如果修改后的指定值仍大于数据的最大值，则修改为指定值成功。否则，默认会修改为数据最大值+1。

   mysql> select * from animals; 
   +-----+-----------+ 
   | id  | name      | 
   +-----+-----------+ 
   | -50 | -middle   | 
   |   1 | dog       | 
   |   2 | cat       | 
   |  50 | middle    | 
   | 100 | rabbit    | 
   +-----+-----------+ 
   mysql> show create table animals; 
   +---------+-----------------------------------------------------+ 
   | Table   | Create Table                                        | 
   +---------+-----------------------------------------------------+ 
   | animals | CREATE TABLE `animals` (   
   `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8 | 
   +---------+-----------------------------------------------------+ 
   mysql> alter table animals AUTO_INCREMENT=150; 
   Query OK, 0 rows affected (0.05 sec) 
   Records: 0  Duplicates: 0  Warnings: 0 
   mysql> show create table animals; 
   +---------+-----------------------------------------------------+ 
   | Table   | Create Table                                         | 
   +---------+-----------------------------------------------------+ 
   | animals | CREATE TABLE `animals` (   
   `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 | 
   +---------+-----------------------------------------------------+ 
   mysql> alter table animals AUTO_INCREMENT=50; 
   Query OK, 0 rows affected (0.04 sec) 
   Records: 0  Duplicates: 0  Warnings: 0 
   mysql> show create table animals; 
   +---------+-----------------------------------------------------+ 
   | Table   | Create Table                                        | 
   +---------+-----------------------------------------------------+ 
   | animals | CREATE TABLE `animals` (   
   `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 | 
   +---------+-----------------------------------------------------+ 
   mysql> delete from animals where id=100; 
   Query OK, 1 row affected (0.00 sec)  
   mysql> select * from animals; 
   +-----+-----------+ 
   | id  | name      | 
   +-----+-----------+ 
   | -50 | -middle   | 
   |   1 | dog       | 
   |   2 | cat       | 
   |  50 | middle    | 
   +-----+-----------+ 
   10 rows in set (0.00 sec)  
   mysql> alter table animals AUTO_INCREMENT=50; 
   Query OK, 0 rows affected (0.04 sec) 
   Records: 0  Duplicates: 0  Warnings: 0  
   mysql> show create table animals; 
   +---------+-----------------------------------------------------+ 
   | Table   | Create Table                                        | 
   +---------+-----------------------------------------------------+ 
   | animals | CREATE TABLE `animals` (   
   `id` mediumint NOT NULL AUTO_INCREMENT,   `name` char(30) NOT NULL,   
   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 | 
   +---------+-----------------------------------------------------+ 
   1 row in set (0.00 sec)

3. AUTO_INCREMENT无法修改为负数。

   alter table animals 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