ON DUPLICATE KEY + AUTO INCREMENT issue mysql

Question

I have table structure like this

when I insert row to the table I'm using this query:

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;

when I first insert data without triggering the ON DUPLICATE KEY the id increments fine:

but when the ON DUPLICATE KEY triggers and i INSERT A NEW ROW the id looks odd to me:

How can I keep the auto increment, increment properly even when it triggers ON DUPLICATE KEY?

Answer 1

This behavior is documented (paragraph in parentheses):

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

    INSERT INTO table (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

    UPDATE table SET c=c+1 WHERE a=1;

(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

Here is a simple explanation. MySQL attempts to do the insert first. This is when the id gets auto incremented. Once incremented, it stays. Then the duplicate is detected and the update happens. But the value gets missed.

You should not depend on auto_increment having no gaps. If that is a requirement, the overhead on the updates and inserts is much larger. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. A better solution is to calculate incremental values on output.