INSERT, Multi-Row Writes, INSERT ... SELECT, and Generated Values

Learning outcomes

Chapter 06 turns the course from mostly reading data into changing shared state safely. A write is not merely a command that “adds a row.” MySQL must validate the statement, acquire the necessary InnoDB locks, create undo and redo information, maintain indexes and constraints, allocate generated values, and either commit or roll back according to the transaction boundary.

We use a disposable servicehub_write_lab database throughout the chapter. The domain is the same field-service setting used earlier: customers open work orders, technicians handle them, and parts inventory is shared by concurrent sessions. Lesson 1 establishes the write schema and concentrates on inserts before later lessons deliberately create contention.

Build the disposable write lab

The setup below is intentionally explicit. InnoDB is MySQL’s default transactional storage engine and is the engine assumed by every transaction and locking example in this chapter. A generated column derives its value from other columns. An AUTO_INCREMENT column asks MySQL to allocate identifiers; it is an identifier mechanism, not a promise that IDs will be gapless.

DROP DATABASE IF EXISTS servicehub_write_lab;CREATE DATABASE servicehub_write_lab  CHARACTER SET utf8mb4  COLLATE utf8mb4_0900_ai_ci;USE servicehub_write_lab;CREATE TABLE customers (  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  customer_name VARCHAR(100) NOT NULL,  region VARCHAR(20) NOT NULL,  PRIMARY KEY (customer_id)) ENGINE=InnoDB;CREATE TABLE technicians (  technician_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  technician_name VARCHAR(100) NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE,  PRIMARY KEY (technician_id)) ENGINE=InnoDB;CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  request_key VARCHAR(64) NOT NULL,  customer_id BIGINT UNSIGNED NOT NULL,  technician_id BIGINT UNSIGNED NULL,  status VARCHAR(16) NOT NULL DEFAULT 'open',  priority TINYINT UNSIGNED NOT NULL DEFAULT 2,  labor_minutes INT UNSIGNED NOT NULL DEFAULT 0,  parts_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,  estimated_total DECIMAL(12,2)    GENERATED ALWAYS AS (parts_cost + labor_minutes * 1.25) STORED,  opened_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  closed_at TIMESTAMP NULL,  PRIMARY KEY (work_order_id),  UNIQUE KEY uq_work_orders_request_key (request_key),  KEY ix_work_orders_status_priority (status, priority, work_order_id),  KEY ix_work_orders_customer (customer_id),  CONSTRAINT ck_work_orders_status CHECK (status IN ('open','assigned','closed','cancelled')),  CONSTRAINT ck_work_orders_priority CHECK (priority BETWEEN 1 AND 3),  CONSTRAINT fk_work_orders_customer FOREIGN KEY (customer_id)    REFERENCES customers(customer_id),  CONSTRAINT fk_work_orders_technician FOREIGN KEY (technician_id)    REFERENCES technicians(technician_id)) ENGINE=InnoDB;CREATE TABLE work_order_staging (  request_key VARCHAR(64) NOT NULL,  customer_id BIGINT UNSIGNED NOT NULL,  priority TINYINT UNSIGNED NOT NULL,  labor_minutes INT UNSIGNED NOT NULL DEFAULT 0,  parts_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00) ENGINE=InnoDB;CREATE TABLE parts_inventory (  part_id INT UNSIGNED NOT NULL,  part_name VARCHAR(80) NOT NULL,  on_hand INT NOT NULL,  PRIMARY KEY (part_id),  CONSTRAINT ck_parts_on_hand CHECK (on_hand >= 0)) ENGINE=InnoDB;INSERT INTO customers(customer_name,region) VALUES ('Northwind Clinic','north'), ('City Library','central'), ('Harbor Foods','south');INSERT INTO technicians(technician_name) VALUES ('Ava'),('Omar');INSERT INTO parts_inventory(part_id,part_name,on_hand) VALUES (1,'Filter cartridge',20),(2,'Valve kit',15);

After setup, verify the engine and generated expression rather than trusting the script visually:

SHOW CREATE TABLE work_orders\GSELECT TABLE_NAME, ENGINEFROM information_schema.tablesWHERE table_schema='servicehub_write_lab'  AND table_name IN ('work_orders','parts_inventory');

SHOW CREATE TABLE is the authoritative server-rendered definition. It should show the unique request key, checks, foreign keys, secondary indexes, the generated estimated_total column, and ENGINE=InnoDB.

Single-row INSERT: name target columns and observe generated state

The safest beginner habit is to name the target columns. It makes the mapping between values and schema visible and survives many additive schema changes better than relying on physical column order.

INSERT INTO work_orders  (request_key, customer_id, priority, labor_minutes, parts_cost)VALUES  ('REQ-1001', 1, 1, 40, 12.50);SELECT LAST_INSERT_ID() AS allocated_id, ROW_COUNT() AS rows_changed;SELECT work_order_id, request_key, status, priority,       labor_minutes, parts_cost, estimated_totalFROM work_ordersWHERE request_key='REQ-1001';

With a freshly reset lab, the ID is normally 1 and estimated_total is 62.50. The important lesson is not the literal number 1. LAST_INSERT_ID() is connection-specific state that reports the generated value associated with that session’s insert. Another connection performing inserts does not replace your session’s value. Failed statements, concurrent allocation, rollbacks, and engine behavior can create gaps, so never use an AUTO_INCREMENT sequence as a row-count or “no records were deleted” proof.

The generated column is omitted from the INSERT because MySQL computes it. The default status='open' and current timestamp are also server-side parts of the row contract.

Multi-row VALUES: one statement, multiple candidate rows

A multi-row INSERT reduces client/server round trips and lets MySQL process several rows in one statement. It is not automatically optimal at every size. Extremely large statements can consume memory, enlarge transactions, hold locks longer, stress logs, or exceed packet/configuration limits. Treat batch size as a measured operational parameter.

INSERT INTO work_orders  (request_key, customer_id, technician_id, status, priority, labor_minutes, parts_cost)VALUES  ('REQ-1002', 2, 1, 'assigned', 2, 25, 8.00),  ('REQ-1003', 3, 2, 'assigned', 3, 55, 30.00),  ('REQ-1004', 1, NULL, 'open', 2, 0, 0.00);SELECT ROW_COUNT() AS rows_changed;SELECT work_order_id, request_key, status, estimated_totalFROM work_ordersORDER BY work_order_id;

ROW_COUNT() should report 3 for the INSERT. The generated totals are computed per row. In production, use a batch size that meets latency, replication, redo, lock-duration, and application-memory goals under your own workload; do not copy an arbitrary “best” batch size.

INSERT ... SELECT: move validated relational rows, not strings

INSERT ... SELECT lets a query produce the rows inserted into a target table. A common use is validate-and-promote: load input into a staging table, validate it, then insert the acceptable subset into the final relational schema.

INSERT INTO work_order_staging  (request_key, customer_id, priority, labor_minutes, parts_cost)VALUES  ('REQ-2001',1,1,35,10.00),  ('REQ-2002',2,2,20,5.00),  ('REQ-2003',999,1,15,1.00);SELECT s.*FROM work_order_staging AS sLEFT JOIN customers AS c ON c.customer_id=s.customer_idWHERE c.customer_id IS NULL;INSERT INTO work_orders  (request_key, customer_id, priority, labor_minutes, parts_cost)SELECT s.request_key, s.customer_id, s.priority, s.labor_minutes, s.parts_costFROM work_order_staging AS sJOIN customers AS c ON c.customer_id=s.customer_idWHERE s.priority BETWEEN 1 AND 3;SELECT ROW_COUNT() AS promoted_rows;

The orphan staging row with customer 999 is visible before promotion and excluded by the inner join. This is stronger than “try the insert and hope a foreign-key error tells us which row was bad”: the validation query is an explicit data-quality step.

Failure case: a duplicate inside a multi-row INSERT

Suppose a batch accidentally repeats the already-used REQ-1001 request key. With normal strict semantics, the unique constraint rejects the statement. For transactional InnoDB tables, the failed statement does not leave the successful-looking rows from that same statement partially inserted.

SELECT COUNT(*) AS before_count FROM work_orders;INSERT INTO work_orders(request_key,customer_id,priority) VALUES ('REQ-3001',1,1), ('REQ-1001',2,2),  -- duplicate unique request key ('REQ-3002',3,3);-- After the error, run this as a separate statement:SELECT COUNT(*) AS after_count FROM work_orders;SELECT request_key FROM work_ordersWHERE request_key IN ('REQ-3001','REQ-3002');

You should receive a duplicate-key error, and neither REQ-3001 nor REQ-3002 should exist. This demonstrates statement atomicity. It does not prove that a surrounding multi-statement transaction was rolled back; Lesson 3 separates those scopes carefully.

Observability: prove what was stored

For data modifications, “Query OK” is only one signal. Use result-state verification plus server metadata. ROW_COUNT() describes the immediately preceding DML statement for the current session. SHOW WARNINGS shows warnings from the prior statement. Information Schema exposes constraints and indexes but does not replace querying the business rows.

SHOW WARNINGS;SELECT CONSTRAINT_NAME, CONSTRAINT_TYPEFROM information_schema.table_constraintsWHERE table_schema='servicehub_write_lab'  AND table_name='work_orders'ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;SHOW INDEX FROM work_orders;SELECT COUNT(*) AS total_work_orders,       MIN(work_order_id) AS min_id,       MAX(work_order_id) AS max_idFROM work_orders;

Do not infer “no gaps” from the MIN/MAX relationship. AUTO_INCREMENT values can be consumed without corresponding committed rows. Verify business invariants directly—for example, unique request keys and valid foreign-key references.

Hands-on lab: design a bounded ingestion unit

1. Reset and seed servicehub_write_lab.
2. Insert one work order and record LAST_INSERT_ID().
3. Insert a three-row batch and verify generated totals.
4. Stage three rows, intentionally include one invalid customer, identify it with a validation query, then promote only valid rows.
5. Run the duplicate-key batch and prove the entire failed statement inserted none of its candidate rows.
6. Repeat with batches of 10 and 100 locally if you want to measure behavior, but record your own timing and environment rather than treating the result as universal.

What InnoDB is doing during an INSERT

An INSERT touches more than the clustered row. InnoDB must find the target location in the clustered primary-key B-tree, maintain every affected secondary index, check unique keys and foreign keys, evaluate CHECK constraints, and record enough transactional information to undo uncommitted work and recover committed work after a crash. Generated-column expressions and defaults are evaluated as part of producing the row that reaches the storage engine.

That helps explain why “one row” can create contention in places the application did not explicitly name. A unique secondary index such as uq_work_orders_request_key must be checked before the row can be accepted. A foreign key can require a lookup and relevant locking in the referenced table. AUTO_INCREMENT allocation has its own concurrency behavior. More indexes improve some reads but increase the work performed by every insert.

Statement atomicity also has an engine cost. If a multi-row statement inserts several candidate records and a later candidate fails, InnoDB must undo effects belonging to that failed statement. This is one reason giant statements are not automatically superior to reasonable bounded batches: a larger failure unit can mean more rollback work, a bigger payload to retransmit, and a longer period before the client can react.

Later chapters examine redo, undo, pages, and buffer-pool behavior in depth. For now, use the mental model that a successful INSERT is a transactional update to a set of related index structures, not merely an append to a text-like table file.

Production judgment and next step

Use multi-row writes to reduce round trips when the business unit allows batching, but bound transaction size and measure the effects on latency, redo volume, lock duration, replicas, memory, and failure recovery. Keep duplicate behavior explicit—reject, upsert, or deduplicate by a business key—and make application-generated idempotency keys separate from AUTO_INCREMENT identity.

Lesson 2 moves from creating rows to changing and deleting them, where an incomplete predicate can be more dangerous than a failed insert.

Authoritative references

• MySQL 8.4 Reference Manual — INSERT Statement
• MySQL 8.4 Reference Manual — InnoDB Error Handling
• MySQL 8.4 Reference Manual — AUTO_INCREMENT Handling in InnoDB
• MySQL 8.4 Reference Manual — LAST_INSERT_ID()
• MySQL 8.4 Reference Manual — SHOW WARNINGS