Prevent auto increment on MySQL duplicate insert
Using MySQL 5.1.49, I'm trying to implement a tagging system
the problem I have is with a table with two columns:
tag(unique varchar) (InnoDB)
When using query,
INSERT IGNORE INTO tablename SET tag="whatever", the auto increment
id value increases even if the insert was ignored.
Normally this wouldn't be a problem, but I expect a lot of possible attempts to insert duplicates for this particular table which means that my next value for
id field of a new row will be jumping way too much.
For example I'll end up with a table with say 3 rows but bad
1 | test 8 | testtext 678 | testtextt
Also, if I don't do
INSERT IGNORE and just do regular
INSERT INTO and handle the error, the auto increment field still increases so the next true insert is still a wrong auto increment.
Is there a way to stop auto increment if there's an
INSERT duplicate row attempt?
As I understand for MySQL 4.1, this value wouldn't increment, but last thing I want to do is end up either doing a lot of
SELECT statements in advance to check if the tags exist, or worse yet, downgrade my MySQL version.
You could modify your INSERT to be something like this:
$tagis the tag (properly quoted or as a placeholder of course) that you want to add if it isn't already there. This approach won't even trigger an INSERT (and the subsequent autoincrement wastage) if the tag is already there. You could probably come up with nicer SQL than that but the above should do the trick.
If your table is properly indexed then the extra SELECT for the existence check will be fast and the database is going to have to perform that check anyway.
This approach won't work for the first tag though. You could seed your tag table with a tag that you think will always end up being used or you could do a separate check for an empty table.
I just found this gem...
If affectedRows = 1 then it inserted; otherwise if affectedRows = 0 there was a duplicate.
The MySQL documentation for v 5.5 says:
Since version 5.1 InnoDB has configurable Auto-Increment Locking. See also http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...
Workaround: use option innodb_autoinc_lock_mode=0 (traditional).
I found mu is too short's answer helpful, but limiting because it doesn't do inserts on an empty table. I found a simple modification did the trick:
Replacing the table in the from clause with a "fake" table
(select 1) as aallowed that part to return a record which allowed the insert to take place. I'm running mysql 5.5.37. Thanks mu for getting me most of the way there ....
The accepted answer was useful, however I ran into a problem while using it that basically if your table had no entries it would not work as the select was using the given table, so instead I came up with the following, which will insert even if the table is blank, it also only needs you to insert the table in 2 places and the inserting variables in 1 place, less to get wrong.
Hope you find it useful
You can always add
ON DUPLICATE KEY UPDATERead here (not exactly, but solves your problem it seems).
From the comments, by @ravi
I had the same problem but didn't want to use innodb_autoinc_lock_mode = 0 since it felt like I was killing a fly with a howitzer.
To resolve this problem I ended up using a temporary table.
Then I inserted the values with:
After that you simply do another insert but use "not in" to ignore duplicates.
I haven't tested this for performance, but it does the job and is easy to read. Granted this was only important because I was working with data that was constantly being updated so I couldn't ignore the gaps.
modified the answer from mu is too short, (simply remove one line) as i am newbie and i cannot make comment below his answer. Just post it here
the query below works for the first tag
I just put an extra statement after the insert/update query: ALTER TABLE
table_nameAUTO_INCREMENT = 1 And then he automatically picks up the highest prim key id plus 1.