更新InnoDB表中的auto_increment值 [英] Updating auto_increment value in an InnoDB table

问题描述

简短版本:我可以以编程方式更新表上的auto_increment值吗?我正在尝试通过mysql init_file来执行此操作，因此它会在启动时发生，但我看不到它起作用.

Short version: Can I programatically update the auto_increment value on a table? I'm trying to do this via the mysql init_file so it happens on startup, but I don't see it working.

USE theDb;

SELECT max(maxid) FROM (SELECT max(RegistrationId)+1 maxid FROM Registration
UNION
SELECT max(RegistrationId)+1 maxid FROM RegistrationArchive) t into @maxId;

ALTER TABLE Registration AUTO_INCREMENT=@maxId;

更长的版本: 我有一个带有InnoDB表的mysql数据库.一个表(包含注册信息)具有一个自动递增列，当处理一行时，它将被复制到第二个存档表中，并从第一个存档表中删除.存档表没有自动递增列. (顺便说一句，不是我的设计...)

Longer version: I have a mysql database with InnoDB tables. One table (holding registration info) has an auto increment column and when a row is processed, it is copied to a second archive table and is deleted from the first. The archive table does not have an auto increment column. (btw, not my design...)

问题是，由于某种原因(很少见)重启数据库时，第一个表将重新计算下一个增量值-InnoDB的功能.该表通常为空或非常小，并且计算出的下一个增量将对应于已使用且在存档表中的ID.数据被移到了可以的存档位置，但之后的后续处理将无法正常工作.

Problem is that when the database is restarted for some reason, which is infrequent, the first table recalulates the next increment value -- a feature of InnoDB. The table will often be empty or very small and the calculated next increment will correspond to an id that has already been used and is in the archive table. The data gets moved to archive ok, but subsequent processes don't work right after that.

推荐答案

我知道这已经晚了，但是在询问了

I know this is a late, but after asking this related question, I found out that certain parts of an sql statement need to be literals and can't be replaced by user_defined_variables. If you need to change such parts of an SQL statement you need to use prepared statements.

因此，您需要执行以下操作:

So you need to do something like this:

SET @`stmt_alter` := CONCAT('ALTER TABLE `Registration` AUTO_INCREMENT = ', @`maxId`);
PREPARE `stmt` FROM @`stmt_alter`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

这篇关于更新InnoDB表中的auto_increment值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！