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, |
(col_name_or_mask [, col_name_or_mask] ...) |
|
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)