How to Import a CSV File Into MySQL the Reliable Way

Most guides to importing a CSV into MySQL stop at the LOAD DATA statement succeeding. That is where the interesting failures start. The statement reports success while turning every US formatted date in your file into 0000-00-00, clipping names that are too long, and storing empty numbers as zero, and it does so by design: the keyword LOCAL changes how errors are handled. This guide shows the working statement, the three settings that stop it running, the strictness rule that surprises people, and a script that refuses to publish a load that produced a warning. Measurements are from MySQL 8.4 on a 2,000,000 row file.

The shortest statement that works

MySQL's defaults for LOAD DATA are the opposite of a CSV: fields end at a tab, nothing is quoted, and a backslash escapes the next character. The manual's page for LOAD DATA says the defaults are the same as writing FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'. A CSV therefore needs its format spelled out. This is the table and the four row file:

id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,"Smith, Jane",[email protected],2025-02-03,0
3,Bo Chen,,2025-02-20,
4,Cy Dubois,[email protected],2025-03-01,15.75

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;
SHOW WARNINGS;
SELECT id, name, email, email IS NULL AS email_null, signup_date, balance FROM customers ORDER BY id;
SQL

Level	Code	Message
Warning	1366	Incorrect decimal value: '' for column 'balance' at row 3
id	name	email	email_null	signup_date	balance
1	Ana Silva	[email protected]	0	2025-01-15	120.50
2	Smith, Jane	[email protected]	0	2025-02-03	0.00
3	Bo Chen		0	2025-02-20	0.00
4	Cy Dubois	[email protected]	0	2025-03-01	15.75

The four rows loaded, and the quoted Smith, Jane is one value. Look at the details, because they are the lesson of the whole guide. The warning says Incorrect decimal value: '' for column 'balance' at row 3, and the table shows 0.00 where the file had nothing. The email for Bo Chen is an empty string, with email_null equal to 0, not NULL. MySQL loaded the empty fields as the type's zero value. The LOAD DATA article goes through every clause, and the column mapping that fixes this is covered below.

Three requirements, three errors

A load can fail to start for three different reasons, each with its own message, and they are the most searched part of this topic.

These are real outputs from my bench, and the first two articles show each along with the fix. The security reasoning is in the manual's section on LOAD DATA LOCAL: in a local load the server names the file the client should send, so a hostile or compromised server could ask for any file the client user can read. That is why both sides are off by default, and why I do not leave local_infile on globally on a server that I do not control the clients of. A load from a trusted workstation to a trusted server is the case it exists for.

LOCAL means IGNORE

This is the part to read twice. For a server side load, MySQL applies strict SQL mode: a bad value aborts the statement and rolls it back. For a LOCAL load, the documentation says that unless REPLACE is also given, LOCAL has the same effect as IGNORE on how the file is interpreted and how errors are handled. The reason is stated there too: the server cannot stop the client sending a file in the middle of the transfer. Data interpretation errors become warnings and the load continues, even in a strict SQL mode.

I ran a file of US dates through both. The file has four customers with dates like 01/15/2025, and the column is a DATE:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers-us-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT COUNT(*) AS rows_loaded FROM customers;
SQL

Level	Code	Message
Warning	1265	Data truncated for column 'signup_date' at row 1
Warning	1265	Data truncated for column 'signup_date' at row 2
Warning	1265	Data truncated for column 'signup_date' at row 3
Warning	1366	Incorrect decimal value: '' for column 'balance' at row 3
Warning	1265	Data truncated for column 'signup_date' at row 4
rows_loaded
4

rows_loaded is 4 and the warnings say only Data truncated for column 'signup_date'. Then I selected the column:

id	signup_date
1	0000-00-00
2	0000-00-00
3	0000-00-00
4	0000-00-00

All four dates are 0000-00-00, on a server whose SQL mode includes NO_ZERO_DATE. The mode stops you writing a zero date by hand, and a local load wrote four. Every row was converted to a placeholder and the command line printed nothing unusual. The same file loaded from the server's own filesystem, without LOCAL:

ERROR 1292 (22007) at line 2: Incorrect date value: '01/15/2025' for column 'signup_date' at row 1

ERROR 1292 (22007): Incorrect date value: '01/15/2025' for column 'signup_date' at row 1, and nothing loaded. A single INSERT of the same value fails the same way. The difference between those two outcomes, an error and four zero dates, is the keyword LOCAL, and since LOCAL is how most people run it, the dangerous behavior is the common one. The article on date errors has the rest of the cases: 2025-02-30, empty dates, zero dates and one digit months.

The same rule clips strings and numbers. In my truncation test, a name of Bartholomew in a VARCHAR(5) became Barth, 40000 in a SMALLINT became 32767, and the text x in a number column became 0. The truncation article shows the warnings and the checks, and the fix is the same everywhere: look at the warnings after every load, and treat a nonzero count as a failure.

Local and server side loads compared

Having seen the date result, the useful thing is a table of what differs between the two forms, built from the cases I ran:

The server side form is the better default for a controlled load, since it is strict and its failures are errors. It needs the file on the server, which in practice means a staging directory and a copy step. The local form is the convenient one, and it has to be wrapped in checks to be safe, which is the purpose of the script below.

Duplicate keys deserve their own example, because the outcome is the opposite of what people expect. The table already held Old Ana with id 1, and the file had new rows for ids 1 and 2:

mysql -h mysql -uroot lab -e "TRUNCATE customers; INSERT INTO customers VALUES (1,'Old Ana','[email protected]','2024-01-01',1.00)"
mysql -h mysql -uroot --local-infile=1 lab -vvv -e "LOAD DATA LOCAL INFILE 'dups.csv' INTO TABLE customers FIELDS TERMINATED BY ',' IGNORE 1 LINES; SHOW WARNINGS; SELECT id, name FROM customers ORDER BY id" 2>&1 | grep -v '^---\|^$\|^Bye\|^LOAD DATA\|^SHOW\|^SELECT'

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Deleted: 0  Skipped: 1  Warnings: 1
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'customers.PRIMARY' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
+----+---------+
| id | name    |
+----+---------+
|  1 | Old Ana |
|  2 | New Bo  |
+----+---------+
2 rows in set (0.00 sec)

The local load reported Skipped: 1 and a warning Duplicate entry '1' for key 'customers.PRIMARY', kept the old row and added the new Bo. A refresh of an existing table, loaded this way, silently updates nothing and adds only the new keys. Adding REPLACE before INTO TABLE swaps that behavior: it deleted the old row and inserted the new one, with Deleted: 1:

Query OK, 3 rows affected (0.00 sec)
Records: 2  Deleted: 1  Skipped: 0  Warnings: 0
+----+---------+
| id | name    |
+----+---------+
|  1 | New Ana |
|  2 | New Bo  |
+----+---------+
2 rows in set (0.00 sec)

The server side load of the same file, with no keyword, stopped with the error:

ERROR 1062 (23000) at line 1: Duplicate entry '1' for key 'customers.PRIMARY'

That is the third place where the word LOCAL changes what the statement means, and it is the one most likely to produce stale data that nobody notices. If your load is meant to refresh a table, say what you want: REPLACE, or load into a staging table and use INSERT ... ON DUPLICATE KEY UPDATE so you control which columns change.

Map the columns and clean as you load

Because values that do not fit are silently altered, I do the conversion explicitly in the statement. LOAD DATA reads each field into either a column or a user variable, and a SET clause computes the column from the variables. This turns empty into NULL, parses a US date and discards a column the table does not have:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
TRUNCATE customers;
LOAD DATA LOCAL INFILE 'customers-us-dates.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
  (id, name, @e, @d, @b)
  SET email = NULLIF(@e, ''), signup_date = STR_TO_DATE(@d, '%m/%d/%Y'), balance = NULLIF(@b, '');
SELECT id, name, signup_date, balance FROM customers ORDER BY id;
SQL

id	name	signup_date	balance
1	Ana Silva	2025-01-15	120.50
2	Smith, Jane	2025-02-03	0.00
3	Bo Chen	2025-02-20	NULL
4	Cy Dubois	2025-03-01	15.75

@e, @d and @b are the raw email, date and balance. NULLIF(@e, '') returns NULL for an empty string, and STR_TO_DATE(@d, '%m/%d/%Y') parses the US format, so row 3 has NULL for the balance and the dates are 2025-01-15 and onward, not zeros. The pattern also works for dropping columns: name a variable you never use. Mapping CSV columns to table columns explains it, including the shift that happens when your column list is shorter than the file: in my test the third file column landed in the country column and the load clipped it to 99.

How fast is it

I loaded the 2,000,000 row orders file five ways, three runs each.

python bench_mysql.py

method                                          median s
LOAD DATA into a table with a primary key           5.86      341,455 rows/s
LOAD DATA, checks off, one transaction              5.84      342,476 rows/s
LOAD DATA with no key, key added after              7.99      250,321 rows/s
one INSERT per row, one commit (100,000 rows)       7.69       13,010 rows/s
multi-row INSERT, 1,000 rows each (100,000 rows)      0.96      104,253 rows/s

buffer_pool_mb	flush_at_commit	sync_binlog	binlog	redo_mb
128.00000000	1	1	1	100.00000000

LOAD DATA into a table with a primary key took 5.86 s, which is about 341,000 rows per second. Turning off unique_checks and foreign_key_checks changed nothing in that run, 5.84 s, because the table has neither a secondary index nor a foreign key for them to skip. Loading with no key and adding it afterwards was slower here, 7.99 s in total, since InnoDB rebuilds the table to add a primary key. One INSERT per row ran at 13,010 rows per second, and a multi-row INSERT with 1,000 rows per statement at 104,253 rows per second, eight times faster than single rows and three times slower than LOAD DATA. The settings line explains why these numbers are lower than PostgreSQL's on similar hardware in the PostgreSQL guide: binary logging is on, innodb_flush_log_at_trx_commit is 1, the 128 MB buffer pool is smaller than the 200 MB table and the redo log capacity is 100 MB. Different containers and defaults make this a comparison of configurations, not of engines.

Failing a load from application code

The shell script above works for a person with a terminal. From code, the same rule is a few lines: run the load inside a transaction, read SHOW WARNINGS before committing, and roll back if there is anything in it. LOAD DATA into an InnoDB table can be rolled back like any other statement, so a warning can undo the load even though the statement already ran. This Python function does that with pymysql:

"""LOAD DATA LOCAL from Python, failing the job on any warning and rolling the load back."""
import sys

import pymysql

path, table, spec = sys.argv[1], sys.argv[2], sys.argv[3]
conn = pymysql.connect(host="mysql", user="root", password="secret", database="lab", local_infile=True, autocommit=False)
cur = conn.cursor()
cur.execute(f"LOAD DATA LOCAL INFILE '{path}' INTO TABLE {table} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES {spec}")
loaded = cur.rowcount
cur.execute("SHOW WARNINGS")
warnings = cur.fetchall()
if warnings:
    conn.rollback()
    print(f"rolled back: {loaded} rows were loaded with {len(warnings)} warning(s)")
    for level, code, message in warnings[:5]:
        print(f"  {level} {code}: {message}")
    sys.exit(1)
conn.commit()
print(f"committed {loaded} rows, no warnings")

On the six row file with the six problems, it loaded six rows, found six warnings, rolled back and exited with status 1, and the table was empty afterwards:

python ../../load_checked.py truncation.csv items ""
echo "exit status $?"; mysql -h mysql -uroot -N lab -e "SELECT CONCAT('items has ', COUNT(*), ' rows')  FROM items"

rolled back: 6 rows were loaded with 6 warning(s)
  Warning 1265: Data truncated for column 'name' at row 2
  Warning 1264: Out of range value for column 'qty' at row 3
  Warning 1264: Out of range value for column 'price' at row 4
  Warning 1366: Incorrect integer value: 'x' for column 'qty' at row 5
  Warning 1366: Incorrect integer value: 'yes' for column 'flag' at row 5
exit status 1
items has 0 rows

On the clean customers file, with the mapping that turns empty strings into NULL, it committed four rows with no warnings:

committed 4 rows, no warnings

The point is the decision rule, not the language. A load that produced any warning is treated as failed, and the table is left as it was. If a known harmless warning is expected, for example the decimal rounding note, filter it by code in the check, so the exceptions are written down and not tolerated by default.

Refreshing a table that already has data

A load that is meant to update an existing table needs a plan for the keys that already exist. REPLACE rewrites whole rows and resets columns that are not in the file, and the local default skips them silently. The approach that gives control is a staging table and an upsert. Load the file into a table with the same structure, then merge it with INSERT ... ON DUPLICATE KEY UPDATE, naming the columns that may change:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL'
DROP TABLE IF EXISTS customers_stage;
CREATE TABLE customers_stage LIKE customers;
LOAD DATA LOCAL INFILE 'stage-new.csv' INTO TABLE customers_stage FIELDS TERMINATED BY ',' IGNORE 1 LINES;
INSERT INTO customers (id, name, email, signup_date, balance)
  SELECT id, name, email, signup_date, balance FROM customers_stage
  ON DUPLICATE KEY UPDATE email = VALUES(email), balance = VALUES(balance);
SELECT id, name, email, balance FROM customers ORDER BY id;
SQL

id	name	email	balance
1	Ana Silva	[email protected]	200.00
2	Smith, Jane	[email protected]	0.00
3	Bo Chen	NULL	NULL
4	Cy Dubois	[email protected]	15.75
5	Eli Fox	[email protected]	6.00

Ana Silva (id 1) got a new email and balance from the file, id 5 was added, and the other rows, including Smith, Jane, whose name and date are not in the update columns, were untouched. Every column the statement does not name keeps its value, which is the difference from REPLACE, and what makes this the safer way to refresh. Note that VALUES(col) in this form is deprecated in recent MySQL versions in favor of an alias for the inserted row, so check the manual for the version you run; it worked on 8.4.11 as written.

Profile the file, then size the columns

Because a local load clips instead of failing, the table definition is the only protection the data has, and it has to be right before the load. A profile of the file gives you the numbers to write it from. qsv stats reads the whole file once and reports the type it infers, the range and the lengths for each column. This is the 2,000,000 row file:

field        type     min                  max                  min_length  max_length  nullcount
order_id     Integer  1                    2000000                                      0
customer_id  Integer  1                    249999                                       0
zip          String   00500                99998                5           5           0
amount       Float    0.04                 654.24                                       0
created_at   String   2025-01-01 00:00:04  2025-12-31 23:59:38  19          19          0
status       String   failed               shipped              4           8           0
country      String   BR                   US                   2           2           0
note         String   call first           rush order           0           13          400697

Translate each row into a MySQL type with room to spare. order_id runs to 2,000,000 and customer_id to 249,999, so INT is right, and SMALLINT would have clamped at 32,767. zip is a string of exactly five characters beginning 00500, which settles VARCHAR(5) or CHAR(5) and rules out any numeric type, since a numeric column would drop the leading zeros. amount has a maximum of 654.24 and two decimals, so DECIMAL(10,2) has room. created_at is a fixed 19 character timestamp, which loads into DATETIME as written. status is at most 8 characters, so VARCHAR(10) fits, country is exactly 2 so CHAR(2) fits, and note has a maximum length of 13 and is empty in 400,697 rows, so VARCHAR(20) with a NULLIF in the SET clause turns those into real NULLs.

The second use of the profile is as a check on the data's shape, before any of this: the column count and the widths of every record come out of the same pass, and a file whose profile has changed since last month, with a new column or a type that flipped from integer to string, is a file to look at before loading. A profile takes seconds on a file this size. The guide to validating a CSV file turns the idea into a set of checks you can run on every delivery.

The clauses, in one place

The statements in this guide use a handful of clauses repeatedly, and it helps to see them together with what each guards against.

Each of these has an article, and the LOAD DATA article walks through the first few with outputs. The most useful habit is to write the full set every time, even where the default is what you want, so the statement documents the file's format.

Indexes: before or after

The benchmark above used a table with only a primary key. Real tables have secondary indexes, and InnoDB has to maintain each one as rows arrive. I loaded the same 2,000,000 rows into a table with two secondary indexes, one on customer_id and one on created_at, and compared declaring them up front with adding them in a single ALTER TABLE after the load:

python bench_mysql_idx.py

strategy                                              median s
indexes in the table definition, then load               15.09
load first, then add both indexes in one ALTER            7.43

With the indexes in the definition, the load took 15.09 s. Loading first and adding both indexes in one ALTER TABLE took 7.43 s in total, half the time, since InnoDB builds an index by sorting the data once instead of inserting into the tree row by row. That is the reverse of the result for the primary key in the same article, where adding the key afterwards was slower: the primary key is the clustered index and adding it rewrites the whole table, while secondary indexes are built from the finished data. The advice follows: create the table with its primary key, load, then add the secondary indexes in one statement, and the saving grows with the number of indexes.

The caveat is that the table is not fully usable until the ALTER completes, which is another argument for loading into a new table and swapping. Unique secondary indexes added afterwards can fail on duplicates in the data, which is also a check: finding that at the end of a load is better than finding it a month later.

What other sessions see

A LOAD DATA into an InnoDB table is one transaction. I ran the 2,000,000 row load in one session while another counted rows and inserted unrelated rows every 200 ms. The reader never saw loaded rows before the commit, its COUNT(*) took a median of 44 ms, and the writer's INSERT of an unrelated key took a median of 2 ms. The large import article has the full run, a comparison with loading in 20 chunks (5.79 s against 5.86 s, no gain) and the swap that matters: loading into a new table and replacing the old one with RENAME TABLE took 21 ms.

A load that checks itself

This is the routine that follows from all of the above. It counts the records with a strict CSV reader, loads into a new table, reads @@warning_count in the same session as the load, compares the row count with the file, and only then swaps the new table in. If either check fails, nothing changes.

#!/usr/bin/env bash
# Load a CSV into MySQL with LOAD DATA LOCAL, then refuse to swap it in if anything was converted, clipped or lost.
set -euo pipefail
file=${1:?usage: load_orders_mysql.sh orders.csv}
mysql_() { mysql -h mysql -uroot --local-infile=1 -N lab "$@"; }

expected=$(qsv count "$file")
mysql_ -e "DROP TABLE IF EXISTS orders_new; CREATE TABLE orders_new (
  order_id INT PRIMARY KEY, customer_id INT NOT NULL, zip VARCHAR(5), amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL, status VARCHAR(10) NOT NULL, country CHAR(2), note VARCHAR(20))"

warnings=$(mysql_ -e "LOAD DATA LOCAL INFILE '$file' INTO TABLE orders_new
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES
  (order_id, customer_id, zip, amount, created_at, status, country, @note) SET note = NULLIF(@note, '');
  SELECT @@warning_count")
loaded=$(mysql_ -e "SELECT COUNT(*) FROM orders_new")

echo "file: $expected records, loaded: $loaded, warnings: $warnings"
if [ "$loaded" -ne "$expected" ] || [ "$warnings" -ne 0 ]; then
  echo "not swapping: the load was not clean" >&2
  exit 1
fi
mysql_ -e "DROP TABLE IF EXISTS orders_old; CREATE TABLE IF NOT EXISTS orders LIKE orders_new; RENAME TABLE orders TO orders_old, orders_new TO orders; DROP TABLE orders_old"
echo "swapped in"

On the 2,000,000 row file, with zero warnings:

./load_orders_mysql.sh orders-2m.csv

file: 2000000 records, loaded: 2000000, warnings: 0
swapped in

Then on a copy where I changed a country code on one row to three letters, which the CHAR(2) column cannot hold:

head -n 1000 orders-2m.csv | sed '500s/,[A-Z][A-Z],/,ZZZ,/' > bad-status.csv
./load_orders_mysql.sh bad-status.csv; echo "exit status $?"
mysql -h mysql -uroot -N lab -e "SELECT CONCAT('orders still has ', COUNT(*), ' rows') FROM orders"

file: 999 records, loaded: 999, warnings: 1
not swapping: the load was not clean
exit status 1
orders still has 2000000 rows

One warning was enough to stop the swap, exit with status 1 and leave the live orders table with its previous 2,000,000 rows. Without that check, the load would have succeeded, clipped one value to ZZ, and nobody would have known. In a real pipeline I also print the warnings text, which SHOW WARNINGS gives in the same session, so the log says which row and column.

Server settings worth checking

Five variables explain most of the variation between servers. innodb_buffer_pool_size decides whether the table and its indexes fit in memory while they are being written, and the 128 MB default on my server is smaller than the 200 MB table, which is why the secondary index build was slow. innodb_redo_log_capacity bounds how much write ahead log can accumulate before flushing, 100 MB here, and a load that outruns it stalls for checkpoints. innodb_flush_log_at_trx_commit at its default of 1 flushes at each commit, which a single large load pays once and a loop of single row inserts pays every time. log_bin and sync_binlog decide whether the load is also written, and synced, to the binary log that replicas read. And max_allowed_packet limits the size of one statement, which matters for multi-row inserts and is not a limit for LOAD DATA of a file.

On a production server these are tuned and not defaults. Before a large load, read the values with SELECT @@innodb_buffer_pool_size, @@innodb_redo_log_capacity, @@log_bin and compare them with the table size. Raising the buffer pool on a server that is going to hold the data anyway is the single change that most often turns a slow load into a fast one.

Characters and encodings

The load reads the file in the character set you name, or the connection's default. MySQL 8.4 defaults to utf8mb4, which holds every character, but older tables created as utf8 hold only three bytes per character, and a local load clips emoji to ? with a warning. The article on emoji has the reproduction, and the garbled text guide covers a Latin 1 file: LOAD DATA ... CHARACTER SET latin1 converts on load. Line endings are the other classic trap: a Windows file needs LINES TERMINATED BY '\r\n' or every last column carries a carriage return, as the line endings article measured.

The order I run things in

For a file from a source I do not trust, the sequence matters more than any single clause, so here it is in the order I would do it and why each step sits where it does.

First the bytes: a preflight for the encoding, NUL bytes and a byte order mark, from the garbled text guide, because every later step assumes the characters are right. Second the shape: a strict record count and the widths of every record, because a ragged file loads shifted values with only a truncation warning. Third the profile above, to size the columns. Fourth the table: create orders_new with the types from the profile and the primary key but no secondary indexes. Fifth the load, with every clause written out, inside a session that reads @@warning_count before anything else runs. Sixth the checks: the row count against the file, a sum of one numeric column against the same sum computed by a second tool, a look at the minimum and maximum of every date, and a handful of rows with the awkward characters. Seventh the secondary indexes, in one ALTER. Eighth the swap with RENAME TABLE, keeping the old table until the new one has served traffic.

None of that is exotic, and it is about ten minutes of work for a file of this size once the commands are in a script. The failure it prevents is the one this guide opened with: a successful looking load that altered the data. Every step has a measurement behind it above, and every one of them can be skipped when the file is small and from a source you control. When someone asks whether the extra care is worth it, the answer is that the clipped value and the zero date are not found by the load, and the people who find them are the ones reading a report next month.

Where each article fits

If a load will not start, see error 1290 and local infile disabled. For the statement itself, see using LOAD DATA INFILE and headers and odd quotes. For column mapping, see mapping CSV columns. For warnings and errors in values, see incorrect date values and data truncated. For a graphical route, see Workbench versus the command line, and for big files, see loading a large CSV without locking the table. The same job in PostgreSQL and SQLite is in the PostgreSQL guide and the SQLite guide.

When LOAD DATA is not the right tool

LOAD DATA is right for a complete file you control, going into a table whose layout you decided. Four cases want something else.

If you are on MariaDB, most of this carries over, but its defaults and some options differ, so check its manual for the version you run. If the server is a managed one, such as RDS or Aurora, local_infile is a parameter group setting you change and the server side form is not available, so LOCAL or the provider's S3 import is the route. If you are loading a few hundred rows once, the import wizard in a GUI client is fine, as the Workbench comparison explains. And if the file is huge and the work is parallelizable, MySQL Shell's import utility reads in parallel; I did not test it here, so measure it against your own data before you plan around it.

For continuous feeds, batch the rows into multi-row INSERT statements of about a thousand rows, which my test put at 104,253 rows per second, eight times a single row insert. And for files full of text that you have not inspected, load into an all VARCHAR staging table first, which cannot clip or convert anything, and do the conversion in SQL where you can see it.