How to do a batch insert in MySQL

sql

batch-insert

mysql-performance

data-insertion

byAnton Shumikhin·Dec 7, 2024

Efficiently achieve a batch insert in MySQL by using a single INSERT INTO statement containing multiple rows. This strategy helps to reduce server round trips, therefore, enhancing performance. Here's how to do it:

--Inserting three new talented developers to our team in a single go.
INSERT INTO table_name (dev_name, skill) VALUES ('John', 'Python'), ('Emma', 'Java'), ('Tom', 'Node.js');
--Because we don't like slowly hiring one by one!

You can replace table_name, dev_name, and skill with your actual table and column names and replace 'John', 'Python', etc., with the data you want to insert. This approach allows for the swift insertion of multiple rows, making it far more efficient than inserting each row one by one.

Dealing with large data

When handling hefty datasets, remember:

LOAD DATA INFILE can be your best friend for bulk inserts, especially when the data counts in millions. Just be careful about file path and permission issues.
If you are hitting query size limits or experiencing performance degradation, split your inserts into smaller chunks.
Be mindful of the max_allowed_packet configuration in order to keep your query size in check. Sometimes MySQL verifies size and suddenly decides to take a nap during a large query.

Use INSERT INTO ... SELECT syntax if you feel lazy and want to copy an existing dataset within your database.
If you are using JDBC, set autoCommit to false and use PreparedStatement.addBatch() for the batch insert. So you can do all or nothing(just joking) using executeBatch() and commit().
Be aware of any restrictions from your hosting provider or DBMS when performing batch inserts in tightly controlled environments.

Striking the right balance

Aim for the optimal balance between speed and system stability:

Test different insert sizes to find one that meets the performance demands of your infrastructure. -** Error handling**: Make sure your awesome coding skills aren't overshadowed by a lack of robust error handling.
Watch out for indexing strategies. While indexes are great for data retrieval, they might leave you hanging during data insertion, a kind of ‘love-hate’ relationship.

Best practices

Follow these benchmarks for batch operations:

Test your batch insert operation thoroughly before going live.
Schedule regular backups before performing batch operations to prevent data loss.
In cases where SQL feels left out, consider using API batching supported by most ORMs and SQL environments.

explain-codes / Sql / How to do a batch insert in MySQL

Linked

Mysql: How to copy rows, but change a few fields?



Mysql INSERT INTO table VALUES.. vs INSERT INTO table SET



C# SQL Server - Passing a list to a stored procedure



Preparedstatement IN clause alternatives?



Python pandas to_sql with sqlalchemy: how to speed up exporting to MS SQL?

