Protips re: MySQL's LAST_INSERT_ID function

MySQL offers the LAST_INSERT_ID() function for use with tables that have AUTO_INCREMENT primary keys. When we insert into such a table the function offers a way to get the generated ID for the row that we added. OK, not news to most of us, but wait, there's more!

Using an example, and quoting directly from the docs, let's look at a couple of less obvious uses of the function.

Example one - multiple row insertion

Here I'll create a table and populate it with multiple rows. How do we find out the IDs for the rows we've added? From the docs:

With no argument, LASTINSERTID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.

So, in exactly the same way you would use the function for figuring out the ID generated on insert of a single row, LAST_INSERT_ID() will return you the first ID generated for an insert of multiple rows, and you can figure the others out programmatically. In the example, I insert four rows and LAST_INSERT_ID() tells me the ID generated for the first was 1, which in turn tells me the IDs for the subsequent three rows are {2, 3, 4}.

DROP TABLE IF EXISTS `NickCageGreatestRoles`;
CREATE TABLE `NickCageGreatestRoles` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `Title` VARCHAR(255) NOT NULL,
  `Role` VARCHAR(255),
  `Rating` INT(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=INNODB CHARSET=utf8;

INSERT INTO `NickCageGreatestRoles` 
    (`Title`, `Role`, `Rating`)
VALUES
    ('Werewolf Women of the SS', 'Fu Manchu', 1),
    ('Raising Arizona', 'H.I. McDunnough', 2),
    ('Kick-Ass', 'Big Daddy', 3),
    ('Leaving Las Vegas', 'Ben Sanderson', 4);

SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1

Example two - single insert

This is the typical usage. I insert an additional row and LAST_INSERT_ID() tells me the generated ID was 5. Unsurprising, but worth mentioning.

INSERT INTO `NickCageGreatestRoles` 
    (`Title`, `Role`, `Rating`)
VALUES
    ('Adaptation', 'Charlie Kaufman', 5);

SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5

Example three - updates

If you are updating, instead of inserting, you can still use the function. This is useful when updating based on unique constraint versus primary key, perhaps in an ON DUPLICATE KEY UPDATE clause. From the docs:

If expr is given as an argument to LASTINSERTID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LASTINSERTID().

Here I show two instances of updating data and using LAST_INSERT_ID(expr) to figure out the ID of the affected rows. Pretty nifty.

UPDATE `NickCageGreatestRoles`
SET
    `Rating` = 1,
    -- effectively a noop, 
    -- but primes the function for the next call
    `ID` = LAST_INSERT_ID(`ID`)
WHERE
    `Title` = 'Raising Arizona';

SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
2
UPDATE `NickCageGreatestRoles`
SET
    `Rating` = 2,
    `ID` = LAST_INSERT_ID(`ID`)
WHERE
    `Title` = 'Werewolf Women of the SS';

SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1

This SELECT shows the overall effect on my data. It's also worth noting that the function is well defined for use within stored procedures and transactions. I recommend reading the complete docs.

SELECT
    *
FROM `NickCageGreatestRoles`
ORDER BY
    `Rating`;
ID Title Role Rating
2 Raising Arizona H.I. McDunnough 1
1 Werewolf Women of the SS Fu Manchu 2
3 Kick-Ass Big Daddy 3
4 Leaving Las Vegas Ben Sanderson 4
5 Adaptation Charlie Kaufman 5