Set AUTO_INCREMENT value through variable in MySql

Question

I have a table with an auto_increment column. I save the last value of the column in another table called ids_tbl, and when mysql restarts, read the value from ids_tbl and re-set the AUTO_INCREMENT value. If I do this:

alter table outgoing_tbl auto_increment=500;

it works

But if I do this

select @max_id:= max_id FROM ids_tbl;
alter table outgoing_tbl auto_increment=@max_id;

or if I do this:

select @max_id:= max_id FROM ids_tbl;
alter table outgoing_tbl auto_increment=(select @max_id);

Then it does not work, how do I set the auto increment value throgh a variable?

One option is to use a [13.5 Prepared SQL Statement Syntax](https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html). — wchiquito, Oct 07 '17 at 17:01
FWIW, the need for this is going away in MySQL 8.0. See http://lefred.be/content/bye-bye-bug-199/ — Bill Karwin, Oct 07 '17 at 18:02

score 27 · Accepted Answer · edited Feb 27 '23 at 17:02

27

Use the below code. This is working fine. And follow the MySQL prepared statement https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

SET @max_id = (SELECT MAX(id) + 1 FROM `ids_tbl` );
SET @sql = CONCAT('ALTER TABLE `outgoing_tbl` AUTO_INCREMENT = ', @max_id);
PREPARE st FROM @sql;
EXECUTE st;

edited Feb 27 '23 at 17:02

NobleUplift

5,631
8
45
87

answered Oct 07 '17 at 17:26

sheraz

434
4
8

Thank you. That was great. Thank you @Bill Karwin, I will think about an upgrade too. – Sinai Oct 08 '17 at 06:06
9

Except you might want to increment the @max_id by 1: `SELECT MAX(id) + 1 INTO @max_id FROM ids_tbl;` – michelek Mar 29 '18 at 11:49
Thank you for this great solution – jeewiya Oct 19 '20 at 08:16

Set AUTO_INCREMENT value through variable in MySql

1 Answers1

Linked