使用LOAD DATA进行数据导入 - PolarDB - Alibaba Cloud Documentation Center

This topic describes the limits and notes when you execute LOAD DATA statements to import data to tables in PolarDB-X databases.

Note

LOAD DATA statements are not transactions. If a failure such as a database breakdown occurs when a LOAD DATA statement is executed, some data may fail to insert.
local-infile must be enabled on your client.

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] ...)]

Parameter description

Parameter	Description
LOAD DATA [LOCAL] INFILE	Specifies whether the file is stored on the server or client.
file_name	If the file name is a relative path, the path is relative to the path where the client is started.
REPLACE	Replaces an existing row with the row that you want to import when the primary key in the new row has the same value as the primary key in the existing row.
IGNORE	Discards the row that you want to import when the primary key in the new row has the same value as the primary key in the existing row.
[FIELDS] TERMINATED BY 'string'	The delimiter used to separate fields. Default value: \t.
[FIELDS] ENCLOSED BY 'char'	The characters used to enclose each column of data. For example, if the "test" string is in a column and this parameter is set to enclosed by '"', the double quotation marks ("") are removed from "test" before data import.
[LINES] TERMINATED BY 'string'	The delimiter used to separate rows of data. Default value: \n.
IGNORE number LINES	The number of first rows that you want to ignore in the file. For example, `IGNORE 1 LINES` specifies that the first row of data is ignored when data is imported.
(col_name_or_mask [, col_name_or_mask] ...)	The columns that you want to import. If you do not specify this parameter, data is imported in the column order by default. The columns in the file that you want to ignore. If you specify this parameter, the specified columns in the file are ignored when data is imported. For example, data is imported from a file to the `table test(x int, y int)` table. The file has three columns. If you specify `(x, @name, y)` for data import, the second column in the file is ignored. The x column in the table is filled with the values of the first column in the file and the y column in the table is filled with the values of the third column in the file.

Examples

Create a test table.

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

The following code block provides the content of the local file that you want to import:

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

To import the file, execute the following LOAD DATA statement:

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

The following result is returned:

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)