使用ON DUPLICATE UPDATE时，PDO可以避免间隙[重复]

Question

This question already has an answer here:

• ON DUPLICATE KEY + AUTO INCREMENT issue mysql 3 answers

I'm using pdo to handle my database and I've just noticed that when I try to insert multiple rows if I use ON DUPLICATE KEY UPDATE there is a gap in the auto-incremented field(in my case it is called id).

This is my table:

id  |  name  |  age 
1   |  Emma  |  20
2   |  Jane  |  21
3   |  John  |  25

And my statement is:

$pdo->prepare('
    INSERT INTO person 
        (`name`, `age`) 
    VALUES 
        (?, ?), (?, ?), (?, ?)
    ON DUPLICATE KEY UPDATE 
        age = VALUES(`age`)
')->execute(['Emma', '20', 'Jane', '21', 'Sarah', '35']);

If i run this query it will insert name: Sarah, age: 35 with id 6(a gap of 2 ids). Why is this happening? And is there a solution to that?

(p.s i want it to work with multiple rows insert)

</div>

Answer 1

That's expected behaviour for auto_increment values. It increments the value every time you run anINSERT on it. Even if that insert fails because of a unique value already existing. So even though you have a ON DUPLICATE KEY value in your query, it still tries to INSERT them once before realising they are already in there.

If you want to work around this you could split your query into two parts where you first try to SELECT the users and then when you see they don't exist, you INSERT them. That way you don't try to INSERT any values that might already exist so you won't increment the auto_increment value.

EDIT: You can also play around with the auto increment lock mode. I'm not exactly sure how reliable this is, but you can find some information on it here: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html