LOAD DATA INFILE 语法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name [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,...]

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

• If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 13.2.8, “REPLACE Syntax”. 
• If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. 
• If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

For both the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements, the syntax of the FIELDS and LINES clauses is the same. Both clauses are optional, but FIELDS must precede LINES if both are specified.

f you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

LINES TERMINATED BY '\n' STARTING BY ''

If all the lines you want to read in have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip over the prefix, and anything before it. If a line does not include the prefix, the entire line is skipped. Suppose that you issue the following statement:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test

  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

If the data file looks like this:

xxx"abc",1

something xxx"def",2

"ghi",3

The resulting rows will be ("abc",1) and ("def",2). The third row in the file is skipped because it does not contain the prefix.å

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

一些不能被LOAD DATA INFILE 支持的情况：

•     固定尺寸的记录行（FIELDS TERMINATED BY 和FIELDS ENCLOSED BY 均为空）和BLOB 或TEXT 列。

•     如果用户指定一个分隔符与另一个相同，或是另一个的前缀，LOAD DATA INFILE 可能会不能正确地解释输入。例如，下列的FIELDS 子句将会产生问题：

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

•    

•     如果FIELDS ESCAPED BY 为空，一个字段值中包含有FIELDS ENCLOSED BY 或LINES TERMINATED BY 后面紧跟着FIELDS TERMINATED BY 的值时，将会引起LOAD DATA INFILE 过早地停止读取一个字段或一行。这是因为LOAD DATA INFILE 不知道字段或行值在哪里结束。