title: Prevent auto increment on duplicate entry
body: <p>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.</p>
  
  <p><strong>Would it be better to remove the auto increment, and handle it myself, selecting the max(ID)?</strong></p>
  
  <hr>
  
  <p>At the moment I have tried several strategies to make it work as is, including <code>INSERT IGNORE</code> and <code>INSERT ... ON DUPLICATE KEY UPDATE</code></p>
  
  <p>My latest try was using the following query:</p>
  
  <pre><code>INSERT INTO
      content(field1, field2)
  SELECT(:field1, :field2) FROM DUAL
  WHERE NOT EXISTS(
      SELECT field1, field2
      FROM content
      WHERE field1 = :field1
  )
  </code></pre>
  
  <hr>
  
  <p>Related</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/8877468/in-mysql-when-there-is-a-duplicate-entry-error-how-do-i-prevent-the-primary">In MySQL, when there is a &quot;duplicate entry&quot; error, how do I prevent the primary key from auto incrementing?</a></li>
  <li><a href="https://stackoverflow.com/questions/267804/sql-server-how-to-insert-a-record-and-make-sure-it-is-unique">SQL Server - How to insert a record and make sure it is unique</a></li>
  </ul>