INSERT INTO ... VALUES adds rows. Always name the columns: INSERT INTO products VALUES (...) depends on column order, so the day somebody adds a column in the middle, every insert in your codebase starts writing values into the wrong fields. Naming them costs nothing and is immune.
Inserting many rows in one statement is dramatically faster than many statements, because each statement is parsed separately and, outside a transaction, committed separately. Thousands of single-row inserts in a loop is the most common reason an import takes an hour instead of a minute.
When the row might already exist there are three behaviours and they are different. INSERT IGNORE turns errors into warnings and skips the row - which also hides unrelated errors, so use it narrowly. ON DUPLICATE KEY UPDATE inserts or updates, which is the usual upsert. REPLACE deletes the existing row and inserts a new one, which means a new AUTO_INCREMENT value and any ON DELETE CASCADE firing - rarely what anybody wants.
LAST_INSERT_ID() returns the id generated by your connection's most recent insert, and it is per-connection, so a concurrent insert by somebody else cannot confuse it.
-- Name the columns. Positional inserts break the day a column is added.
INSERT INTO customers (name, email, city)
VALUES ('Ada Lovelace', 'ada@example.com', 'London');
SELECT LAST_INSERT_ID(); -- per connection, so it is safe under concurrency
-- One statement for many rows: parsed once, committed once. Thousands of
-- single-row inserts in a loop is why imports take an hour.
INSERT INTO customers (name, email, city) VALUES
('Grace Hopper', 'grace@example.com', 'New York'),
('Alan Turing', 'alan@example.com', 'Manchester'),
('Katherine J.', 'kj@example.com', 'Hampton');
-- Upsert: insert, or update the row that is already there.
INSERT INTO products (sku, name, price)
VALUES ('SKU-00001234', 'Pencil', 1.20)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price);
-- Skips rows that would error - including errors you wanted to know about.
-- INSERT IGNORE INTO customers (email) VALUES ('ada@example.com');
-- DELETES the old row first: new AUTO_INCREMENT, and cascades fire.
-- REPLACE INTO products (sku, name, price) VALUES ('SKU-00001234', 'Pen', 2.00);
-- Copy rows from a query.
INSERT INTO archived_orders (id, customer_id, total)
SELECT id, customer_id, total FROM orders WHERE placed_at < '2024-01-01';