youngbobby youngbobby - 3 months ago 12
MySQL Question

Prevent InnoDB auto increment ON DUPLICATE KEY

I am currently having problems with a primary key

ID
which is set to
auto increment
. It keeps incrementing
ON DUPLICATE KEY
.

For Example:

ID | field1 | field2

1 | user | value

5 | secondUser | value

86 | thirdUser | value


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.

Is there anyway to avoid this ?

Here's what my mySQL query looks like:

INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2)
ON DUPLICATE KEY
UPDATE field1 = :value1, field2 = :value2


And here's what my table looks like;

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 ;

Answer

You could set the innodb_autoinc_lock_mode config option to "0" for "traditional" auto-increment lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns.

That said, you shouldn't depend on the auto-increment IDs being consecutive in your application. Their purpose is to provide unique identifiers.