user3853973 user3853973 - 25 days ago 16
MySQL Question

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) without increasing autoindex

I'm running a SQL Querry, looks a bit like this

INSERT INTO people (Name, Role)
VALUES('{$Name}', '{$Occupation')
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)


The 'people' table has a surrogate ID key as primary, that is set by MySQLs Autoindex.

'Name' and 'Role' is put together as a composite unique key.

Problem is, when I run this query and hit someone with the same name and role, and get their respective ID, the autoindex still increases.

Is it possible to prevent this easily, or do i need to run 2 separate queries to check if already exists and then insert?

Answer

First, you shouldn't worry if the auto-increment increases. Admittedly, it is aesthetically "ugly", but gaps in the sequence shouldn't matter. And, the gap could appear for other reasons.

But, if this matters, you can reduce the probability of it happening by checking in the query:

INSERT INTO people (Name, Role)
    SELECT name, role
    FROM (SELECT '{$Name}' as name, '{$Occupation}' as role) x
    WHERE NOT EXISTS (SELECT 1 FROM people p WHERE p.name = x.name)
    ON DUPLICATE KEY UPDATE name = VALUES(name);  -- no op;

You can still get race conditions where gaps might occur, but this makes that much less likely.

Comments