Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 75 ยท Changing Data

Bulk Loading and Exporting Data

Move large amounts of data in and out of MySQL: LOAD DATA INFILE, secure_file_priv, SELECT INTO OUTFILE, mysqlimport, MySQL Shell importTable and insert batching.

Sooner or later you have a file with a million rows that must end up in a table, or a table that must end up in a file. Looping over the rows and sending one INSERT for each works, but it is slow, because every statement pays for a network round trip, parsing, and (in autocommit mode) a durable commit. MySQL gives you purpose-built tools for the job: LOAD DATA to read a file into a table, SELECT ... INTO OUTFILE to write one, a command-line wrapper called mysqlimport, and a parallel importer in MySQL Shell. When you must use plain INSERT, a few batching rules make it many times faster.

This lesson covers all of them. The file-based tools need a real MySQL server with access to a file system, so they are shown as read-only code. The batching ideas and a "staging table" cleaning pattern run live here in the browser's SQLite stand-in. If you want to overwrite existing rows while loading, read Upserts first.

Four ways to move data

LOAD DATA Read a CSV or text file straight into a table.
INTO OUTFILE Write a query result to a file on the server.
mysqlimport A shell command that wraps LOAD DATA.
Shell importTable Parallel import from MySQL Shell.

All four beat a loop of single-row INSERT statements.

1 / 2

LOAD DATA INFILE

LOAD DATA is the fastest way to bring a delimited text file into a table. Suppose products.csv looks like this:

TEXT
sku,name,price,stock
A-100,"Desk lamp",24.90,3
A-200,Notebook,4.50,
A-300,"Mug, large",7.25,12

A typical statement to load it:

SQL
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku, name, price, @stock)
SET stock = NULLIF(@stock, '');

Piece by piece:

  • FIELDS TERMINATED BY and OPTIONALLY ENCLOSED BY describe the CSV dialect, so a quoted "Mug, large" stays one field.
  • LINES TERMINATED BY '\n' is the row separator. Files written on Windows usually need '\r\n'.
  • IGNORE 1 LINES skips the header.
  • The column list maps file fields to table columns, in file order. A field you want to transform goes into a user variable (@stock) first.
  • SET computes column values from those variables. Here an empty string becomes NULL, which matters because an empty field loaded into an INT column would otherwise become 0 (with a warning) or be rejected in strict mode.
  • CHARACTER SET utf8mb4 tells the server how the file is encoded. If it is omitted, the server uses its own setting, which is a classic source of garbled accents (Character Sets and Collations).

The whole file is one statement, so it runs in one transaction on InnoDB: if it fails halfway, nothing is loaded. After the load, check SHOW WARNINGS, because truncated values and bad numbers are reported there when they are not errors.

Duplicate keys behave differently depending on the form. With a server-side file, a duplicate key stops the load with an error unless you add IGNORE (skip the row) or REPLACE (delete the old row, insert the new one). With LOCAL, described next, duplicates are skipped with warnings by default. REPLACE has the same hazards here as anywhere else: it deletes and re-inserts, so foreign keys and triggers see a delete.

secure_file_priv and the LOCAL option

A LOAD DATA INFILE without LOCAL makes the server process read a file from the server's disk. That is a powerful capability, so MySQL restricts it with the read-only variable secure_file_priv:

Value Meaning
a directory path Server-side file reads and writes are allowed only inside that directory.
empty string No restriction. Convenient and unsafe.
NULL Server-side import and export are disabled.

Package installers usually set it to a directory such as /var/lib/mysql-files. You can see the current value with:

SQL
SHOW VARIABLES LIKE 'secure_file_priv';

The variable can only be changed in my.cnf and with a restart. If you see ERROR 1290 ... running with the --secure-file-priv option, you either put the file in the allowed directory or use LOCAL.

LOAD DATA LOCAL INFILE flips the direction: the client reads the file and streams it to the server, so the file can live on your laptop. Because a malicious server could ask a client for arbitrary files, local_infile is off by default on the server, and the client must opt in too:

Terminal
mysql --local-infile=1 -u app -p shop
SQL
SET GLOBAL local_infile = 1;   -- needs the right privilege; turn it off again afterwards

LOAD DATA LOCAL INFILE 'C:/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;

Use forward slashes in Windows paths inside the statement, or double every backslash. Managed services (RDS, Cloud SQL and others) usually forbid server-side files entirely, so LOCAL or their own import tools are the only option there.

SELECT ... INTO OUTFILE

The reverse operation writes a result set to a file on the server:

SQL
SELECT sku, name, price, stock
FROM products
WHERE stock IS NOT NULL
INTO OUTFILE '/var/lib/mysql-files/in-stock.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Rules that surprise people:

  • The file must not already exist. MySQL refuses to overwrite, so scripts add a timestamp to the name.
  • It is written by the server process, owned by the mysql operating-system user, inside secure_file_priv.
  • NULL is written as \N, so the file round-trips through LOAD DATA unchanged.
  • There is no header row. If you need one, UNION ALL a literal row of column names on top, or let the application write the file.
  • INTO DUMPFILE writes one raw row with no escaping, which is used for a single BLOB.

For ad-hoc exports from your own machine you often do not need any of this. The mysql client in batch mode writes tab-separated output to standard output, and you redirect it:

Terminal
mysql -u app -p --batch --skip-column-names -e "SELECT sku, price FROM shop.products" > prices.tsv

mysqlimport and MySQL Shell

mysqlimport is a thin command-line wrapper that builds LOAD DATA statements for you. It names the table after the file: products.csv goes into table products.

Terminal
mysqlimport --local --fields-terminated-by=',' --fields-optionally-enclosed-by='"' \
            --ignore-lines=1 --user=app --password shop products.csv

MySQL Shell (mysqlsh) has a modern, faster importer. util.importTable splits a file into chunks and loads them over several parallel connections, and it can show progress:

JavaScript
// run inside mysqlsh in JavaScript mode
util.importTable("C:/data/products.csv", {
  schema: "shop",
  table: "products",
  dialect: "csv-unix",
  skipRows: 1,
  columns: ["sku", "name", "price", "stock"],
  threads: 4
});

Its counterpart util.exportTable writes a table to a file, and it works with LOCAL data, so it is a good choice when the server is remote. Both need local_infile to be enabled on the server for the import.

Batching plain INSERT statements

Sometimes LOAD DATA is not available, for example when the data comes out of application code or needs per-row logic. Then these rules give most of the speed:

  1. Send many rows per statement: INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y'), .... A few hundred to a few thousand rows per statement is a good range. Beyond that the statement hits max_allowed_packet (the size limit of one statement or packet) and the gains flatten out.
  2. Commit once per batch, not once per row. Wrap a batch in START TRANSACTION ... COMMIT, or turn autocommit off while loading. With the default innodb_flush_log_at_trx_commit = 1, every commit waits for the log to reach disk, so row-by-row autocommit is dominated by waiting.
  3. Insert in primary-key order when you can. InnoDB stores rows in primary-key order, so ascending keys fill pages sequentially instead of splitting them.
  4. Use prepared statements or a driver's batch API, so the statement is parsed once.
  5. Create secondary indexes after the load when loading an empty table. Building an index once from sorted data is cheaper than updating it row by row. (Dropping indexes on a table people are using is another matter.)
  6. As a last resort, SET unique_checks = 0 and SET foreign_key_checks = 0 for the loading session skip checks. Only do this if you are certain the data is clean, and turn them back on afterwards, because the server will not verify the skipped rows later.

You cannot time things in this lesson's SQLite stand-in, but you can still see the shape of a batched load. This example inserts three batches in one transaction and a generated series in one statement:

SQL Batches and a generated series, one transaction

Press Run (or Ctrl + Enter) to see the result here.

Each batch holds three rows, with ids 1 to 3, 4 to 6 and 7 to 9. All three statements committed together, so another session would have seen either nothing or all nine rows.

The staging-table pattern

Real files are messy: header lines, padded text, empty strings where you want NULL, values that are not numbers at all. LOAD DATA can clean some of it with SET, but a very common and safer approach is to load everything as text into a staging table, then clean and move the rows with ordinary SQL. You can query the problems, fix them, and reject what cannot be fixed, all before touching the real table.

SQL Staging table: clean, load, report rejects

Press Run (or Ctrl + Enter) to see the result here.

Three rows reach products: the lamp is trimmed, the notebook has a NULL quantity instead of 0 (because NULLIF turned the empty string into NULL first), and the mug is loaded as is. The second query reports the pen on line 4, whose price n/a is not a number. In MySQL, the first step would be a LOAD DATA into staging, and in strict mode you would use CAST(... AS DECIMAL(10,2)) instead of REAL.

What ran live here

The batching example and the staging pattern ran in SQLite. LOAD DATA, secure_file_priv, INTO OUTFILE, mysqlimport and util.importTable are MySQL tools that need a server and files, so they are read-only code blocks.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does LOAD DATA LOCAL INFILE do differently from LOAD DATA INFILE?

    Without LOCAL, the server process reads a file from its own disk, subject to secure_file_priv. With LOCAL, the client reads it. local_infile must be enabled on both sides.
  2. 2. What does secure_file_priv = NULL mean?

    A directory path limits file operations to that directory, an empty string removes the limit, and NULL turns server-side file import and export off.
  3. 3. What happens if the target file of SELECT ... INTO OUTFILE already exists?

    INTO OUTFILE never overwrites an existing file. Scripts usually put a date or counter in the file name.
  4. 4. Which change usually speeds up a loop of single-row INSERT statements the most?

    Batching removes most round trips, parsing and per-commit log flushes. Extra indexes make inserts slower.
  5. 5. Why load a messy file into a staging table first?

    Loading raw text and then cleaning it with INSERT ... SELECT lets you query the bad rows and keep the production table clean.

Summary

  • LOAD DATA INFILE is the fastest import. Describe the file with FIELDS, LINES, IGNORE n LINES, map columns with a list and clean values with SET.
  • secure_file_priv limits server-side file access to one directory (or disables it). LOCAL lets the client send the file, but local_infile must be allowed on both sides.
  • SELECT ... INTO OUTFILE writes on the server, never overwrites and has no header row.
  • mysqlimport wraps LOAD DATA, and MySQL Shell's util.importTable loads in parallel.
  • For plain INSERT, use multi-row statements, one commit per batch, primary-key order and prepared statements.
  • A staging table lets you clean and reject rows with ordinary SQL.

Next: The JSON Data Type.