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

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.