How to Bulk Upload CSV file data into MySql Table? A very fast way using LOAD DATA.

😄 Introduction

Are you still using "for" or "while" loops to iterate over your rows and insert them in your database? Do you still writing separate code for reading .csv file and the uploading it to your MySQL database?

Say "NO" to linear logic with the "LOAD DATA" statement provided by the MySQL. Prepare yourself for changing your code to both minimize the lines, and increase the performance drastically.

🗃 What is the LOAD DATA INFILE Statement?

MySql provides the LOAD DATA statement, which is a high-speed way to read the data from text files and insert it into table with very minimal code.

As stated in the MySQL documentation, the LOAD DATA statement is used to Read Data from the file in a quick manner and you can insert this data in your table with one single query, instead of hitting your db multiple times with "INSERT INTO" query.

MySQL also provides a statement -> "SELECT…INTO OUTFILE", which does exactly opposite of what "LOAD DATA" does, which is to read the data from the table to file.

mysqlimport utility provided by MySql internally calls the LOAD DATA statement on the server to import the data.

🧐 How to Use it?

Above gist snippet shows the generic load data statements. All the available options are mentioned in the code above.

Let's now focus on a simple example of reading a .csv file and inserting its rows into a compatible table.

A simple example :

LOAD DATA INFILE ‘data.csv’ INTO TABLE db.my_table

A birds-eye example with all essential options :

Needless to say that your CSV file should be properly formatted for this statement to work😉.

And voila, using the above statement, your .csv with more than 100 thousands rows will be imported into your table in seconds.

Now, some important things to note:

The above statement runs at the MySql level, hence the source files are copied from client to server in order to import them.
This raises some security issues, we need to make sure the client-server connection is secured while using this code.

In MySql 8.0, the capability to use LOCAL is set as False by default. As your server and client should be configured to have LOCAL permited, some of you might get a permission error.

In case of permission error, we need to override it by enabling the local_infile:

SET GLOBAL local_infile = true;

Note: Overriding this flag is not a security solution but rather an acknowledgement for accepting the risks, you can refer this documentation for more information.

🐱‍🚀 Conclusion

• MySql LOAD DATA statement is used to read files within very less time
• You can use this code with any backend API to provide the source file

• Source file can be any text file, we have used .csv in this example

• LOAD DATA LOCAL copies the source file to your server via MySql hence a security measures on the server side should be implemented

• mysqlimport utility uses the LOAD DATA statement internally

• You can ignore the headers in CSV by adding IGNORE 1 LINES in the statement

📚 Reference

Official Docs