How to set Auto_Increment value based on a variable?

Question

Is it possible to set the auto-increment value in MySQL based on a variable?

SELECT 1 + MAX(id) 
INTO @newAiValue
FROM Result_Import_Archive;

ALTER TABLE Result_Import
AUTO_INCREMENT = @newAiValue;

This is what I intend to do, however, MySQL complains that it is syntactically incorrect.

Whatever your intention is, this is probably a bad idea. Auto_increment values are there to identify a row uniquely. Nothing more. You should not rely on it to be sequential. Missing values can happen for multiple reasons, like deleting rows, rolling back transactions, gap locks during inserts (I believe), ... Never mess with auto_inc values. Just use them as intented. Insert in one table, get the auto_inc value with LAST_INSERT_ID(), use this value in the table that has to be linked to this table. And that's it. — tombom, May 16 '18 at 12:29
My problem is that I use a staging table, whose content will be deleted after converting and transferring data into other tables and and an archive table. After deleting all rows, MySQL forgets the last used ID and restarts with 1. — nrainer, May 16 '18 at 13:02

tombom · Accepted Answer · 2018-05-16 13:34:25Z

0

You can do it with a prepared statement.

set @sql = concat('alter table ai auto_increment = ', @newai);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

answered May 16 '18 at 13:34

tombom

2,8181 gold badge15 silver badges23 bronze badges

add a comment |

1 Answer 1