How to import data using LOAD DATA-PolarDB(PolarDB)-阿里云帮助中心

LOAD DATA reads rows from a text file and inserts them into a PolarDB-X table. It is the fastest way to bulk-load flat files and works with both local client files (LOCAL) and server-side files.

Before you begin

Enable `local-infile` on your client before running any LOAD DATA LOCAL INFILE statement.
`LOAD DATA` is not transactional. If a failure such as a database crash occurs mid-import, some rows may be inserted and others may not. Always verify the row count after a large import.

Syntax

LOAD DATA
    [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_mask [, col_name_or_mask] ...)]

Parameters

Parameter	Description
`LOCAL`	Reads the file from the client host. If omitted, reads from the server host.
`file_name`	Path to the source file. Relative paths are resolved from the directory where the client was started.
`REPLACE` \| `IGNORE`	Controls behavior when a row in the file has the same primary key as an existing row. See Handling duplicate primary keys.
`CHARACTER SET charset_name`	Character set of the input file.
`[FIELDS] TERMINATED BY 'string'`	Field delimiter. Default: `\t` (tab).
`[FIELDS] ENCLOSED BY 'char'`	Character used to enclose field values. For example, `ENCLOSED BY '"'` strips surrounding double quotes before inserting.
`[FIELDS] ESCAPED BY 'char'`	Escape character.
`[LINES] STARTING BY 'string'`	Prefix to strip from the beginning of each line before parsing.
`[LINES] TERMINATED BY 'string'`	Row delimiter. Default: `\n`.
`IGNORE number LINES` \| `IGNORE number ROWS`	Number of leading lines to skip. Use this to skip header rows. For example, `IGNORE 1 LINES` skips the first line.
`(col_name_or_mask, ...)`	Maps file columns to table columns. Use a column name to import that column, or a user variable (such as `@dummy`) to discard a column. If omitted, columns are mapped in order.

Handling duplicate primary keys

When a row in the file shares a primary key with an existing row in the table, the outcome depends on which keyword you specify:

Keyword	Behavior	When to use
`REPLACE`	The existing row is replaced with the incoming row.	The file contains the authoritative, up-to-date version of the data.
`IGNORE`	The incoming row is silently discarded; the existing row is kept.	The file may contain duplicates, but the table's existing data takes precedence.

Examples

Import a delimited file with a header row

The following example creates a partitioned table and loads data from a local file that uses commas as field delimiters, strips a test line prefix from each row, and skips one header row.

Create the table:

CREATE TABLE test (
    a int(11) NOT NULL DEFAULT '0',
    b varchar(8) NOT NULL,
    PRIMARY KEY (a)
) DBPARTITION BY hash(a);

Source file (`~/test.txt`):

x,y
test1,2
test2,3
test3,4
test4,5
test5,6
test7,8
test8,9

Import statement:

LOAD DATA LOCAL INFILE '~/test.txt'
    IGNORE
    INTO TABLE test
    FIELDS TERMINATED BY ','
    LINES STARTING BY 'test' TERMINATED BY '\n'
    IGNORE 1 LINES;

STARTING BY 'test' strips the test prefix from each line before parsing, so test1,2 becomes 1,2. IGNORE 1 LINES skips the x,y header row.

Expected result:

SELECT * FROM test ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    1 | 2    |
|    2 | 3    |
|    3 | 4    |
|    4 | 5    |
|    5 | 6    |
|    7 | 8    |
|    8 | 9    |
+------+------+
7 rows in set (0.02 sec)

Import a CSV file

For standard CSV files with double-quoted fields and a header row:

LOAD DATA LOCAL INFILE '/path/to/data.csv'
    INTO TABLE tbl_name
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

Skip columns in the source file

To import only selected columns from a file that has more columns than the target table, use a user variable as a placeholder to discard unwanted columns.

The following example imports a three-column file into a two-column table, discarding the second column:

-- Table: test(x int, y int)
-- File columns: col1, col2 (discarded), col3
LOAD DATA LOCAL INFILE '/path/to/data.txt'
    INTO TABLE test
    (x, @dummy, y);

x receives values from the first file column, @dummy absorbs and discards the second, and y receives values from the third.