How to bulk load data in MariaDB

How do I bulk load CSV data into MariaDB quickly?

LOAD DATA INFILE ingests large text files directly into a MariaDB table, bypassing row-by-row INSERTs for speed.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does bulk loading data in MariaDB mean?

Bulk loading uses LOAD DATA INFILE to stream a text file—typically CSV or TSV—straight into a table. The engine parses and writes rows in a single transaction, reaching hundreds of thousands of rows per second.

When should I use LOAD DATA INFILE instead of INSERT?

Use it for initial data migrations, nightly imports from external systems, or back-filling historical records.INSERT is fine for real-time writes; LOAD DATA INFILE shines when the file already exists and speed matters.

What is the syntax for LOAD DATA INFILE?

See the full syntax below. Key options: LOCAL (client file), FIELDS TERMINATED BY, ENCLOSED BY, IGNORE lines, and SET for computed columns.

How do I bulk load CSV into the Customers table?

Place customers.csv on the server (or add LOCAL) and run the example in the next section.Verify row counts with SELECT COUNT(*).

How do I handle NULLs and defaults?

Represent NULL with \N in the file or use SET col = NULLIF(@col,''). Columns not listed inherit table defaults.

Tip: Disable indexes during massive loads

For multi-million-row files, drop or disable secondary indexes first, load the data, then recreate indexes. This can cut total time by 70-90%.

What permissions are required?

The account needs FILE and INSERT privileges.Without LOCAL, the file must reside in secure_file_priv.

How to troubleshoot common errors?

"Errcode 13" means wrong file path or permissions. "Malformed line" indicates delimiter or quote mismatch. Use SHOW WARNINGS after the load for row-level diagnostics.

Why How to bulk load data in MariaDB is important

How to bulk load data in MariaDB Example Usage


LOAD DATA LOCAL INFILE '/Users/me/data/customers.csv'
INTO TABLE Customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, @created)
SET created_at = STR_TO_DATE(@created,'%Y-%m-%d %H:%i:%s');

How to bulk load data in MariaDB Syntax


LOAD DATA [LOCAL] INFILE 'file_path'
     INTO TABLE table_name
     [CHARACTER SET charset]
     FIELDS TERMINATED BY ','
            [OPTIONALLY] ENCLOSED BY '"'
            ESCAPED BY '\\'
     LINES TERMINATED BY '\n'
     [IGNORE n LINES]
     (col1, col2, @dummy, col3)
     [SET col4 = expression];

-- Example for ecommerce Orders
LOAD DATA INFILE '/var/lib/mysql-files/orders_2024.csv'
INTO TABLE Orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, customer_id, order_date, total_amount);

Common Mistakes

Mistake: Omitting LOCAL when the file is on the client machine, causing “File not found” errors. Fix: Add the LOCAL keyword or move the file to the server’s secure_file_priv directory.
Mistake: Forgetting to ignore the CSV header row, leading to column-type conversion errors. Fix: Add IGNORE 1 LINES to skip the header.

Frequently Asked Questions (FAQs)

Can I load data from STDIN instead of a file?

Yes. Use LOAD DATA LOCAL INFILE '-' and pipe data to the client. Make sure local_infile is enabled on both client and server.

How do I keep the database online during large loads?

Wrap the load in a low-priority transaction or run during off-peak hours. Partitioning the table can also isolate write amplification.

Does LOAD DATA INFILE honor triggers?

Yes. BEFORE and AFTER INSERT triggers fire for each loaded row, so heavy logic can slow the process. Disable or simplify triggers for best throughput.