MySQL – INSERT | Love Me Some Coding

INSERT adds rows. The basic form takes ten seconds to learn; the parts worth knowing are the multi-row form, what to do when the row might already exist, and which columns you are allowed to leave out.

The basic form, and the multi-row one

INSERT INTO crust (name, price_delta, active, display_order, public_id, created_at, updated_at, deleted) VALUES
    ('Gluten Free', 3.00, TRUE, 5, 'cccccccc-0000-4000-8000-000000000005', '2026-01-01', '2026-01-01', FALSE),
    ('Cauliflower', 3.50, TRUE, 6, 'cccccccc-0000-4000-8000-000000000006', '2026-01-01', '2026-01-01', FALSE);

SELECT id, name, price_delta, display_order FROM crust ORDER BY id;

+----+----------------+-------------+---------------+
| id | name           | price_delta | display_order |
+----+----------------+-------------+---------------+
|  1 | Original Pan   |        0.00 |             1 |
|  2 | Hand Tossed    |        0.00 |             2 |
|  3 | Thin 'N Crispy |        0.00 |             3 |
|  4 | Stuffed Crust  |        2.50 |             4 |
|  5 | Gluten Free    |        3.00 |             5 |
|  6 | Cauliflower    |        3.50 |             6 |
+----+----------------+-------------+---------------+

Always list the columns. INSERT INTO crust VALUES (…) is legal and depends on column order, so the day someone adds a column your insert starts putting values in the wrong places — or fails, if you are lucky.

The multi-row form is not just tidier, it is dramatically faster. One statement means one round trip, one parse, and one transaction; a thousand separate inserts mean a thousand of each. Batching in the low hundreds to low thousands is the usual sweet spot — past that the statement gets large enough to bump into max_allowed_packet.

Which columns you can omit

A column can be left out if it has a DEFAULT, is AUTO_INCREMENT, or is nullable. Everything else must be supplied — which is why the insert above lists public_id, created_at and updated_at even though they look like bookkeeping.

That is worth pausing on, because it is a real consequence of a schema decision. The pizza schema declares those NOT NULL with no default and fills them from JPA lifecycle callbacks in the application. The benefit is one mechanism, visible in the Java, that behaves the same on any database. The cost is exactly this: anything writing outside the application — a migration, a fix at the mysql> prompt — has to supply them by hand. Database-side DEFAULT CURRENT_TIMESTAMP would trade that the other way. See date and time types.

INSERT ... SELECT

Insert the result of a query rather than literals — the standard way to copy rows, populate a summary table, or backfill:

INSERT INTO topping (name, price, category, active, public_id, created_at, updated_at, deleted)
SELECT CONCAT('Extra ', name), price * 1.5, category, TRUE,
       CONCAT('bbbbbbbb-0000-4000-8000-', LPAD(id + 100, 12, '0')), '2026-01-01', '2026-01-01', FALSE
FROM   topping
WHERE  category = 'CHEESE';

No VALUES keyword. The select list has to line up with the column list positionally, so read them together.

When the row might already exist

crust.name is UNIQUE, so inserting a duplicate is an error. Three ways to handle that, and they are not interchangeable:

INSERT INTO crust (name, price_delta, active, display_order, public_id, created_at, updated_at, deleted)
VALUES ('Gluten Free', 4.25, TRUE, 5, 'cccccccc-0000-4000-8000-000000000005', '2026-01-01', '2026-01-01', FALSE)
ON DUPLICATE KEY UPDATE price_delta = VALUES(price_delta);

SELECT id, name, price_delta FROM crust WHERE name = 'Gluten Free';

+----+-------------+-------------+
| id | name        | price_delta |
+----+-------------+-------------+
|  5 | Gluten Free |        4.25 |
+----+-------------+-------------+

	On a duplicate key
`ON DUPLICATE KEY UPDATE`	updates the existing row. A real upsert, and the one you usually want.
`INSERT IGNORE`	skips the row silently — and downgrades other errors to warnings too, including truncated data and failed foreign keys. Too blunt; prefer the explicit form.
`REPLACE`	deletes the old row and inserts a new one. It gets a new `AUTO_INCREMENT` id, and any child row with `ON DELETE CASCADE` goes with it. Rarely what anyone means.

MySQL 8.0.20 deprecates VALUES(col) in favour of a row alias:

INSERT INTO crust (name, price_delta, active, display_order, public_id, created_at, updated_at, deleted)
VALUES ('Cauliflower', 3.75, TRUE, 6, 'cccccccc-0000-4000-8000-000000000006', '2026-01-01', '2026-01-01', FALSE) AS new
ON DUPLICATE KEY UPDATE price_delta = new.price_delta;

AUTO_INCREMENT leaves gaps

Insert one more crust and look at the id it gets:

INSERT INTO crust (name, price_delta, active, display_order, public_id, created_at, updated_at, deleted)
VALUES ('Sourdough', 2.00, TRUE, 7, 'cccccccc-0000-4000-8000-000000000007', '2026-01-01', '2026-01-01', FALSE);

SELECT id, name FROM crust ORDER BY id;

+----+----------------+
| id | name           |
+----+----------------+
|  1 | Original Pan   |
|  2 | Hand Tossed    |
|  3 | Thin 'N Crispy |
|  4 | Stuffed Crust  |
|  5 | Gluten Free    |
|  6 | Cauliflower    |
|  9 | Sourdough      |
+----+----------------+

Seven rows, and the last id is 9. Ids 7 and 8 do not exist and never will: the two ON DUPLICATE KEY UPDATE statements above each allocated one before discovering the row already existed, and MySQL does not give them back. A rolled-back transaction does the same.

This is normal and by design — reusing ids would mean locking the counter across concurrent inserts. The lesson is that an AUTO_INCREMENT id is an identifier, not a count. "How many crusts are there" is COUNT(*), never MAX(id). It also means an INT key runs out sooner than the row count suggests, which is the argument for BIGINT in data types.

Getting the id back

Inserting an order and then its line items needs the order's new id. LAST_INSERT_ID() is per-connection, so it is safe under concurrency — see LAST_INSERT_ID.

Bulk loading

LOAD DATA LOCAL INFILE '/tmp/toppings.csv'
INTO TABLE topping
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES  TERMINATED BY '\n'
IGNORE 1 LINES
(name, price, category);

Far faster than INSERT for a large file, because it skips per-statement parsing. LOCAL reads the file on the client; without it the server reads it, which needs FILE privilege and the file to be on the server. Both the server (local_infile) and the client have to enable LOCAL — it is off by default because a malicious server could otherwise ask your client for arbitrary files.

What to remember

List your columns.
One multi-row INSERT beats many single-row ones by a wide margin.
ON DUPLICATE KEY UPDATE for an upsert; avoid INSERT IGNORE and REPLACE.
AUTO_INCREMENT gaps are normal. Ids are identifiers, not counts.