How to obtain the last inserted value of an AUTO_INCREMENT column using the LAST_INSERT_ID() function?

LAST_INSERT_ID() is a MySQL built-in function that returns the last inserted value of an AUTO_INCREMENT column. For example:

CREATE TABLE MyTable (id INTEGER AUTO_INCREMENT, comment CHAR(80),
  PRIMARY KEY (id));

SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |                0 |
  -- +------------------+

INSERT INTO MyTable (comment) VALUES ('I like it!'); 
INSERT INTO MyTable (comment) VALUES ('Good job1'); 

 SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |                2 |
  -- +------------------+

SELECT * FROM MyTable; 
  -- +----+------------+
  -- | id | comment    |
  -- +----+------------+
  -- |  1 | I like it! |
  -- |  2 | Good job1  |
  -- +----+------------+

Note that LAST_INSERT_ID() returns the first inserted id if the last INSERT statement inserted multiple rows. For example:

INSERT INTO MyTable (comment) VALUES 
  ('Best example!'), 
  ('Easy to follow!'); 

SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |                3 |
  -- +------------------+

SELECT * FROM MyTable; 
  -- +----+-----------------+
  -- | id | comment         |
  -- +----+-----------------+
  -- |  1 | I like it!      |
  -- |  2 | Good job1       |
  -- |  3 | Best example!   |
  -- |  4 | Easy to follow! |
  -- +----+-----------------+

If LAST_INSERT_ID() is called with an argument, it will return the argument and set the argument as the last inserted AUTO_INCREMENT value. For example:

SELECT LAST_INSERT_ID(99);
  -- +--------------------+
  -- | LAST_INSERT_ID(99) |
  -- +--------------------+
  -- |                 99 |
  -- +--------------------+

SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |               99 |
  -- +------------------+

You can use the combination of LAST_INSERT_ID(seq) and LAST_INSERT_ID() to build a table-based sequence generator as shown below:

CREATE TABLE sequence (id INTEGER);
INSERT INTO sequence VALUES (0);

UPDATE sequence SET id = LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |                1 |
  -- +------------------+

UPDATE sequence SET id = LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |                2 |
  -- +------------------+

UPDATE sequence SET id = LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
  -- +------------------+
  -- | LAST_INSERT_ID() |
  -- +------------------+
  -- |                3 |
  -- +------------------+

Reference information of the LAST_INSERT_ID() function:

LAST_INSERT_ID(exp): seq
  Returns the last inserted value of an AUTO_INCREMENT column, 
  if argument is not provided.
  Returns the given exp and sets it as the last inserted 
  AUTO_INCREMENT value, if argument is provided.

Arguments, return value and availability:
  exp: Optional. Default is the first AUTO_INCREMENT value 
    of the last INSERT statement.
  seq: Return value. The last inserted AUTO_INCREMENT value.
  Available since MySQL 4.0.

⇒ PS_CURRENT_THREAD_ID() - PS Thread ID of Current Connect

⇐ ICU_VERSION() - ICU (International Components for Unicode) Version

⇑ MySQL Functions on System Information

⇑⇑ MySQL Function References