更新时间:2023-09-06 GMT+08:00
修改表的自增AUTO_INCREMENT值
AUTO_INCREMENT修改时,遵循如下约束限制:
- 当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 | +---------+-------------------------------------------------------+
- 当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)
- 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
父主题: 基本使用类