I want to insert a value into a table if it doesn’t exist, and get the id (which is autoincremented) of the inserted row or the id of the existing row.
At the moment I am doing this like so:
INSERT INTO table SET col = 'value' ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
Then I can just get the last insert id to get the id of the existing or new row (‘col’ has a UNIQUE constraint on it).
But the table in question is using the InnoDB storage engine, which means that it allocates the autoincrement value to the row before the INSERT operation, so if the INSERT fails, the autoincrement value has still increased. e.g. with an empty table
INSERT INTO table SET col = 'value1' ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
would return the id of 1.
If I try and insert ‘value1’ again 500 times, each time I would get back the id 1.
Now if I try and insert a new value
INSERT INTO table SET col = 'value2' ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
I will get back the id of 502, since InnoDB increased the auto increment value for those 500 insert operations, even though they didn’t insert anything.
So I am worried about my autoincrement value getting really high really quickly.
There are a few solutions I can think of:
- Switch to MyISAM and use triggers instead of Foreign Key Constraints (I don’t need row level locking in this instance)
- Check whether a record exists before trying to insert it (and only insert records that don’t exist)
- Periodically run a script that ‘cleans up’ the auto increment values (I can’t do this as I may want to use the id values in URLs)
- Don’t worry about it and keep everything as it is
Can anyone advise me on this issue?