更新时间：2025-04-23 gmt 08:00
修改表的自增auto-j9九游会登录
修改方法如下：
- 当auto_increment大于表中数据的最大值时，可以在取值范围内任意修改为更大的值。
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 | --------- ------------------------------------------------------------------ 1 row in set (0.00 sec) mysql> select * from animals; ----- ----------- | id | name | ----- ----------- | -50 | -middle | | 1 | fish | | 2 | cat | | 50 | middle | | 100 | rabbit | ----- ----------- 11 rows in set (0.00 sec) mysql> alter table animals auto_increment=200; query ok, 0 rows affected (0.22 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=200 default charset=utf8 | --------- -------------------------------------------------------
- 当auto_increment大于表中数据的最大值时，如果修改后的指定值仍大于数据的最大值，则修改为指定值成功。否则，默认会修改为数据最大值 1。
mysql> select * from animals; ----- ----------- | id | name | ----- ----------- | -50 | -middle | | 1 | fish | | 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 | fish | | 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无法修改为负数。
mysql> 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
相关文档
意见反馈
文档内容是否对您有帮助？
提交成功！非常感谢您的反馈，我们会继续努力做到更好！
您可在查看反馈及问题处理状态。
系统繁忙，请稍后重试
如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨
