Set AUTO_INCREMENT value programmatically

Question

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 )

score 2 · Answer 1 · edited Feb 19 '13 at 14:34

2

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

edited Feb 19 '13 at 14:34

partlov

13,789
6
63
82

answered Feb 19 '13 at 14:13

rpaillao

193
1
2
11

score 1 · Answer 2 · edited May 23 '17 at 12:01

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

Good idea but seems mysql doesn't like it mysql> PREPARE stmt FROM 'ALTER TABLE variation AUTO_INCREMENT = ?'; 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 '?' at line 1 — Tim, Apr 30 '10 at 00:50

alex · Answer 3 · 2010-04-30T00:16:08.713

0

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 :)

edited Apr 30 '10 at 00:16

answered Apr 29 '10 at 23:55

alex

479,566
201
878
984

Yep was hoping to do this all in SQL but its looking like I will need to do something like the above instead. – Tim Apr 30 '10 at 00:53

score 0 · Answer 4 · answered Dec 06 '18 at 09:59

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 ;

Set AUTO_INCREMENT value programmatically

4 Answers4

Linked