SQL INFILE

What is SQL INFILE?

INFILE specifies the source file path in a LOAD DATA or LOAD XML statement to bulk-import data into a table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INFILE: MySQL, MariaDB (full support). Other systems use COPY (PostgreSQL), BULK INSERT (SQL Server), or SQL*Loader (Oracle) instead and do not support the INFILE keyword directly.

SQL INFILE Full Explanation

INFILE is a MySQL and MariaDB keyword that appears inside the bulk-loading commands LOAD DATA and LOAD XML. It tells the server where to find the external text or XML file that contains rows to be inserted. When the optional LOCAL modifier precedes INFILE, the client, rather than the server, reads the file and streams its contents to the server—useful when the database server cannot access the file system directly. Without LOCAL, the file must reside on the database server host and be readable by the MySQL/MariaDB process.Important caveats:- File path must be quoted and use the server’s file-system conventions.- The server requires the FILE privilege (or the secure_file_priv directory restriction) when LOCAL is not used.- INFILE is case-insensitive but conventionally written in uppercase.- Performance is significantly faster than multiple INSERT statements because data is parsed in bulk.- Security configurations such as --local_infile flag or the server variable local_infile may disable LOCAL INFILE for safety.

SQL INFILE Syntax

LOAD DATA [LOCAL] INFILE 'file_path'
INTO TABLE table_name
[FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"']
[LINES TERMINATED BY '\n']
[IGNORE n LINES];

SQL INFILE Parameters

file_path (string) - Absolute or relative path to the file containing data.
LOCAL (keyword) - Optional keyword that forces the client to read the file and send it to the server.

Example Queries Using SQL INFILE

-- Import a CSV file located on the server
LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- Import from client workstation using LOCAL
LOAD DATA LOCAL INFILE '/Users/alex/downloads/sales.tsv'
INTO TABLE sales
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

Expected Output Using SQL INFILE

MySQL parses the specified file row by row and inserts each record into the target table
Upon completion it reports the number of records successfully loaded and any warnings or errors

Use Cases with SQL INFILE

Seeding a table with large historical datasets during initial database setup.
Nightly batch jobs that ingest log files or clickstream data.
One-off migrations from CSV/TSV exports maintained by another system.
Rapidly reloading data after performing data cleansing outside the database.

Common Mistakes with SQL INFILE

Assuming LOCAL is always enabled; many hosts disable it for security.
Specifying a file path not readable by the MySQL server when LOCAL is omitted.
Forgetting to quote the path string.
Mismatching field or line delimiters, resulting in shifted columns.
Overlooking the IGNORE n LINES clause for header rows.

First Introduced In

MySQL 3.23

Frequently Asked Questions

What permissions are needed to use INFILE?

You need the FILE privilege for server-side files and an enabled local_infile setting for LOCAL INFILE. The target table also requires INSERT privileges.

Can I load compressed files with INFILE?

Not directly. Decompress the file first or use a pipeline that writes to a temporary uncompressed file before running LOAD DATA INFILE.

How do I handle NULL values in the file?

Specify the NULL keyword or use a custom string in the FIELDS clauses, for example: FIELDS TERMINATED BY ',' NULL '\\N'.

Does INFILE work inside a transaction?

Yes. If autocommit is off, the rows are loaded within the current transaction and can be rolled back before COMMIT.