MySQL Relational Database Server and Its Tools
View All Tags
After an INSERT, you usually need the new row's ID. After an UPDATE or a DELETE, you want to know whether it found anything. MySQL has a function for each: LAST_INSERT_ID() and ROW_COUNT(). They're simple, with a few behaviours worth seeing. All of it is run here on MySQL 9.7.
Your table's IDs go 1, 2, 5, 7, 12, and you can't account for the missing numbers. Nothing is wrong. MySQL hands out an AUTO_INCREMENT value before it knows whether the insert will succeed, and it doesn't take the value back. Here's that happening on MySQL 9.7, in two different ways.
A query can look fine and read a whole table to answer. EXPLAIN shows you how MySQL plans to run it, without running it. On MySQL 9.7 the default output is a tree, not the old table of columns many guides still show. This guide reads that tree on real queries, on a table with about 5,000 rows.
A product row holds a CategoryId, not a category's name. A JOIN brings the two tables together, so you can read both in one result. INNER JOIN keeps only the rows that match, and LEFT JOIN keeps every row from the first table. This guide runs both on MySQL 9.7, and shows a counting mistake that's easy to make.
SELECT COUNT(*) FROM table tells you how many rows a table has. Add WHERE to count only some of them, or GROUP BY to count per category. There's one trap: COUNT(column) skips NULLs, so it can give a smaller number than you expect. All of it is run here on MySQL 9.7.
LIMIT says how many rows to return, and OFFSET says how many to skip, which is all you need to page through results. There's one catch that's easy to miss: without ORDER BY, you don't know which rows you'll get. This guide pages through a real table, and shows that happening.
DELETE removes rows, and outside a transaction there's no undo. Forgetting the WHERE empties the table. This guide shows the habits that keep it safe on MySQL 9.7: check the rows first, confirm how many went, try it in a transaction, and let the database's own guards stop mistakes.
An UPDATE with the wrong WHERE, or no WHERE at all, can change every row in a table, and there's no undo afterwards. A few habits make it safe: check the rows with a SELECT first, read what MySQL reports, and use a transaction when it matters. This guide does all of those on MySQL 9.7.
INSERT adds rows to a table. You list the columns, give their values, and MySQL fills in an AUTO_INCREMENT ID by itself. This guide adds one row, gets its ID back, adds several rows at once, and shows what happens when you leave out a column that's required.
SELECT is how you read from MySQL. You name the columns and the table, then narrow it down with WHERE, sort it with ORDER BY, and cut it short with LIMIT. This guide runs each of those on a real table in MySQL 9.7, and shows what comes back.
