Using variable in setting mysql INNODB AUTO_INCREMENT table value

Question

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

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.

Addendum

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. I found a related question here on So, but with no answer: Updating auto_increment value in an InnoDB table

EDIT corrected missing semicolon

@wchiquito would you care to explain how to prepare an alter table statement? — luksch
– luksch, Commented Dec 14, 2015 at 12:45

wchiquito · Accepted Answer · 2015-12-14 13:09:56Z

4

Try:

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

UPDATE

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 Fiddle demo

edited Dec 14, 2015 at 13:09

answered Dec 14, 2015 at 12:51

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wchiquito Over a year ago

@Shadow:

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

. See 9.4 User-Defined Variables.

Shadow Over a year ago

Yep, got that. I was too focussed on the error messages :(

luksch Over a year ago

It is working now. And I understand why it did not before. Thank you for pointing this out to me.

Collectives™ on Stack Overflow

Using variable in setting mysql INNODB AUTO_INCREMENT table value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related