How do I perform a bulk insert in MySQL?-Mysql Tutorial-php.cn

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)]

Copy after login

Where:

: The path to the input text file.

: The name of the target MySQL table.

: The field delimiter character in the input file.

: The optional line delimiter character in the input file.

[field_options]: Additional options for handling specific columns, such as IGNORE or NULL.

Example:

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:

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

Copy after login

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!

source：php.cn

Previous article：Is mysql_real_escape_string Sufficient to Prevent SQL Injection? Next article：Is mysql_real_escape_string Truly Effective Against SQL Injection, and What Are Its Limitations?

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn