How to Bulk Upload CSV file data into MySql Table? A very fast way using LOAD DATA.-mysql教程-PHP中文網

? 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.

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

? What is the LOAD DATA INFILE Statement?

As per the official docs of MySQL states, the LOAD DATA statement is used to Read Data from the file in a very fast 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 datafrom 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.

Lets now focus on a simple example of simply reading a .csv file and inserting it's 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 thelocal_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 LOCALcopies the source file to your server via MySql hence a security measures on the server side should be implemented.

mysqlimportutility uses the LOAD DATA statement internally.

You can ignore the headers in CSV by addingIGNORE 1 LINESin the statement.

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