I have a MySQL table with an auto-increment primary key id, and a not null unique field reference. I want to set reference equal to a prefix plus the records id, like “Prefix-1”.

I tried updating reference’s column to be a concat of the prefix and the id, but having a calculated column based off a auto-increment column is illegal in MySQL. I tried using a before insert trigger, setting information_schema_stats_expiry to zero and fetching the latest auto_increment value from information_schema, which works for single or sequential inserts, but multiple inserts (via insert into x values row1 row2) end up having issues as they seem to get the same auto_increment values, which from skimming MySQL docs seems intentional for the sake of reproducibility.

Fetching the last inserted record’s id and adding one doesn’t work since records can be deleted and the id would still have been incremented from the deleted record.

It seems like the only way is to remove the not null and unique modifiers from reference and then update it after every insert, but is there any alternative or better way I am missing?