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 ;
Advertisement
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.