How can I perform BULK DATA INSERTION in MySQL?

BULK DATA INSERTION in MySQL

In Microsoft SQL Server, the BULK INSERT command efficiently imports data from a text file into a database table. However, in MySQL, a different approach is required.

Equivalent MySQL Command: LOAD DATA INFILE

MySQL provides the LOAD DATA INFILE statement to perform bulk data insertion from a file. This command works similarly to BULK INSERT, allowing you to specify the source file path and table destination, as well as the data formatting details.

Syntax:

LOAD DATA INFILE '<file_path>'
INTO TABLE <table_name>
FIELDS TERMINATED BY '<delimiter>'</delimiter></table_name></file_path>

Parameters:

• file_path: The full path to the text file containing the data to be imported.
• table_name: The destination table in the database.
• delimiter: The character(s) separating the data fields in the file.

Example:

To insert data from the file "C:MyTextFile.txt" into the table "MyTable" in the database "myDatabase", using a comma (",") as the field delimiter:

LOAD DATA INFILE 'C:\MyTextFile.txt'
INTO TABLE myDatabase.MyTable
FIELDS TERMINATED BY ','

Additional Notes:

• The file can be in text, CSV, or other supported formats.
• You can specify additional parameters in the LOAD DATA INFILE command, such as IGNORE LINES (to skip header rows) or REPLACE (to overwrite existing data).
• For more detailed information and configuration options, refer to the MySQL documentation on LOAD DATA INFILE: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

The above is the detailed content of How can I perform BULK DATA INSERTION in MySQL?. For more information, please follow other related articles on the PHP Chinese website!