How to do a batch insert in MySQL

Performing bulk insert in MySQL is a very common and useful operation, especially when you need to quickly insert large volumes of data into the database. Bulk insert can significantly improve data insertion efficiency by reducing the number of network communications and SQL parsing operations. Below are the steps for performing bulk insert in MySQL along with some best practices:

Step 1: Using the INSERT Statement for Bulk Insert

The basic syntax for bulk insert is as follows:

sql
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(valueA1, valueA2, valueA3, ...),
(valueB1, valueB2, valueB3, ...),
(valueC1, valueC2, valueC3, ...),
...;

Here, you can add multiple sets of parentheses after VALUES, each containing the values for a single row to be inserted.

Example

Suppose we have a table named students with three columns: id, name, and age. We need to insert multiple student records:

sql
INSERT INTO students (id, name, age)
VALUES
(1, 'Alice', 21),
(2, 'Bob', 22),
(3, 'Charlie', 23);

This SQL statement inserts three records into the students table in a single operation.

Step 2: Be Aware of the Maximum Allowed Packet Size

When performing bulk data insertion, pay attention to the max_allowed_packet parameter in MySQL configuration. This parameter defines the maximum size of data packets transmitted between the client and server. If your bulk insert operation exceeds this size, MySQL will return an error. You can check the current value of max_allowed_packet using the following command:

sql
SHOW VARIABLES LIKE 'max_allowed_packet';

If necessary, you can increase this value in the MySQL configuration file or set it temporarily in the session:

sql
SET GLOBAL max_allowed_packet=104857600;  -- Set to 100MB

Step 3: Using Transactions

If your bulk insert operation is critical and you need to ensure data integrity, use transactions to guarantee that all data is inserted successfully or rolled back entirely if an error occurs. For example:

sql
START TRANSACTION;
INSERT INTO students (id, name, age) VALUES (4, 'David', 24), (5, 'Eve', 25);
COMMIT;

This ensures that both records are inserted successfully or neither is inserted if an error occurs.

Conclusion

Bulk insert is a highly effective operation, but you must consider packet size limitations and transaction usage. By optimizing these aspects, you can enhance database operation efficiency while ensuring data security. This technique is frequently used in scenarios such as data migration, logging, or bulk data processing.