intro
How does LOAD DATA work for MySQL? Read this article and find out everything you need to know about data import with the LOAD DATA INFILE MySQL statement.
One of the most common tasks when working with a new database is populating it with data. Sometimes, generating sample data is enough. Other times, you already have a file or another source where the data is stored, and you want to import it into your MySQL server. This is where LOAD DATA comes in!
At the end of this guide, you will know how to answer: How does LOAD DATA work for MySQL? You will also know what that statement is and how to use it.
What Is LOAD DATA in MySQL?
LOAD DATA is a MySQL statement to efficiently import rows from a text file into a table. The file can be located either on the server or on the client machine. Depending on the file's location, the way MySQL interprets the data and handles errors may differ.
Keep in mind that the MySQL LOAD DATA statement complements the SELECT ... INTO OUTFILE instruction. Specifically, you can use SELECT ... INTO OUTFILE to export table data to a file, and then use LOAD DATA to import that data back into a table.
Thus, LOAD DATA is a powerful tool for data backup, transfer, and bulk data loading operations.
How to Use the MySQL LOAD DATA Statement
This is the simplified syntax of LOAD DATA in MySQL:
1
LOAD DATA
2
[LOW_PRIORITY | CONCURRENT] [LOCAL]
3
INFILE 'file_path'
4
INTO TABLE table_name
5
[CHARACTER SET charset_name]
6
[FIELDS
7
[TERMINATED BY 'string']
8
[ENCLOSED BY 'char']
9
[ESCAPED BY 'char']]
10
[LINES
11
[STARTING BY 'string']
12
[TERMINATED BY 'string']]
13
[IGNORE number LINES]
14
[(column1, column2, ...)]
Where:
Keep in mind that the possible values for FIELDS are:
Then, the possible values for LINES are:
For the complete syntax with all possible options, refer to the official documentation.
Notes:
How Does LOAD DATA Work for MySQL?
To really appreciate the performance benefits of LOAD DATA for importing data, it helps to understand how the statement works under the hood. As the official MySQL documentation notes:
“When loading a table from a text file, use LOAD DATA. This is usually 20 times faster than using INSERT statements.”
There are at least six reasons why the LOAD DATA INFILE MySQL statement is so efficient. Analyze them all!
1. Bulk Reading From a File
Instead of executing one INSERT per row, LOAD DATA reads the entire file (like a CSV or TSV) as a stream and parses it line by line. This is much faster because:
2. Reduced Network Overhead
When using LOAD DATA LOCAL, the client sends the file contents to the server in large chunks. That reduces the number of round-trips needed compared to sending many INSERT queries.
3. Minimal Parsing Overhead
When reading data from a text file, MySQL utilizes lightweight tokenization based on simple delimiters (like commas or tabs) and escape sequences. This avoids the SQL parsing overhead you would get from individual INSERT statements.
4. Faster Transaction Handling
If you are not using autocommit, MySQL can wrap the entire import in a single transaction. That means the server will execute only one commit at the end, instead of committing every row or batch individually. Again, this leads to a performance benefit.
5. Index and Constraint Optimizations
MySQL defers certain operations—like updating indexes or enforcing constraints (e.g., uniqueness, foreign keys)—until the full batch of data is loaded. This is far more efficient than checking those constraints row-by-row.
To speed things up even more, you can even temporarily disable foreign key checks and unique constraints with:
1
SET foreign_key_checks = 0;
2
SET unique_checks = 0;
Do not forget to re-enable them after the import operation if necessary.
6. File-Based Efficiency
When LOAD DATA reads from a file on the server, the data never touches the network. This means the data is streamed directly from the disk into memory and into the storage engine, maximizing the throughput.
Importing Data in MySQL With LOAD DATA: 2 Approaches
Let’s explore two different ways to use the LOAD DATA INFILE MySQL statement to achieve the same goal: loading data into a MySQL database!
Suppose you have the following employees table:
That is currently empty, but you want to populate it with data from a CSV file containing the following information:
1
id,name,position,salary
2
1,Jane Doe,Engineer,75000
3
2,John Smith,Manager,90000
4
3,Alice Lee,Designer,67000
The goal is to use the MySQL LOAD DATA INFILE statement to import this data into the table. See how you can do it!
Approach #1: Using a MySQL LOAD DATA INFILE Statement
The first approach involves using LOAD DATA directly from the MySQL server. To start, connect to your MySQL server and place your CSV file in the directory /var/lib/mysql-files/ (or another folder of your choice) adhering to the configuration outlined in my.cnf.
Note: /var/lib/mysql-files/ is the default directory allowed by MySQL for LOAD DATA INFILE. You can configure this directory using the secure_file_priv setting in the MySQL configuration file.
Once your file is in the correct location, run the following LOAD DATA statement in the MySQL CLI:
1
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
2
INTO TABLE employees
3
FIELDS TERMINATED BY ','
4
LINES TERMINATED BY '\n'
5
IGNORE 1 LINES;
This will populate the employees table with the data from the CSV file. If you now query the employees table using a MySQL database client like DbVisualizer, you will see the data has been imported successfully:
Fantastic! The data have been imported as required.
Approach #2: Using a Visual Database Client
Connecting to a MySQL server via the CLI can be risky and may require technical knowledge that you might not have. The easiest way to import data into MySQL using the LOAD DATA statement is by using a powerful database client with visual importing options, like DbVisualizer.
Download DbVisualizer for free and install it. After installation, set up your MySQL connection and connect to the database. Then, navigate to the “Databases” section, open the “Tables” dropdown, find the employees table, and right-click on it.
Select the “Import Table Data > Load Table Data...” option from the context menu:
This will open a modal window where you can visually customize your MySQL LOAD DATA INFILE query. To import the employees.csv data into the employees table, configure the import settings like this:
Note that the resulting LOAD DATA MySQL query is:
1
SET GLOBAL local_infile=ON;
2
LOAD DATA LOCAL INFILE '\employees.csv'
3
INTO TABLE `company`.`employees`
4
FIELDS TERMINATED BY ','
5
LINES TERMINATED BY '\n'
6
IGNORE 1 LINES;
Press the “Execute” button, and the data from the CSV will be imported into the employees table as expected. After the import, the table will contain the data from the CSV:
Amazing! Thanks to DbVisualizer, you were able to import data using LOAD DATA without manually running any queries. This is a much more user-friendly way to perform this task!
Note: The table data import feature is only available in DbVisualizer Pro. Grab a 21-day free trial to test all the advanced features!
Conclusion
In this article, you learned what you need to know to answer the question, "How does LOAD DATA work for MySQL?" In particular, you now understand that the LOAD DATA INFILE MySQL statement allows you to import bulk data from text files.
As shown here, you can execute it manually on the server, but the data import process becomes much easier with a powerful, feature-rich, multi-database client like DbVisualizer. This advanced tool also offers features like query optimization, SQL formatting, and schema visualization with ERD-like diagrams. Try DbVisualizer for free today!
FAQ
How does LOAD DATA work for MySQL?
The LOAD DATA INFILE MySQL statement is an efficient way to import large datasets from a text file (e.g., TXT or CSV). It reads the file in bulk, parsing it line-by-line, and loads the data directly into a table for optimal performance.
How to deal with the “Loading local data is disabled; this must be enabled on both the client and server sides” error?
Some possible solutions to fix the error are:
1
mysql --local-infile=1 -u your_username -p
1
[mysqld]
2
local_infile = 1
Then, restart the MySQL server after modification.
1
SET GLOBAL local_infile = 1;
1
jdbc:mysql://localhost:3306/database_name?allowLoadLocalInfile=true
What is the difference between LOAD DATA and a list of INSERTs in MySQL
The key difference between LOAD DATA and multiple INSERT statements in MySQL is performance. The MYSQL LOAD DATA INFILE statement is much faster for importing large datasets because it reads a text file in bulk, minimizing network overhead and parsing time. It executes the import in a single operation, while INSERT executes one statement per row, which adds overhead with each insert.
What is the difference between LOAD DATA and an INSERT with multiple VALUES in MySQL?
The difference between LOAD DATA and INSERT with multiple VALUES lies in efficiency. LOAD DATA reads data from a file in bulk, making it faster for large imports, while INSERT with multiple VALUES still requires parsing and executing each row individually.
Why use a visual database client for MySQL data import?
Embracing a visual database client like DbVisualizer for data import offers several advantages. It provides an intuitive, user-friendly interface, making the data table import process much easier than manually running queries. With DbVisualizer, you can visually configure the data import, reducing the risk of errors and enabling even non-technical users to interact safely with your databases. Test it out and simplify your database management!
