论坛风格切换
您好,欢迎光临本站!   登录 注册新用户
  • 5066阅读
  • 1回复

[oracle]MySQL SELECT ... INTO OUTFILE和LOAD DATA INFILE用法 [复制链接]

上一主题 下一主题
 
发帖
34
黑豆
6
威望
138
贡献值
0
交易币
0
红豆
0
只看楼主 倒序阅读 0 发表于: 2016-06-20
SELECT ... INTO OUTFILE用来把MySQL表中的数据读取到一个文件中,其语法如下:




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










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




LOCAL选项决定LOAD DATA文件的位置以及LOAD DATA过程中出错后的处理方式。
如果参数--local-infile=0,那么LOCAL参数不可用。
加上LOCAL选项后,在LOAD DATA的时候,客户端读取文件,并发送给服务端,该文件会临时存储在服务端的临时文件夹下(系统临时文件夹下,而非MySQL服务的临时文件夹,不是tmpdirslave_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:在导入过程中遇到重复的行、与主键或唯一索引重复的行,会跳过并继续导入
在没有指定REPLACEIGNORE的情况下,当遇到上述情况时,处理方法取决于是否指定了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 INFILESELECT ... INTO OUTFILE,参数FIELDSLINES必须一致,如果同时指定这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有选择性的往表的某些特定列里导入数据,也可以再导入的过程中对数据进行一些运算。








我们可以再LOAD DATA语句最后面加上列的信息,从而指定往哪些列里导入数据。



接下来演示再导入数据的过程中通过变量来对导入的数据进行运算:





4、STARTING BY参数决定从每行的哪个字段开始导入数据:




从实验结果中,我们看到,id为1、2、4的行全部导入t3表了,而id=3的行在t11.txt里面没有wyzc字段,因此没有导入到t3表中。


更多Oracle精彩内容 请关注我:



快速回复
限100 字节
 
上一个 下一个