基本使用类 修改表的自增AUTOINCREMENT值 AUTOINCREMENT修改时,遵循如下约束限制: 1. 当AUTOINCREMENT大于表中数据的最大值时,可以在取值范围内任意修改为更大的值。 set session autoincrementoffset10; set session autoincrementincrement2; show variables like 'autoinc%'; ++ Variablename Value ++ autoincrementincrement 2 autoincrementoffset 10 ++ create table autotest2(id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id)); Query OK, 0 rows affected (0.08 sec) show create table autotest2; CREATE TABLE autotest2 ( id int NOT NULL AUTOINCREMENT, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.01 sec) insert into autotest2 values(0), (0), (0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 select from autotest2; + id + 2 4 6 + 3 rows in set (0.01 sec) 2. 当AUTOINCREMENT大于表中数据的最大值时,如果修改后的指定值仍大于数据的最大值,则修改为指定值成功。否则,默认会修改为数据最大值+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 AUTOINCREMENT, name char(30) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT200 DEFAULT CHARSETutf8 ++ mysql> alter table animals AUTOINCREMENT150; 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 AUTOINCREMENT, name char(30) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT150 DEFAULT CHARSETutf8 ++ mysql> alter table animals AUTOINCREMENT50; 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 AUTOINCREMENT, name char(30) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT101 DEFAULT CHARSETutf8 ++ mysql> delete from animals where id100; 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 AUTOINCREMENT50; 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 AUTOINCREMENT, name char(30) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTOINCREMENT51 DEFAULT CHARSETutf8 ++ 1 row in set (0.00 sec) 3. AUTOINCREMENT无法修改为负数。 alter table animals AUTOINCREMENT1; 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