LAST_INSERT_ID() en MySQL: el último id insertado, sin MAX(id)

Insertas una fila y necesitas su id para insertar otras que dependen de ella: las líneas de un pedido, los adjuntos de un expediente. La función es LAST_INSERT_ID():

INSERT INTO pedidos (cliente_id, fecha) VALUES (7, NOW());
SELECT LAST_INSERT_ID();

Devuelve el valor AUTO_INCREMENT que generó el último INSERT de tu conexión. Y eso es lo importante.

Por qué no SELECT MAX(id)

Es lo primero que se le ocurre a casi todo el mundo, y funciona mientras solo haya un usuario. En cuanto hay  más concurrentes, entre tu INSERT y tu SELECT MAX(id) otra conexión puede haber insertado su fila, y adivina cual sale... Encima es de esas cosa que te enteras en producción.

LAST_INSERT_ID() no tiene ese problema porque el servidor lo guarda por conexión: lo que inserten los demás no lo cambia.

Desde PHP

// PDO
$pdo->prepare('INSERT INTO pedidos (cliente_id, fecha) VALUES (?, NOW())')->execute([7]);
$id = $pdo->lastInsertId();

// mysqli
$mysqli->query("INSERT INTO pedidos (cliente_id, fecha) VALUES (7, NOW())");
$id = $mysqli->insert_id;

Por debajo es lo mismo: el valor de la conexión. Por eso hay que pedirlo con la misma conexión que hizo el INSERT, y justo después. Con lastInsertId() en PDO, además, el resultado es una cadena, no un entero.

A tener en cuenta

• INSERT de varias filas: devuelve el id de la primera fila insertada, no el de la última.
• Si el INSERT no inserta nada (por ejemplo, un INSERT IGNORE que choca con una clave única), el valor no cambia: sigue siendo el del INSERT anterior. Comprueba las filas afectadas antes de fiarte del id.
• Un ROLLBACK no lo deshace. Si la transacción se revierte, LAST_INSERT_ID() sigue devolviendo un id que ya no existe en la tabla.
• Insertar con el id puesto a mano no lo actualiza: solo cuenta el valor que genera AUTO_INCREMENT.

¿Cual es el id de una fila existente?

Con INSERT … ON DUPLICATE KEY UPDATE, si la fila ya estaba, no se genera id nuevo. El truco es pasárselo a la función como argumento:

INSERT INTO clientes (nif, nombre) VALUES ('12345678Z', 'Ana')
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre), id = LAST_INSERT_ID(id);

SELECT LAST_INSERT_ID(); -- el id nuevo o el de la fila existente

LAST_INSERT_ID(expr) devuelve expr y además lo guarda como valor de la conexión, así que el SELECT siguiente lo recoge en ambos casos.

En MariaDB: RETURNING

Desde la versión 10.5, MariaDB admite RETURNING, que devuelve lo insertado en la misma sentencia, sin consulta aparte:

INSERT INTO pedidos (cliente_id, fecha) VALUES (7, NOW()) RETURNING id;

Con varias filas devuelve todos los ids, no solo el primero. MySQL no lo tiene.

Si lo que buscabas era el último registro de una tabla por fecha, no el último insertado por ti, eso va por otro lado: Primer y último registro en MySQL.