MySQL LAST_INSERT_ID used with On Duplicate Key Update

last_insert_test.sql

/*
 * Quick demonstration of `id = LAST_INSERT_ID(id)` being the key to returning
 * the existing id when doing `ON DUPLICATE KEY UPDATE`. The unfortunate side
 * effect of this approach is that the sequence number for `id` increments on
 * every update, even though the value for the updated row does not change. On
 * update-heavy systems with 32-bit id`s, the sequence could be exhausted in a
 * fairly short amount of time.
 *
 * Just switch to MariaDB and use `RETURNING id` instead. PostgreSQL got this
 * keyword in 2006. Oracle was doing this in procedural code no later than 1997.
 * Doing bad workarounds because you use inferior tools is a thing to avoid.
*/

create table last_insert (
    id int auto_increment primary key,
    name  varchar(32) not null,
    `desc` varchar(128) null,
    unique index (name)
) ;

insert into last_insert(name, `desc`) values ('first', null);
select last_insert_id();
-- 1

insert into last_insert(name, `desc`) values ('second', null);
select last_insert_id();
-- 2
insert into last_insert(name, `desc`) values ('first', null)
    on duplicate key update `desc` = 'update first', id = last_insert_id(id);
select last_insert_id();
-- 1

insert into last_insert(name, `desc`) values ('second', null)
    on duplicate key update `desc` = 'update second';
select last_insert_id();
-- 1

insert into last_insert(name, `desc`) values ('second', null)
on duplicate key update `desc` = 're-update second', id = last_insert_id(id);
select last_insert_id();
-- 2

insert into last_insert(name, `desc`) values ('third', 'third is not updated')
on duplicate key update `desc` = 'third was updated', id = last_insert_id(id);
select last_insert_id();
-- 6
-- notice how the two updates have caused the sequence to increment,
-- even though the original id's remain unchanged

select * from last_insert;/*
+--+------+--------------------+
|id|name  |desc                |
+--+------+--------------------+
|1 |first |update first        |
|2 |second|re-update second    |
|6 |third |third is not updated|
+--+------+--------------------+*/

drop table last_insert;