LOAD DATA INFILE in MariaDB
Last updated: 2026-06-24
This skill covers the delta between standard SQL bulk loading and MariaDB's LOAD DATA [LOCAL] INFILE (and the LOAD XML companion). It assumes the agent knows the basic LOAD DATA INFILE 'f' INTO TABLE t form. The inverse — writing a file — is SELECT … INTO OUTFILE (see mariadb-select); the command-line wrapper is mariadb-import (see mariadb-import).
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.
What LLMs Often Miss
| If the agent writes / assumes… |
…prefer the MariaDB form |
LOAD DATA LOCAL INFILE … will just work once the client passes the file |
LOCAL needs both ends enabled: the client/connector must allow local-infile (often off by default in drivers — the usual real blocker) and the server's local_infile system variable must be ON (it is, by default). Otherwise: "the … server or client has disabled the local infile capability" |
Without LOCAL, the path is relative to the client / current directory |
Without LOCAL the server reads the file from its own filesystem. The connecting user needs the global FILE privilege, and if secure_file_priv is set the file must sit under that directory |
Adds REPLACE / IGNORE to a LOCAL load to control duplicate-key handling |
With LOCAL the server can't error mid-stream, so a bare LOAD DATA LOCAL silently treats duplicate-key rows as IGNORE (keeps the old row, warns) — your intent to error is dropped. For reliable REPLACE/error semantics, load from a server-side file (no LOCAL) |
Expects strict mode to error on bad/truncated values during a LOCAL load |
With LOCAL, strict-mode abort-on-warning is disabled — bad data is coerced and warned, never aborted. (A non-LOCAL load honors strict mode.) Always check SHOW WARNINGS after loading |
Assumes LOAD DATA errors out like an INSERT on bad rows |
Even without LOCAL, LOAD DATA leans toward warn-and-coerce — too-few/too-many fields per row raise warnings, not errors. Inspect SHOW WARNINGS every time |
LOAD DATA INFILE 'x.csv' INTO TABLE t and expects CSV parsing |
MariaDB's defaults are tab-separated fields, newline-terminated lines, backslash escape — not CSV. For real CSV: FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' |
| Post-filters or pre-strips a header row |
Use IGNORE 1 LINES (LINES and ROWS are interchangeable) to skip header rows |
Loads a value that needs transforming, then fixes it with a follow-up UPDATE |
Read the raw field into a user variable and transform in SET in the same pass: (id, @raw) SET created = STR_TO_DATE(@raw, '%m/%d/%Y') |
Relies on SET NAMES / character_set_client for the file's encoding |
LOAD DATA ignores those — use the CHARACTER SET clause. The default read charset is character_set_database; CHARACTER SET binary means no conversion |
Adds LOW_PRIORITY / CONCURRENT to speed up an InnoDB load |
Both affect only table-lock engines (CONCURRENT is MyISAM-specific); no effect on InnoDB. They're also mutually exclusive |
Uses LOAD DATA machinery (FIELDS/LINES) for XML |
LOAD XML has no FIELDS/LINES — it uses ROWS IDENTIFIED BY '<tag>' (angle brackets literal; default <row>) |
Syntax
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (p, …)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var, …)]
[SET col_name = expr, …];
Defaults when a clause is omitted: fields \t, lines \n, escape \.
-- Real CSV with a header row and an on-the-fly transform:
LOAD DATA LOCAL INFILE '/tmp/data.csv'
INTO TABLE t
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(a, @raw_b) SET b = UPPER(@raw_b);
-- Server-side load (needs FILE privilege; path under secure_file_priv):
LOAD DATA INFILE '/var/lib/mysql-files/import.tsv' REPLACE INTO TABLE t;
LOAD XML
LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number {LINES | ROWS}]
[(column_or_user_var, …)]
[SET col_name = expr, …];
LOCAL, REPLACE/IGNORE, CHARACTER SET, the column list, and SET behave exactly as for LOAD DATA — including the LOCAL duplicate/strict-mode caveats above. ROWS IDENTIFIED BY names the row element (default <row>); MariaDB auto-detects the common attribute/element layouts.
Security checklist
- No
LOCAL → server reads its own filesystem; requires FILE privilege; path constrained by secure_file_priv (and secure_file_priv = NULL disables file I/O entirely).
LOCAL → client streams the file; needs the connector's local-infile option on and server local_infile = ON; relaxes duplicate and strict-mode handling (see traps above).
See Also
mariadb-select — SELECT … INTO OUTFILE / INTO DUMPFILE, the inverse of LOAD DATA, with the same FIELDS/LINES grammar and FILE/secure_file_priv constraints (match CHARACTER SET on both sides for round-tripping)mariadb-import — the command-line wrapper that issues LOAD DATA INFILE per filemariadb-insert / mariadb-replace — duplicate-key handling: IGNORE semantics and REPLACE (delete-then-insert) caveats that LOAD DATA … REPLACE inherits- Canonical references on
mariadb.com/docs (consult only for edge cases not covered here):
This page is: Copyright © 2026 MariaDB. All rights reserved.
1---2name: mariadb-load-data3description: "MariaDB-specific syntax and behavior for LOAD DATA [LOCAL] INFILE (and LOAD XML) — the LOCAL vs server-side fork and its security/privilege requirements (local_infile, FILE privilege, secure_file_priv), tab/newline (not CSV) defaults, how LOCAL silently downgrades duplicate handling to IGNORE and disables strict-mode aborts, IGNORE n LINES, user-variable + SET transformation, the CHARACTER SET clause, and priority/concurrency. Use when writing, generating, or reviewing LOAD DATA / LOAD XML statements, or bulk-loading CSV/TSV/XML into MariaDB."4---56# LOAD DATA INFILE in MariaDB78*Last updated: 2026-06-24*910This skill covers the **delta between standard SQL bulk loading and MariaDB's** `LOAD DATA [LOCAL] INFILE` (and the `LOAD XML` companion). It assumes the agent knows the basic `LOAD DATA INFILE 'f' INTO TABLE t` form. The inverse — writing a file — is `SELECT … INTO OUTFILE` (see `mariadb-select`); the command-line wrapper is `mariadb-import` (see `mariadb-import`).1112> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.1314## What LLMs Often Miss1516| If the agent writes / assumes… | …prefer the MariaDB form |17|---|---|18| `LOAD DATA LOCAL INFILE …` will just work once the client passes the file | `LOCAL` needs **both** ends enabled: the client/connector must allow local-infile (often **off by default** in drivers — the usual real blocker) *and* the server's `local_infile` system variable must be ON (it is, by default). Otherwise: *"the … server or client has disabled the local infile capability"* |19| Without `LOCAL`, the path is relative to the client / current directory | Without `LOCAL` the **server** reads the file from *its own* filesystem. The connecting user needs the global **`FILE`** privilege, and if `secure_file_priv` is set the file must sit under that directory |20| Adds `REPLACE` / `IGNORE` to a `LOCAL` load to control duplicate-key handling | With `LOCAL` the server can't error mid-stream, so a bare `LOAD DATA LOCAL` **silently treats duplicate-key rows as `IGNORE`** (keeps the old row, warns) — your intent to error is dropped. For reliable `REPLACE`/error semantics, load from a server-side file (no `LOCAL`) |21| Expects strict mode to **error** on bad/truncated values during a `LOCAL` load | With `LOCAL`, strict-mode abort-on-warning is **disabled** — bad data is coerced and warned, never aborted. (A non-`LOCAL` load honors strict mode.) Always check `SHOW WARNINGS` after loading |22| Assumes `LOAD DATA` errors out like an `INSERT` on bad rows | Even without `LOCAL`, `LOAD DATA` leans toward **warn-and-coerce** — too-few/too-many fields per row raise warnings, not errors. Inspect `SHOW WARNINGS` every time |23| `LOAD DATA INFILE 'x.csv' INTO TABLE t` and expects CSV parsing | MariaDB's defaults are **tab-separated fields, newline-terminated lines, backslash escape — not CSV**. For real CSV: `FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'` |24| Post-filters or pre-strips a header row | Use `IGNORE 1 LINES` (`LINES` and `ROWS` are interchangeable) to skip header rows |25| Loads a value that needs transforming, then fixes it with a follow-up `UPDATE` | Read the raw field into a **user variable** and transform in `SET` in the same pass: `(id, @raw) SET created = STR_TO_DATE(@raw, '%m/%d/%Y')` |26| Relies on `SET NAMES` / `character_set_client` for the file's encoding | `LOAD DATA` ignores those — use the `CHARACTER SET` clause. The default read charset is `character_set_database`; `CHARACTER SET binary` means no conversion |27| Adds `LOW_PRIORITY` / `CONCURRENT` to speed up an InnoDB load | Both affect only table-lock engines (`CONCURRENT` is MyISAM-specific); no effect on InnoDB. They're also mutually exclusive |28| Uses `LOAD DATA` machinery (FIELDS/LINES) for XML | `LOAD XML` has no FIELDS/LINES — it uses `ROWS IDENTIFIED BY '<tag>'` (angle brackets literal; default `<row>`) |2930## Syntax3132```sql33LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'34 [REPLACE | IGNORE]35 INTO TABLE tbl_name36 [PARTITION (p, …)]37 [CHARACTER SET charset_name]38 [{FIELDS | COLUMNS}39 [TERMINATED BY 'string']40 [[OPTIONALLY] ENCLOSED BY 'char']41 [ESCAPED BY 'char']]42 [LINES43 [STARTING BY 'string']44 [TERMINATED BY 'string']]45 [IGNORE number {LINES | ROWS}]46 [(col_name_or_user_var, …)]47 [SET col_name = expr, …];48```4950Defaults when a clause is omitted: fields `\t`, lines `\n`, escape `\`.5152```sql53-- Real CSV with a header row and an on-the-fly transform:54LOAD DATA LOCAL INFILE '/tmp/data.csv'55 INTO TABLE t56 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'57 LINES TERMINATED BY '\n'58 IGNORE 1 LINES59 (a, @raw_b) SET b = UPPER(@raw_b);6061-- Server-side load (needs FILE privilege; path under secure_file_priv):62LOAD DATA INFILE '/var/lib/mysql-files/import.tsv' REPLACE INTO TABLE t;63```6465## `LOAD XML`6667```sql68LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'69 [REPLACE | IGNORE]70 INTO TABLE [db_name.]tbl_name71 [CHARACTER SET charset_name]72 [ROWS IDENTIFIED BY '<tagname>']73 [IGNORE number {LINES | ROWS}]74 [(column_or_user_var, …)]75 [SET col_name = expr, …];76```7778`LOCAL`, `REPLACE`/`IGNORE`, `CHARACTER SET`, the column list, and `SET` behave exactly as for `LOAD DATA` — including the `LOCAL` duplicate/strict-mode caveats above. `ROWS IDENTIFIED BY` names the row element (default `<row>`); MariaDB auto-detects the common attribute/element layouts.7980## Security checklist8182- **No `LOCAL`** → server reads its own filesystem; requires `FILE` privilege; path constrained by `secure_file_priv` (and `secure_file_priv = NULL` disables file I/O entirely).83- **`LOCAL`** → client streams the file; needs the connector's local-infile option on and server `local_infile = ON`; relaxes duplicate and strict-mode handling (see traps above).8485## See Also8687- **`mariadb-select`** — `SELECT … INTO OUTFILE` / `INTO DUMPFILE`, the inverse of `LOAD DATA`, with the same `FIELDS`/`LINES` grammar and `FILE`/`secure_file_priv` constraints (match `CHARACTER SET` on both sides for round-tripping)88- **`mariadb-import`** — the command-line wrapper that issues `LOAD DATA INFILE` per file89- **`mariadb-insert`** / **`mariadb-replace`** — duplicate-key handling: `IGNORE` semantics and `REPLACE` (delete-then-insert) caveats that `LOAD DATA … REPLACE` inherits90- Canonical references on `mariadb.com/docs` (consult only for edge cases not covered here):91 - <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile>92 - <https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-xml>9394<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>
mariadb-corporation