RETURNING
MySQL 8.4 has no RETURNING clause — here is what LAST_INSERT_ID and ROW_COUNT give you instead, and where they lie.
SELECT LAST_INSERT_ID(), ROW_COUNT();PostgreSQL, SQLite and MariaDB let you append RETURNING id to a write and read the affected rows back in the same round trip. MySQL 8.4 has no such clause, on any of INSERT, UPDATE or DELETE. Ported SQL that relies on it has to be rewritten.
What you use instead
LAST_INSERT_ID() returns a BIGINT UNSIGNED: the first automatically generated AUTO_INCREMENT value from the most recent INSERT on this connection. It is maintained per connection, so no other client's work can disturb it and no lock is needed. If no row was inserted, it keeps its previous value.
ROW_COUNT() returns the affected-row count of the previous statement: the number of rows changed for DML, 0 for DDL such as CREATE TABLE, and -1 for a SELECT that returned a result set. For anything more than the count — the actual new values, a generated column, a trigger's output — you run a SELECT afterwards, inside the same transaction.
What trips people up
For a multi-row INSERT, LAST_INSERT_ID() returns the value generated for the first row only. Calling it a second time after any other INSERT on the same connection gives you that statement's value, so capture it into a variable immediately. With INSERT IGNORE, a row that was ignored leaves it unchanged — it will hand you a stale id from an earlier statement rather than an error.
Example
START TRANSACTION;
INSERT INTO invoices (customer_id, issued_on, total_cents)
VALUES (1042, CURDATE(), 12750);
-- Capture immediately: the next INSERT on this connection overwrites it.
SET @invoice_id = LAST_INSERT_ID();
INSERT INTO invoice_lines (invoice_id, description, amount_cents)
VALUES (@invoice_id, 'Annual licence', 12000),
(@invoice_id, 'Support', 750);
-- ROW_COUNT() refers to the statement immediately above: 2 here.
SET @lines_written = ROW_COUNT();
-- MySQL's substitute for RETURNING: read the row back inside the transaction,
-- which is the only way to see DEFAULT, generated-column and trigger values.
SELECT id, invoice_no, created_at, total_cents
FROM invoices
WHERE id = @invoice_id;
COMMIT;When to use it
- Get the id of a row you just inserted so you can write its child rows.
- Decide whether an upsert created a record or refreshed an existing one.
- Loop a chunked DELETE or UPDATE until it reports zero affected rows.
- Read back DEFAULT, generated-column or trigger-assigned values after a write.
More examples
Telling an insert from an update after an upsert
This is how you find out whether a client is new or returning without a preceding SELECT. Be careful with the 0 case: it means the row existed and was already correct, which application code usually wants to treat as success, not as a miss. The C API's CLIENT_FOUND_ROWS flag turns that 0 into a 1, so the same query can report differently depending on how your driver connected.
INSERT INTO sessions (token, user_id, last_seen)
VALUES ('a9f3...', 1042, NOW())
AS new
ON DUPLICATE KEY UPDATE last_seen = new.last_seen;
-- ON DUPLICATE KEY UPDATE encodes the branch it took in the affected-rows value:
-- 1 = inserted a new row
-- 2 = updated an existing row
-- 0 = existing row already held these exact values
SELECT ROW_COUNT() AS affected;
-- LAST_INSERT_ID() is set on BOTH branches for a table with an AUTO_INCREMENT
-- column, so it identifies the row either way.
SELECT LAST_INSERT_ID() AS session_row_id;Rows matched versus rows changed
Worth internalising before you write retry logic or an assertion on the affected-row count. A conditional update that returns 0 has two very different causes — the row did not match, or it matched and was already correct — and ROW_COUNT() alone cannot distinguish them. Either run the COUNT(*), or write the WHERE so that a match is impossible without a change.
UPDATE users
SET plan = 'pro'
WHERE org_id = 17;
-- Client prints: Rows matched: 40 Changed: 3 Warnings: 0
-- ROW_COUNT() reports CHANGED, not MATCHED: 3, not 40.
-- The other 37 rows were already on the 'pro' plan.
SELECT ROW_COUNT();
-- If you need the matched count, ask for it explicitly with the same predicate.
SELECT COUNT(*) FROM users WHERE org_id = 17;Capturing the ids of a batch insert
This is the gap that RETURNING would close, and the reason ORMs on MySQL often fall back to single-row inserts when they need the generated keys. If the batch is large enough that per-row inserts hurt, stamp a batch identifier onto the rows yourself: it turns key retrieval into an ordinary indexed SELECT and stays correct regardless of the auto-increment lock mode.
-- Wrong. innodb_autoinc_lock_mode = 2 is the MySQL 8.4 default and does NOT
-- promise the three generated values are consecutive.
INSERT INTO jobs (queue, payload) VALUES ('mail', '{}'), ('mail', '{}'), ('mail', '{}');
SET @first = LAST_INSERT_ID();
-- @first, @first + 1, @first + 2 <- do not rely on this
-- Reliable option A: supply your own natural key and read the rows back.
INSERT INTO jobs (queue, payload, batch_uuid)
VALUES ('mail', '{}', 'b3c1-...'), ('mail', '{}', 'b3c1-...'), ('mail', '{}', 'b3c1-...');
SELECT id FROM jobs WHERE batch_uuid = 'b3c1-...';
-- Reliable option B: one INSERT per row, reading LAST_INSERT_ID() after each.
-- Slower, but each call is unambiguous.
Discussion