在设置mysql INNODB AUTO_INCREMENT表值时使用变量 [英] Using variable in setting mysql INNODB AUTO_INCREMENT table value

问题描述

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

I try to (re)set the auto_increment value to the value of a variable, but it fails:

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;

最后一条命令失败，并显示以下信息:

The last command fails with:

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值?有哪些选择?我需要在旋转日志表的存储过程中设置此值，并且我需要新表以从某些旧表中获取的值开始.

Is it not possible to set the AUTO_INCREMENT value like this? What are the alternatives? I need to set this value in a stored procedure that rotates log tables and I need the new table to start with values taken from some old table.

附录

使用正确的自动增量初始值直接创建表也将失败:

The direct creation of the table with the correct auto increment intial value fails as well:

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

P.S.我在So上找到了一个相关问题，但没有答案:更新auto_increment InnoDB表中的值

P.S. I found a related question here on So, but with no answer: Updating auto_increment value in an InnoDB table

编辑 更正了丢失的分号

推荐答案

尝试:

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演示

更新

您可以使用 13.5编写的SQL语法 13.1.14创建表语法的语句 >.

You can use 13.5 SQL Syntax for Prepared Statements for 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小提琴演示

这篇关于在设置mysql INNODB AUTO_INCREMENT表值时使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！