PDO avoid gaps when using ON DUPLICATE UPDATE

Question

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)

Why do you care about continuity here? It's meaningless for all intents and purposes. — deceze, Sep 27 '18 at 11:20
Please check https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql — Anton, Sep 27 '18 at 11:31

Dirk Scholten · Answer 1 · 2018-09-27T11:34:21.593

0

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

edited Sep 27 '18 at 11:34

answered Sep 27 '18 at 11:31

Dirk Scholten

1,013
10
19

hey @DirkScholten, and how do i do that? X_X – emma Sep 27 '18 at 11:33
1

Don't do a separate `SELECT`, `INSERT`, since that is prone to race condition problems. That's exactly what `INSERT .. ON DUPLICATE KEY UPDATE` is there to avoid. – deceze Sep 27 '18 at 11:34
@emma The general idea would be that you have two pdo statements. `$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); $stmt->execute($email); $user = $stmt->fetch();` You can then check `$user` to see if the user exists in the database. If it does then you run your update and if it doesn't you run an insert. But like deceze said. This is not the safest way to do things. It could happen that you select a user and find that it doesn't exist, then just before you start your insert someone else inserts that user. That's why the way you do it now is much safer. – Dirk Scholten Sep 27 '18 at 11:45

PDO avoid gaps when using ON DUPLICATE UPDATE

1 Answers1