Efficiently Insert Multiple Rows in MySQL – Beginner Guide

1. Introduction

• Want to use INSERT statements efficiently
• Want to reduce data insertion time
• Want to learn how to handle large datasets

2. Basic INSERT Statement Syntax

Basic INSERT Statement Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• Table name: The name of the table into which data will be inserted.
• column1, column2, …: Specify the column names in the table that will hold the inserted values.
• value1, value2, …: Specify the values corresponding to each column.

Example: Inserting Customer Information

INSERT INTO customers (id, name, email)
VALUES (2, 'Hanako Tanaka', 'hanako@example.com');

How to Omit Column Specification

INSERT INTO customers
VALUES (3, 'Ichiro Suzuki', 'ichiro@example.com');

Things to Note

• Data type matching: The data types of the values being inserted must match the column data types defined in the table.
• Handling NULL values: If a column allows NULL, you can insert NULL without specifying a value.
• Default values: For columns with a defined default value, that default is inserted when no value is specified.

Summary

3. How to Insert Multiple Rows of Data at Once

Basic Syntax for Multi-Row Inserts

INSERT INTO table_name (column1, column2, ...)
VALUES
(value1_1, value1_2, ...),
(value2_1, value2_2, ...),
(value3_1, value3_2, ...);

• Enclose each row’s data in parentheses and separate rows with commas.
• The VALUES clause is written only once.

Basic Example: Inserting Multiple Customer Records

INSERT INTO customers (id, name, email)
VALUES
(4, 'Makoto Kato', 'makoto@example.com'),
(5, 'Sakura Mori', 'sakura@example.com'),
(6, 'Kei Tanaka', 'kei@example.com');

Efficiency Benefits

• Reduced Network Load: Inserting multiple rows with a single SQL statement reduces the number of communications with the server.
• Speed Improvement: Because the insert operation occurs only once, processing becomes more efficient.

Things to Watch Out For

1. Column count must match the number of values

• Example: If there are three columns, each row must also have three values, otherwise an error occurs.

1. Matching Data Types

• Each value’s data type must match the column’s data type defined in the table.

1. Avoiding Duplicate Errors

• If there are primary key or unique key constraints, attempting to insert data with a duplicate key will cause an error.

Error‑Handling Trick: IGNORE Option

INSERT IGNORE INTO customers (id, name, email)
VALUES
(7, 'Ryoichi Suzuki', 'ryoichi@example.com'),
(5, 'Duplicate User', 'duplicate@example.com'); -- This row will be ignored

Summary

4. Bulk Insertion Method for Large Data Sets

LOAD DATA INFILE Basic Syntax

LOAD DATA INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ',' -- field delimiter
LINES TERMINATED BY '\n' -- line delimiter
(column1, column2, ...);

• INFILE: Specifies the path to the file containing the data to be loaded.
• FIELDS TERMINATED BY: Specifies the delimiter for each field (column) (e.g., comma “,”).
• LINES TERMINATED BY: Specifies the line delimiter (e.g., newline “\n”).
• (column1, column2, ...): Specifies the columns into which data will be inserted.

Basic Example: Inserting Data from a CSV File

4,Makoto Kato,makoto@example.com
5,Sakura Mori,sakura@example.com
6,Kei Tanaka,kei@example.com

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email);

Using the LOCAL Option

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email);

Performance Optimization Tips

1. Using Transactions

• Performing the load within a transaction allows you to roll back in case of errors.

   START TRANSACTION;
   LOAD DATA INFILE '/path/to/data.csv' INTO TABLE customers;
   COMMIT;

2. Temporarily Disabling Indexes

• Disabling indexes before the load and re-enabling them afterward speeds up the operation.

   ALTER TABLE customers DISABLE KEYS;
   LOAD DATA INFILE '/path/to/data.csv' INTO TABLE customers;
   ALTER TABLE customers ENABLE KEYS;

3. Processing Data with a SET Clause

• You can transform data before loading:

   LOAD DATA INFILE '/path/to/data.csv'
   INTO TABLE customers
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   (id, name, @email)
   SET email = LOWER(@email);

Considerations

• File Permissions: To use LOAD DATA INFILE, the MySQL server must have permission to access the file.
• Security: When using the LOCAL option, you must take adequate measures against external attacks.

Summary

5. Performance Optimization Tips

Using Transactions

START TRANSACTION;

INSERT INTO customers (id, name, email)
VALUES (7, 'Haruto Sato', 'haruto@example.com'),
       (8, 'Yuki Aoki', 'yuki@example.com');

COMMIT;

• Execute multiple INSERT statements within a transaction and commit them all at once to reduce disk I/O.
• If an error occurs, you can roll back the entire operation with ROLLBACK.

Temporarily Disabling Indexes

ALTER TABLE customers DISABLE KEYS;

INSERT INTO customers (id, name, email)
VALUES (9, 'Kaori Tanaka', 'kaori@example.com'),
       (10, 'Shota Yamada', 'shota@example.com');

ALTER TABLE customers ENABLE KEYS;

• This technique is effective when bulk inserting large amounts of data.
• Only secondary indexes can be disabled; primary keys cannot be affected.

Leveraging Batch Processing

-- Insert 100 rows per INSERT
INSERT INTO customers (id, name, email)
VALUES
(11, 'Hiroshi Kato', 'hiroshi@example.com'),
(12, 'Miku Yamamoto', 'miku@example.com'),
... -- Add 98 more rows
(110, 'Rina Suzuki', 'rina@example.com');

• Adjust the batch size (e.g., 100 rows or 1,000 rows) to keep server load under control.
• Pay attention to log size and server configuration.

Adjusting Buffer Sizes and Settings

• innodb_buffer_pool_size: Increase the size to manage data more efficiently in memory.
• bulk_insert_buffer_size: Expand the buffer size used during bulk inserts.

[mysqld]
innodb_buffer_pool_size=1G
bulk_insert_buffer_size=512M

Summary

1. Use transactions for efficiency.
2. Disable indexes to boost insertion speed.
3. Employ batch processing to distribute load.
4. Adjust server settings to extract maximum performance.

6. Differences from Other Databases

Comparison of MySQL and PostgreSQL

1. Syntax for Multi-Row Insertion

• MySQL and PostgreSQL can generally use the same syntax.

INSERT INTO customers (id, name, email)
VALUES
(1, 'Taro Yamada', 'taro@example.com'),
(2, 'Hanako Tanaka', 'hanako@example.com');

INSERT INTO customers (id, name, email)
VALUES
(1, 'Taro Yamada', 'taro@example.com'),
(2, 'Hanako Tanaka', 'hanako@example.com');

• In PostgreSQL, you can add a RETURNING clause to retrieve the inserted data.

INSERT INTO customers (id, name, email)
VALUES
(3, 'Sakura Mori', 'sakura@example.com')
RETURNING *;

2. Transaction Handling

• Both support transactions, but PostgreSQL has stricter default settings for transaction isolation levels and data integrity.

Comparison of MySQL and Oracle

1. Method for Multi-Row Insertion

INSERT INTO customers (id, name, email)
VALUES
(1, 'Taro Yamada', 'taro@example.com'),
(2, 'Hanako Tanaka', 'hanako@example.com');

INSERT ALL
  INTO customers (id, name, email) VALUES (1, 'Taro Yamada', 'taro@example.com')
  INTO customers (id, name, email) VALUES (2, 'Hanako Tanaka', 'hanako@example.com')
SELECT * FROM dual;

• MySQL inserts multiple rows with a single VALUES clause, whereas Oracle uses the INSERT ALL syntax to insert rows individually.
• Oracle may require a special virtual table called dual.

Other Differences

1. Data Type Differences

• MySQL commonly uses TEXT and BLOB types, while Oracle and PostgreSQL use different types such as CLOB and BYTEA.
• You need to be aware of data type differences when inserting.

2. Error Handling

• In MySQL, you can use the IGNORE option to ignore errors.

INSERT IGNORE INTO customers (id, name, email)
VALUES (1, 'Duplicate User', 'duplicate@example.com');

• In PostgreSQL and Oracle, error handling uses dedicated exception mechanisms (e.g., EXCEPTION or SAVEPOINT).

3. Bulk Insertion Methods

• MySQL offers LOAD DATA INFILE, while PostgreSQL uses the COPY command and Oracle uses a tool called SQL*Loader.

Summary

7. FAQ

Q1: An error occurred when inserting multiple rows. How should I debug it?

1. Data type consistency

• Ensure that the values you insert into each column match the data types defined in the table.
• Example: make sure you aren’t inserting only numbers into a VARCHAR column.

1. Number of values matches number of columns

   INSERT INTO customers (id, name, email)
   VALUES
   (1, 'Taro Yamada'), -- Error: missing email value
   (2, 'Hanako Tanaka', 'hanako@example.com');

3. Constraint violation

• An error occurs if primary key or unique key constraints are not satisfied.
• Solution: use INSERT IGNORE or ON DUPLICATE KEY UPDATE to avoid errors.

Q2: What security considerations should be taken when using LOAD DATA INFILE?

1. File access permissions

• Configure appropriate paths and permissions so that the MySQL server can access the file.
• Check the SECURE_FILE_PRIV directory setting and use only files within the allowed directory.

1. Risks of the LOCAL option

• When using LOAD DATA LOCAL INFILE, use it only between trusted client and server to prevent malicious file reads from a remote server.

1. Data validation

• Validate the data in the file beforehand to ensure no invalid data is present.

Q3: What causes performance degradation when inserting large amounts of data?

1. Index updates

• Updating indexes during insertion slows down processing.
• Mitigation: disable indexes before insertion and re-enable them afterward.

1. Transaction log

• If each insert operation is committed individually, disk I/O increases and processing slows down.
• Mitigation: use transactions and commit in bulk.

1. Insufficient buffer settings

• If settings like innodb_buffer_pool_size or bulk_insert_buffer_size are too small, data insertion can be slow.
• Mitigation: adjust the settings to allocate sufficient memory.

Q4: If existing data is present, can multiple-row inserts be performed safely?

1. Using ON DUPLICATE KEY UPDATE

   INSERT INTO customers (id, name, email)
   VALUES (1, 'Updated Name', 'updated@example.com')
   ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

2. Using REPLACE INTO

   REPLACE INTO customers (id, name, email)
   VALUES (1, 'Replaced Name', 'replaced@example.com');

Q5: What is the optimal size for batch processing?

• Server memory and CPU performance.
• Table structure (presence of indexes, constraints).
• Data volume and record size.

Summary

8. Summary

Key Points

1. Basic INSERT Statement Syntax

• Inserting a single row is a fundamental MySQL operation, and matching data types and columns is important.

1. How to Insert Multiple Rows at Once

• Inserting multiple rows in a single SQL statement reduces network overhead and improves performance.

1. Bulk Insertion of Large Datasets

• Using LOAD DATA INFILE allows efficient insertion of massive data sets, though security and configuration considerations are required.

1. Performance Optimization Tips

• We covered various methods to improve insert efficiency, such as using transactions, disabling indexes, batch processing, and tuning server settings.

1. Differences Compared to Other Databases

• MySQL’s insert methods are simpler compared to PostgreSQL or Oracle, but it’s important to understand techniques that suit each database’s characteristics.

1. FAQ

• We provided concrete answers to common questions and error solutions, offering information useful for real-world tasks.

Conclusion

• Run the SQL statements presented in the article to verify their behavior.
• Choose an insert method suited to your project and attempt performance optimization.
• Consult the official MySQL documentation and related books for deeper knowledge.