Mysql中实现修改主键自增值

admin Mysql

2023-12-01 0 702

Mysql修改主键自增值

一般情况下，修改表的主键自增值直接运行下面的语句，是能成功的。

alter table table_name AUTO_INCREMENT= XX;

但是最近项目迁移数据库，发觉有一张表直接运行这语句修改自增值不生效，下面就这个问题来举例说明。

1.首先，我在本地库名叫做susunma的库下面，新建一张表：

CREATE TABLE `family` (
`uid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \’自增id\’,
`name` varchar(25) NOT NULL COMMENT \’姓名\’,
`age` tinyint(4) NOT NULL COMMENT \’年龄\’,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.插入主键值不连续的数据：

INSERT INTO `family` (`uid`, `name`, `age`)
VALUES
(1, \’Susunma\’, 28),
(2, \’Winnie\’, 25),
(3, \’Peng\’, 24),
(4, \’Jian\’, 32),
(888, \’Jack\’, 17),
(999, \’Tom\’, 16);

3.此时查询记录的最大值以及下一个自增值

select max(id) from family; #999
select AUTO_INCREMENT FROM information_schema.tables WHERE table_name=\”family\” and table_schema = \”susunma\”; #1000

我们会发现，虽然表里面只有六条数据，但是由于999记录的插入，下一个自增值变成了1000。但是实际上我们想要下一个自增值为更小一点，比如定为10，因为记录中uid为888， 999的情况，实际上只是项目的要求，为的是使得某个人的id可以特殊化，下面直接运行一下修改自增值的语句：

alter table family AUTO_INCREMENT= 10;

然后再查询一下，自增值是否修改成功：

select AUTO_INCREMENT FROM information_schema.tables WHERE table_name=\”family\” and table_schema = \”susunma\”; #1000

我们会发现，并没有修改成功，下一个自增值还是1000。所以就在想，难道是修改语句出了问题，所以就开始很纳闷，因为语句并没错。之后，在用不同表调试间，我们就发现了：

当修改后的自增值大于已有记录的最大值时，修改语句变得有效，比如，我们把例子中的自增值修改为比记录中最大值999更大的值2000, 并且查询下修改结果：

alter table family AUTO_INCREMENT=2000;
select AUTO_INCREMENT FROM information_schema.tables WHERE table_name=\”family\” and table_schema = \”susunma\”; #2000

我们会发现，自增值的确变成了2000。也就是说当修改后的自增值小于已有记录的最大值时，修改语句是无效的。

所以这里采取的解决方案的步骤如下：

1.先将大于10 的uid设置为比10小的值, 注意这里不要与已有记录的uid的值重复，因为主键必须是唯一的：

update family set uid = 9 where uid = 999 limit 1;
update family set uid = 8 where uid = 888 limit 1;

2.修改uid的自增值ID为10：

alter table family AUTO_INCREMENT=10;

3.再还原被修改uid的值：

update family set uid = 999 where uid = 9 limit 1;
update family set uid = 888 where uid = 8 limit 1;

4.最后查询下修改结果：

select AUTO_INCREMENT FROM information_schema.tables WHERE table_name=\”family\” and table_schema = \”susunma\”; #10

结果为10，这样，就完成了。