MySQL Insert Multiple Rows: One INSERT with Many VALUES

You can insert many rows with one statement by giving several value lists:

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL
);

query.sql

SQL

INSERT INTO products (name, category, price) VALUES
    ('Notebook',   'stationery',  80.00),
    ('Gel Pen',    'stationery',  15.50),
    ('Backpack',   'bags',       999.00),
    ('Desk Lamp',  'home',       650.00);

Output

OUTPUT

1
2

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

The line Records: 4 Duplicates: 0 Warnings: 0 is a summary of the multi-row insert: 4 rows offered, none duplicate, no warnings.

query.sql

SQL

SELECT * FROM products;

Output

OUTPUT

+----+-----------+------------+--------+
| id | name      | category   | price  |
+----+-----------+------------+--------+
|  1 | Notebook  | stationery |  80.00 |
|  2 | Gel Pen   | stationery |  15.50 |
|  3 | Backpack  | bags       | 999.00 |
|  4 | Desk Lamp | home       | 650.00 |
+----+-----------+------------+--------+
4 rows in set

Why batch?

Each statement has a cost: the round trip over the network, parsing, planning and a commit. One statement with 100 rows is much faster than 100 statements with one row each. In many systems, batching gives 10 to 50 times better insert speed.

Rows	How	Rough effect
1	One statement per row, autocommit on	Slowest. Each row is its own transaction.
100 to 1000	One multi-row `INSERT`	Fast. A good batch size.
Millions	`LOAD DATA` or batches inside transactions	Fastest (LOAD DATA)

How big should a batch be?

A statement cannot be larger than max_allowed_packet (64 MB by default in MySQL 8). Go for 500 to 5,000 rows per statement, depending on row size. Very large statements use a lot of memory and hold locks longer.

query.sql

SQL

SELECT @@max_allowed_packet / 1024 / 1024 AS max_packet_mb;

Output

OUTPUT

+---------------+
| max_packet_mb |
+---------------+
|   64.00000000 |
+---------------+
1 row in set

It is all or nothing

A multi-row INSERT is a single statement. If one row is invalid, in strict mode the whole statement fails and nothing is inserted (for InnoDB tables):

query.sql

SQL

INSERT INTO products (name, category, price) VALUES
    ('Marker Set', 'stationery', 145.00),
    ('Broken Row', 'home', 'not a number');
SELECT COUNT(*) AS rows_in_table FROM products;

Output

OUTPUT

ERROR 1366 (HY000): Incorrect decimal value: 'not a number' for column 'price' at row 2

+---------------+
| rows_in_table |
+---------------+
|             4 |
+---------------+
1 row in set

The count is still 4: the valid Marker Set row was also rolled back. This is good: you will never have half a batch.

Combine many statements in one transaction

If you cannot use one statement, group the inserts into one transaction, so that the commit cost is paid once (Transactions):

query.sql

SQL

START TRANSACTION;
INSERT INTO products (name, category, price) VALUES ('Water Bottle', 'home', 220.00);
INSERT INTO products (name, category, price) VALUES ('Laptop Sleeve', 'bags', 450.00);
COMMIT;
SELECT COUNT(*) AS rows_in_table FROM products;

Output

OUTPUT

+---------------+
| rows_in_table |
+---------------+
|             6 |
+---------------+
1 row in set

From application code

Build one statement with many placeholders, then bind all the values at once:

query.sql

SQL

INSERT INTO products (name, category, price) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)

Never join user data into the string yourself (SQL Injection). The PHP chapter shows batching with PDO (PDO Insert).

lightbulb

Batch size experiment

Time your own import with 100, 1,000 and 5,000 rows per statement. The best size depends on your row width and your server, and the gain flattens quickly above a few thousand rows.