Prevent auto increment on duplicate entry

Question

I have seen this issue around (See links at bottom) but I can't seem to figure out an answer. The problem is that I insert data on a table with an auto increment ID that is a primary key, and another field with a UNIQUE index to avoid duplicates. This works, but when that happens the ID is incremented, although no data has been stored.

Would it be better to remove the auto increment, and handle it myself, selecting the max(ID)?

---

At the moment I have tried several strategies to make it work as is, including INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE

My latest try was using the following query:

INSERT INTO
    content(field1, field2)
SELECT(:field1, :field2) FROM DUAL
WHERE NOT EXISTS(
    SELECT field1, field2
    FROM content
    WHERE field1 = :field1
)

---

Related

• In MySQL, when there is a "duplicate entry" error, how do I prevent the primary key from auto incrementing?
• SQL Server - How to insert a record and make sure it is unique

Answer 1

Thanks to this question I have been able to fix that error. The problem was that the SELECT(:field1, :field2) shouldn't have the parenthesis. So the query should be:

INSERT INTO
    content(field1, field2)
SELECT :field1, :field2 FROM DUAL
WHERE NOT EXISTS(
    SELECT field1, field2
    FROM content
    WHERE field1 = :field1
)