MySQL SELECT ... INTO OUTFILE和LOAD DATA INFILE用法

老书虫 2016-06-20 05:05:04

SELECT ... INTO OUTFILE用来把MySQL表中的数据读取到一个文件中，其语法如下：

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

LOW_PRIORITY：当加上此选项时，执行LOAD DATA命令将会被延迟，直到没有没有任何其它客户端读取目标表。LOW_PRIORITY选项只适用于表级锁的存储引擎（MyISAM、MEMORY、MERGE等）。

CONCURRENT：并行的意思，如果指定该选项，意味着在LOAD DATA过程中，别人也可以读取表里的信息。如果使用CONCURRENT选项，不管有没有人在LOAD DATA过程中读取目标表，都是对LOAD DATA的性能造成一定的影响。对于基于行级别的Replication，CONCURRENT选项在MySQL 5.5.1以前不可用。

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的情况下，当遇到上述情况时，处理方法取决于是否指定了LOCAL选项：

如果没有指定LOCAL，则会中断导入操作；

如果指定了LOCAL，遇到上述情况时按照IGNORE参数处理。

如果想要忽略外键对LOAD DATA的影响，可以在导入前设置参数：SET foreign_key_checks = 0

图片

FIELDS用于定义文件的分割字段

TERMINATED BY：定义字段分隔符

ENCLOSED BY：定义字段括起字符，比如：”WYZC”，则ENCLOSED BY ‘”’

[OPTIONALLY]：有选择行的，如果加上此选项，则数字类型不加引号，其它类型的加引号

ESCAPED BY：定义转义字符

LINES定义每行的分割字段

STARTING BY：定义每行从哪个字段开始读取

TERMINATED BY：定义每行的终止符

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

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

LINES TERMINATED BY '\n' STARTING BY ''

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支持分区表的导入。

接下来用一些实验来验证我们上面的理论：

1、SELECT ... INTO FILE，用实验结果演示下FIELDS项下面ENCLOSED BY加与不加OPTIONALLY选项的区别

看到了吧，没有加OPTIONALLY之前，会对每一个字段都加上双引号，加上OPTIONALLY选项后，有选择行的加，对于数字类型的，就没有加双引号。

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

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

LINES TERMINATED BY '\n' STARTING BY ''

数据正常导入进去了，从侧面证明了，如果没有指定FIELDS或者LINES，则默认是：

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

其实对于上面的t12.txt里的内容，我们直接可以这样导入：load data infile '/tmp/t12.txt';

3、我们可以使用LOAD DATA有选择性的往表的某些特定列里导入数据，也可以再导入的过程中对数据进行一些运算。