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:
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
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
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?