A bulk insert in MySQL is when you need to insert a lot of records in a table at one time. It can be used to quickly create data for testing. It is tiresome to rewrite the insert statement for every row. Here’s how you can bulk insert in MySQL.
Bulk Insert in MySQL
Syntax
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( a1, a2,...aN ),( b1, b2,...bN ),..;
a1, a2, a3,.. – values for field1
b1, b2, b3,.. – values for field2
c1, c2, c3,.. – values for field3
…
Example
For a table table_name with columns a, b, c
mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(3,3,3); +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | | 7 | 8 | 9 | | 3 | 3 | 3 | +------+------+------+
As you can see, bulk insert in MySQL allows duplicate rows/columns. It may cause duplicate primary keys. Here’s how you can avoid it.
Bulk insert in MySQL without duplicates
We use ON DUPLICATE KEY clause. It detects duplicate values of primary key. We also add an UPDATE clause to update the duplicate value.
Syntax
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( a1, a2,...aN ),( b1, b2,...bN ),..
ON DUPLICATE KEY
UPDATE primary_key_field=<expression> ;
a1, a2, a3,.. – values for field1
b1, b2, b3,.. – values for field2
c1, c2, c3,.. – values for field3
…
Example
For a table table_name with columns a, b, c and c is the primary key
mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(3,3,3) ON DUPLICATE KEY UPDATE c=c+1; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | | 7 | 8 | 9 | | 3 | 3 | 4 | +------+------+------+
You can also update value based on other columns
mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(1,2,3) ON DUPLICATE KEY UPDATE c=value(a)+value(b); +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | | 7 | 8 | 9 | | 3 | 3 | 6 | +------+------+------+
