mysql - 在设置 mysql INNODB AUTO_INCREMENT 表值时使用变量

Question

我尝试(重新)将 auto_increment 值设置为变量的值，但失败了:

CREATE TABLE test_table (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1;
SET @tmpvar = 12345;
ALTER TABLE test_table AUTO_INCREMENT=@tmpvar;

最后一条命令失败:

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 '@tmpvar' at line 1

这样设置AUTO_INCREMENT值不可以吗？有哪些选择？我需要在旋转日志表的存储过程中设置此值，并且我需要新表以从某个旧表中获取的值开始。

附录

直接创建具有正确自动增量初始值的表也失败了:

CREATE TABLE test_table (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=@tmpvar;

附言我在 So 上发现了一个相关问题，但没有答案:Updating auto_increment value in an InnoDB table

编辑更正了丢失的分号

Answer 1

尝试:

CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1;

SET @`tmpvar` := 12345;

SET @`stmt_alter` := CONCAT('ALTER TABLE `test_table` AUTO_INCREMENT = ', @`tmpvar`);

PREPARE `stmt` FROM @`stmt_alter`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

SQL Fiddle demo

更新

您可以使用 13.5 SQL Syntax for Prepared Statements对于 13.1.14 CREATE TABLE Syntax .

SET @`tmpvar` := 12345;

SET @`stmt_create` := CONCAT('CREATE TABLE `test_table` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=', @`tmpvar`);

PREPARE `stmt` FROM @`stmt_create`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

SQL Fiddle demo