Prevent InnoDB auto increment ON DUPLICATE KEY
<p>I am currently having problems with a primary key <code>ID</code> which is set to <code>auto increment</code>. It keeps incrementing <code>ON DUPLICATE KEY</code>.</p>

<p>For Example:</p>

<pre><code>ID | field1     | field2

1  | user       | value

5  | secondUser | value

86 | thirdUser  | value
</code></pre>

<p>From the description above, you'll notice that I have 3 inputs in that table but due to auto increment on each update, ID has 86 for the third input.</p>

<p>Is there anyway to avoid this ?</p>

<p>Here's what my mySQL query looks like:</p>

<pre><code>INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2)
            ON DUPLICATE KEY
            UPDATE field1 = :value1, field2 = :value2 
</code></pre>

<p>And here's what my table looks like;</p>

<pre><code>CREATE TABLE IF NOT EXISTS `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(200) NOT NULL,
  `field2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `field1` (`field1`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
</code></pre>