CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 4661

Set AUTO_INCREMENT value programmatically

So this works...

ALTER TABLE variation AUTO_INCREMENT = 10;

But I want to do this;

ALTER TABLE variation AUTO_INCREMENT = (SELECT MAX(id)+1 FROM old_db.varaition);

but that doesnt work, and neither does;

SELECT MAX(id)+1 INTO @old_auto_inc FROM old_db.variation 
ALTER TABLE variation AUTO_INCREMENT = @old_auto_inc;

So does anyone know how to do this?

( I'm trying to ensure that AUTO_INCREMENT keys dont collide between an old and a new site and need to do this automatically. So I can just run a script when the new db goes live )

Upvotes: 4

Views: 2290

Answers (4)

Nae
Nae

Reputation: 15335

You can dynamically inject static value to a dynamic SQL call as in:

SET @minEmptyId := 1337;
CALL statement(CONCAT('
  ALTER TABLE tableName
  AUTO_INCREMENT = ', @minEmptyId))
;

statement procedure implementation:

DELIMITER $$
CREATE PROCEDURE statement(IN dynamic_statement TEXT)
BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END$$
DELIMITER ;

Upvotes: 0

rpaillao
rpaillao

Reputation: 203

Set your auto_increment in 1, zero doesn't work, automatically mysql set the maximum value to the next value for index value.

ALTER TABLE table AUTO_INCREMENT = 1

Upvotes: 2

Therealstubot
Therealstubot

Reputation: 757

I'm not familiar enough with mysql to give a specific answer. However, in other database engines, there's an EXEC method you can pass a string into that will be executed. You simply write a script that determines the value you want for the auto_increment, then insert that value as a string into the script that is EXEC'd. Basically writing a script that writes a second script and runs it.

EDIT: Looks like you want a prepared statement. Search for 'Dynamic SQL' There's an almost duplicate here

EDIT2: Tim, ref this link that is referred to in the almost duplicate StackOverflow post previously given. Search for the string 'Using Parameters' on the page, and you'll get the skinny on that. MySql makes this a little difficult apparently. In MSSqlServer 2000, this was a trivial process. Here is another link to an article about mysql dynamic sql

Upvotes: 1

alex
alex

Reputation: 490183

I don't know if this is a good idea, but could you do it with 2 queries in a server side language, for example, PHP?

$incrementStep = Db::query('SELECT MAX(id)+1 FROM old_db.varaition');

Db::query('ALTER TABLE variation AUTO_INCREMENT = ' . (int) $incrementStep);

Assuming that Db::query is a magic query method that returns exactly what you want, every time :)

Upvotes: 0

Related Questions