MySQL从文本文件批量导入数据

要从外部文件中方便地引入数据从而创建表，MySQL提供了下面的方便方法：假定存在一个表pet，建立一个文本文件pet.txt，每一行为一个记录，记录的每个字段用TAB分开，记录中字段一一对应，若有空字段，应使用NULL来表示，对于文本文件中，则用 \N表示。

To load the text file pet.txt into the pet table, use this statement:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet

-> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement if you wish, but the de-

faults are tab and linefeed. These are sufficient for the statement to read the file pet.txt properly.

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]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]

转载于:https://my.oschina.net/qianyuming/blog/701098