Generated ids and affected rows
35 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)After every change, MySQL can tell you two things: which id it just generated, and how many rows the statement really changed. LAST_INSERT_ID() and ROW_COUNT() return them, and every application that saves data depends on both.
LAST_INSERT_ID() after one and many rows
LAST_INSERT_ID() returns the AUTO_INCREMENT id generated by the most recent INSERT on your connection. After a multi-row INSERT it returns the id of the first new row.
Example 1 adds a customer without giving the id, then asks MySQL which id it picked. Example 2 adds three categories at once.
Example 1 · The id of a new customer
INSERT INTO customers (name, email, phone, city, joined_on)VALUES ('Nisha Bhat', 'nisha.bhat@example.com', '9876500011', 'Mangaluru', '2025-06-06');
SELECT LAST_INSERT_ID();
SELECT customer_id, name, city FROM customers WHERE customer_id = LAST_INSERT_ID();+------------------+ | LAST_INSERT_ID() | +------------------+ | 11 | +------------------+ +-------------+------------+-----------+ | customer_id | name | city | +-------------+------------+-----------+ | 11 | Nisha Bhat | Mangaluru | +-------------+------------+-----------+
What this code does
- The
INSERTleft outcustomer_id, andLAST_INSERT_ID()reports the number MySQL picked: 11. - The function can be used anywhere a value can, so
WHERE customer_id = LAST_INSERT_ID()fetches the row just added.
Example 2 · Many rows: you get the first id
INSERT INTO categories (name) VALUES ('Sports'), ('Kitchen'), ('Garden');SELECT LAST_INSERT_ID() AS first_new_id;
-- A row with an id you give yourself generates no idINSERT INTO categories (category_id, name) VALUES (50, 'Music');SELECT LAST_INSERT_ID() AS still;
SELECT category_id, name FROM categories WHERE category_id > 7 ORDER BY category_id;+--------------+ | first_new_id | +--------------+ | 8 | +--------------+ +-------+ | still | +-------+ | 8 | +-------+ +-------------+---------+ | category_id | name | +-------------+---------+ | 8 | Sports | | 9 | Kitchen | | 10 | Garden | | 50 | Music | +-------------+---------+
What this code does
- The three new categories got ids 8, 9 and 10, but
LAST_INSERT_ID()returns 8, the first one. In a simple multi-rowINSERTlike this one, the ids are consecutive, so the last is 8 + 3 - 1 = 10. - Inserting
Musicwith the id 50 written by hand generated nothing, so the value stays 8.AS first_new_idgives the result column a readable name.
A connection is one logged-in session with the server, such as one mysql client window.
You leave the id out when you insert, so you do not know which number MySQL picked. This function tells you, without searching the table. Two more rules matter. LAST_INSERT_ID() does not change when an INSERT generates no id, for example when you give the id yourself or the table has no AUTO_INCREMENT column. And it is per connection: rows inserted by other users at the same moment never change your value.
Why applications need the new id
A user variable is a named value that starts with @ and lasts until your connection closes, such as SET @order_id = LAST_INSERT_ID();. It lets later statements reuse a value.
Example 3 places an order: the new order id is saved in a variable and used for each item row. Example 4 shows a trap that replaces it.
Example 3 · Place an order: the new id links the items
INSERT INTO orders (customer_id, order_date) VALUES (9, '2025-06-06');SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (@order_id, 11, 2, 399.00), (@order_id, 14, 1, 299.00);
SELECT @order_id;SELECT * FROM orders WHERE order_id = @order_id;SELECT * FROM order_items WHERE order_id = @order_id ORDER BY product_id;+-----------+ | @order_id | +-----------+ | 16 | +-----------+ +----------+-------------+------------+--------+ | order_id | customer_id | order_date | status | +----------+-------------+------------+--------+ | 16 | 9 | 2025-06-06 | PLACED | +----------+-------------+------------+--------+ +----------+------------+----------+------------+ | order_id | product_id | quantity | unit_price | +----------+------------+----------+------------+ | 16 | 11 | 2 | 399.00 | | 16 | 14 | 1 | 299.00 | +----------+------------+----------+------------+
What this code does
- Sneha Patil's new order got id 16.
SET @order_id = LAST_INSERT_ID();keeps it, and both item rows use@order_id, so nothing is typed by hand. - An application does exactly this: insert the parent, read the generated id from the database library, then insert the children with it.
Example 4 · Trap: the next INSERT replaces the id
CREATE TABLE activity_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, note VARCHAR(40) NOT NULL);
INSERT INTO orders (customer_id, order_date) VALUES (5, '2025-06-07');SELECT LAST_INSERT_ID() AS after_order;
INSERT INTO activity_log (note) VALUES ('order placed');SELECT LAST_INSERT_ID() AS after_log;+-------------+ | after_order | +-------------+ | 16 | +-------------+ +-----------+ | after_log | +-----------+ | 1 | +-----------+
What this code does
- After the order, the value is 16, the new order id. One log
INSERTlater it is 1, the id of the log row, and the order id is gone. - Using
LAST_INSERT_ID()for the items at this point would link them to order 1, a real order of another customer. Save the id withSET @order_id = LAST_INSERT_ID();right after theINSERTit belongs to.
The new id links the next rows. When a customer places an order, the app inserts the orders row, and then needs that order's id for every order_items row. The same happens for a booking and its passengers, or a student and her enrollments.
Applications keep the id too: the database library in any language hands back the generated id right after the INSERT. Never find the new id with SELECT MAX(order_id) FROM orders. If another customer orders at the same moment, MAX can return their id, and your items join the wrong order. LAST_INSERT_ID() is safe because it belongs to your connection.
Order of execution
Place an order
Put the statements in the order an app runs them to save one order with its items.
INSERT into any table with an AUTO_INCREMENT column replaces it.ROW_COUNT(): how many rows changed
ROW_COUNT() returns how many rows the previous statement changed: rows added by INSERT, removed by DELETE, or actually changed by UPDATE. After a SELECT it returns -1.
Example 5 prints ROW_COUNT() after an UPDATE, a DELETE and a seat booking that changes no row.
Example 5 · ROW_COUNT after UPDATE, DELETE and a lost seat
UPDATE products SET stock = 0 WHERE category_id = 4;SELECT ROW_COUNT() AS changed;
DELETE FROM order_items WHERE order_id = 2;SELECT ROW_COUNT() AS deleted;
CREATE TABLE seats ( seat CHAR(3) PRIMARY KEY, status ENUM('FREE', 'BOOKED') NOT NULL);INSERT INTO seats VALUES ('F07', 'FREE'), ('F08', 'BOOKED');
UPDATE seats SET status = 'BOOKED' WHERE seat = 'F07' AND status = 'FREE';SELECT ROW_COUNT() AS got_f07;
UPDATE seats SET status = 'BOOKED' WHERE seat = 'F08' AND status = 'FREE';SELECT ROW_COUNT() AS got_f08;+---------+ | changed | +---------+ | 2 | +---------+ +---------+ | deleted | +---------+ | 3 | +---------+ +---------+ | got_f07 | +---------+ | 1 | +---------+ +---------+ | got_f08 | +---------+ | 0 | +---------+
What this code does
- Category 4 has three products, but the mouse already had stock 0, so the
UPDATEchanged only 2 rows. TheDELETEremoved the 3 items of order 2. - The seat bookings show the check an app makes: 1 means F07 was free and is now yours; 0 means F08 was already booked, so the app shows "seat taken" instead of a ticket.
For UPDATE, MySQL counts only rows whose value really changed. A row that already had the new value is matched by the WHERE but not counted. Some database libraries report the matched count instead; check what yours returns. Applications use the count as a check. If "book seat F07 if it is free" changed 1 row, the seat is yours; if it changed 0, someone else got it first. Call ROW_COUNT() right after the statement: the next statement replaces the value.
Reading the Query OK and Rows matched messages
The Query OK message is the line the mysql client prints after a change, such as Query OK, 2 rows affected. It shows how many rows changed, the same number ROW_COUNT() returns.
Example 6 runs a few changes in the interactive client and shows the status line MySQL prints after each.
Example 6 · The same changes in the interactive client
UPDATE products SET stock = 0 WHERE category_id = 4;INSERT INTO categories (name)VALUES ('Sports'), ('Kitchen');SELECT name FROM categories WHERE category_id > 7 ORDER BY category_id;DELETE FROM categories WHERE category_id = 99;SELECT name FROM categories WHERE category_id = 99;mysql> UPDATE products SET stock = 0 WHERE category_id = 4;
Query OK, 2 rows affected (0.135 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> INSERT INTO categories (name)
-> VALUES ('Sports'), ('Kitchen');
Query OK, 2 rows affected (0.149 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT name FROM categories WHERE category_id > 7 ORDER BY category_id;
+---------+
| name |
+---------+
| Sports |
| Kitchen |
+---------+
2 rows in set (0.011 sec)
mysql> DELETE FROM categories WHERE category_id = 99;
Query OK, 0 rows affected (0.004 sec)
mysql> SELECT name FROM categories WHERE category_id = 99;
Empty set (0.008 sec)What this code does
- Each line matches the counts from
ROW_COUNT(): 2 rows changed of 3 matched, 2 records inserted, 0 rows deleted because no category 99 exists.Empty setmeans theSELECTfound nothing. - This is a sample because the times in brackets change on every run and every computer, and some client versions print them with two decimals, such as
(0.01 sec).
Lessons leave these status lines out of the output tables, but you see them every time you type a statement yourself.
Query OK, 2 rows affected (0.01 sec) means the statement worked and changed 2 rows, the same number as ROW_COUNT(). After an UPDATE, Rows matched: 3 Changed: 2 Warnings: 0 adds how many rows the WHERE found. After a multi-row INSERT, Records: 2 Duplicates: 0 Warnings: 0 counts the rows sent. A SELECT ends with 2 rows in set. If Warnings is above 0, run SHOW WARNINGS; straight away to read them. The time in brackets depends on your computer.
Next step
Check your understanding
11 quick questions