LOAD DATA

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] ...) 1. The columns that you want to import. If you do not specify this parameter, data is imported in the column order by default. 2. 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:

mysql> 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)

results matching ""

    No results matching ""