无法更改MySQL AUTO_INCREMENT [英] Can't change MySQL AUTO_INCREMENT

问题描述

我正在将表从使用软件生成的ID更新为MySQL auto_increment ID的工作.

I am working on updating a table from using a software generated ID to a MySQL auto_increment ID.

ID字段为int(10)，数据库类型为InnoDB

The ID field is int(10) and database type is InnoDB

将ID更新为从1开始的顺序ID之后，新的最高ID为122.以前，最高ID为62029832

After updating the IDs to be sequential, starting at 1, the new highest ID is 122. Previously, the highest ID was 62029832

现在，我正在尝试更新auto_increment值，以使下一个插入值为123.

Now I am trying to update the auto_increment value so that the next insert is 123.

当前的auto_increment值为62029833.

The current auto_increment value is 62029833.

到目前为止，我已经尝试过:

So far I have tried:

ALTER TABLE tableName AUTO_INCREMENT = 123; - - 没运气.不会出错，只是不会粘住.

ALTER TABLE tableName AUTO_INCREMENT = 123; --- No luck. Doesn't error, just doesn't stick.

INSERT INTO tableName(ID)VALUES(123); 从tableName删除，WHERE ID = 123; ---仍然没有运气

INSERT INTO tableName (ID) VALUES (123); DELETE FROM tableName WHERE ID = 123; --- Still no luck

如果有其他方法，我想避免将表截断.

I would like to avoid truncating the table if there is another method.

根据我的阅读，InnoDB应该允许将值更改为123，因为当前的最大值是122，但是它的作用好像是有一个更高的值.

From what I've read, InnoDB should allow the change to 123 since the highest value is currently 122, but it's acting as though there is a higher value.

为了进行测试，我还尝试将auto_increment更改为1000、2000、122等.

Just to test, I've also tried changing the auto_increment to 1000, 2000, 122, etc. Nothing sticks.

有什么想法吗?

推荐答案

进一步研究之后，我发现了一个愚蠢的，非直觉的解决方案.

After working on it some more, I found a dumb, non-intuitive solution.

首先，从ID列中删除AUTO_INCREMENT.我检查了外键，因此必须运行:

First, remove AUTO_INCREMENT from your ID column. I had foreign key checks on so I had to run:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `warehouse`.`addresses`
    CHANGE COLUMN `aID` `aID` INT(10) UNSIGNED NOT NULL;
SET FOREIGN_KEY_CHECKS = 1;

下一步，更新AUTO_INCREMENT值:

ALTER TABLE 'warehouse'.'addresses' AUTO_INCREMENT = 123;

最后，重新添加AUTO_INCREMENT:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `warehouse`.`addresses`
    CHANGE COLUMN `aID` `aID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS = 1;

希望这可以帮助一些可怜的灵魂！

Hope this helps some poor soul!

这篇关于无法更改MySQL AUTO_INCREMENT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！