Set AUTO_INCREMENT value programmatically

Question

So it works ...

ALTER TABLE variation AUTO_INCREMENT = 10; 

But I want to do it;

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

but this does not work, and does not:

 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 the AUTO_INCREMENT keys do not collide between the old and the new site and should do this automatically. Therefore, I can just run the script when the new db goes live)

Answer 1

Set auto_increment to 1, zero does not work, automatically mysql sets the maximum value for the next value for the index value.

 ALTER TABLE table AUTO_INCREMENT = 1 

Answer 2

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

EDIT: Looks like you want to prepare an expression. The search for "Dynamic SQL" is almost duplicated here.

EDIT2: Tim, the link is this link that is mentioned in the nearly duplicated StackOverflow post previously given. Find the line "Using Options" on the page and you will get skinny on that. MySql seems to make this a little harder. In MSSqlServer 2000, this was a trivial process. Here is another link to mysql dynamic sql article

Answer 3

I don’t know if this is a good idea, but can you do this with two server-side language queries like PHP?

 $incrementStep = Db::query('SELECT MAX(id)+1 FROM old_db.varaition'); Db::query('ALTER TABLE variation AUTO_INCREMENT = ' . (int) $incrementStep); 

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

Answer 4

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

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

---

statement procedure execution:

 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 ;