How do I perform a bulk insert in MySQL?

Bulk Inserting Data in MySQL

Inserting large volumes of data into a MySQL database can be a daunting task. On Microsoft SQL Server, you can perform bulk inserts using the BULK INSERT command. However, MySQL offers its own unique approach for this operation using the LOAD DATA INFILE statement.

LOAD DATA INFILE

The LOAD DATA INFILE statement allows you to import data from a text file or an external data source directly into a MySQL table. Its syntax is as follows:

LOAD DATA INFILE <file_path>
INTO TABLE <table>
FIELDS TERMINATED BY <delimiter>
[LINES TERMINATED BY <line_delimiter>]
[(field_options)]<p>Where:</p>
<ul>
<li><file_path>: The path to the input text file.</file_path></li>
<li><table>: The name of the target MySQL table.<li><delimiter>: The field delimiter character in the input file.</delimiter></li>
<li><line_delimiter>: The optional line delimiter character in the input file.</line_delimiter></li>
<li>[field_options]: Additional options for handling specific columns, such as IGNORE or NULL.</li>
<p><strong>Example:</strong></p>
<p>To perform a bulk insert into a MySQL table called MyTable from a text file located at C:MyTextFile.txt with commas (,) as the field delimiter, you would use the following query:</p>
<pre class="brush:php;toolbar:false">LOAD DATA INFILE 'C:\MyTextFile.txt'
INTO TABLE MyTable
FIELDS TERMINATED BY ','

By using the LOAD DATA INFILE statement, you can efficiently import large amounts of data into your MySQL tables without requiring complex SQL commands or database modifications.

The above is the detailed content of How do I perform a bulk insert in MySQL?. For more information, please follow other related articles on the PHP Chinese website!