mysql数据库LOAD DATA INFILE Syntax

1.LOAD DATA INFILE用来把一个文本文件里的内容高速写入到MySQL表里，它和SELECT ... INTO FILE的操作是对应的，一个导入、一个导出。使用LOAD DATA INFILE需要MySQL对要导入的文件有读权限，其语法如下：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_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,...]

SELECT ... INTO OUTFILE 'file_name'
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
FROM TABLE_NAME;

 

select * into outfile 'xxx.txt' CHARACTER SET utf8 FIELDS TERMINATED BY ',' from emails1;

 

[LOW_PRIORITY | CONCURRENT]
LOW_PRIORITY： 当加上此选项时，执行LOAD DATA命令将会被延迟，直到没有没有任何其它客户端读取目标表。LOW_PRIORITY选项只适用于表级锁的存储引擎（MyISAM、MEMORY、MERGE等）。
CONCURRENT： 并行的意思，如果指定该选项，意味着在LOAD DATA过程中，别人也可以读取表里的信息。如果使用CONCURRENT选项，不管有没有人在LOAD DATA过程中读取目标表，都是对LOAD DATA的性能造成一定的影响。对于基于行级别的Replication，CONCURRENT选项在MySQL 5.5.1以前不可用。

 

 

[LOCAL]
LOCAL选项决定LOAD DATA文件的位置以及LOAD DATA过程中出错后的处理方式。
如果参数--local-infile=0，那么LOCAL参数不可用。
加上LOCAL选项后，在LOAD DATA的时候，客户端读取文件，并发送给服务端，该文件会临时存储在服务端的临时文件夹下（系统临时文件夹下，而非MySQL服务的临时文件夹，不是tmpdir、slave_load_tmpdir所指定的文件夹）。
使用LOCAL选项，LOAD DATA的速度会稍微慢一点。
如果不加LOCAL选项，当发生数据错误或者主键重复、唯一索引重复的时候，LOAD DATA将会中断，而加上LOCAL后，将不会中断，关于加上LOCAL后遇到上述错误的处理方式，看下面的IGNORE选项中的解释。

 

关于文件的路径问题：
1、如果指定了LOCAL选项，则被导入的文件可以在客户端、也可以在服务端，如果没有指定LOCAL，则被导入的文件必须在服务端；
2、我们尽量使用绝对路径来标识被导入的文件。如果我们使用了相对路径：
   a)在指定LOCAL选项的情况下，相对路径指向客户端所在的目录；
   b)在没有指定LOCAL的选项下，相对路径指向数据文件所在目录或MySQL服务默认库所在目录；
   --> 如果该相对路径是’./a.txt’，那么指向数据文件所在目录；
   -->如果该相对路径是’a.txt’，那么指向MySQL服务默认库所在目录；

 

 

[REPLACE | IGNORE]
REPLACE： 在导入过程中遇到重复的行、与主键或唯一索引重复的行，将被替换掉
IGNORE： 在导入过程中遇到重复的行、与主键或唯一索引重复的行，会跳过并继续导入
在没有指定REPLACE和IGNORE的情况下，当遇到上述情况时，处理方法取决于是否指定了LOCAL选项：
如果没有指定LOCAL，则会中断导入操作；
如果指定了LOCAL，遇到上述情况时按照IGNORE参数处理。
如果想要忽略外键对LOAD DATA的影响，可以在导入前设置参数：SET foreign_key_checks = 0

 

 

[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]

FIELDS 用于定义文件的分割字段
TERMINATED BY：定义字段分隔符
ENCLOSED BY：定义字段括起字符，比如：”WYZC”，则ENCLOSED BY ‘”’
[OPTIONALLY]：有选择行的，如果加上此选项，则数字类型不加引号，其它类型的加引号
ESCAPED BY：定义转义字符

 

 

 

[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]

LINES定义每行的分割字段
STARTING BY：定义每行从哪个字段开始读取
TERMINATED BY：定义每行的终止符

 

 

如果没有指定FIELDS或者LINES，则默认是：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

 

 

[IGNORE number {LINES | ROWS}]

IGNORE number LINES选项可被用来忽略在文件开始的一个列名字的头：

 

 

LOAD DATA INFILE和SELECT ... INTO OUTFILE的一些Tips：
对于LOAD DATA INFILE和SELECT ... INTO OUTFILE，参数FIELDS和LINES必须一致，如果同时指定这2个参数，那么FIELDS必须在LINES之前。
对于一个MyISAM空表，对于表里面的非唯一性索引，在导入的时候会分批次创建，这样会加快LOAD DATA的速度。
有时候我们可以在导入前禁用一个表的索引，导入完成后再ENABLE该表的索引以加快LOAD DATA的速度。
从MySQL 5.6.2开始，LOAD DATA支持分区表的导入。

当你与LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE将一个数据库的数据写进一个文件并且随后马上将文件读回数据库时，两个命令的字段和处理选项必须匹配，否则，LOAD DATA INFILE将不能正确解释文件的内容。假定你使用SELECT ... INTO OUTFILE将由逗号分隔的字段写入一个文件：