Prevent auto increment on MySQL duplicate insert

Question

Using MySQL 5.1.49, I'm trying to implement a tagging system the problem I have is with a table with two columns: id(autoincrement), 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 id's

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.

question from:https://stackoverflow.com/questions/5924762/prevent-auto-increment-on-mysql-duplicate-insert

Answer 1

You could modify your INSERT to be something like this:

INSERT INTO tablename (tag)
SELECT $tag
FROM tablename
WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)
LIMIT 1

Where $tag is 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.