MySQL load data 操纵数据

文章目录

1. 1. Load Data
2. 2. 工具使用语法
3. 3. LOW_PRIORITY & CONCURRENT
   1. 3.1. 注释
4. 4. 如果您希望在载入运行过程中忽略外键的限制，您可以在执行LOAD DATA前发送一个SET FOREIGN_KEY_CHECKS=0语句
   1. 4.1. 注释
5. 5. LOAD DATA INFILE &SELECT…INTO OUTFILE
6. 6. 用例如下：
7. 7. 实际生产中用到 的和字段对应关系

总结

Load Data

作为数据库工程师，跟数据打交道是常见的事情，数据导入导出
也是常遇到的事情。下面对mysql的load data 作一些学习和总结。
通过对5.1官方文档学习，并记录一下

工具使用语法

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

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串

LOW_PRIORITY & CONCURRENT

LOW_PRIORITY: LOAD DATA语句的执行被延迟，直到没有其它的客户端从表中读取为止.
CONCURRENT :如果一个MyISAM表满足同时插入的条件（即该表在中间有空闲块），LOAD
DATA正在执行时，其它线程会从表中重新获取数据。即使没有其它线程在同时使用本表格
使用本选项也会略微影响LOAD DATA的性能

LOCAL : 如果指定了LOCAL，则被认为与连接的客户端有关,如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录
如果LOCAL没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。
当在服务器主机上为文件定位时，服务器使用以下规则：

1. 如果给定了一个绝对的路径名称，则服务器使用此路径名称。
2. 如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。 
3. 如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。

注释

如果使用了LOCAL 参数，其实会比直接操作数据库文件慢，毕竟每次都会通过mysql客户
端来处理文件，再发送给sever 处理。

####REPLACE & IGNORE

如果您指定了REPLACE，则输入行会替换原有行（换句话说，与原有行一样，对一个主索引或唯一索引具有相同值的行）

如果您指定IGNORE，则把原有行复制到唯一关键字值的输入行被跳过

如果您这两个选项都不指定，则运行情况根据LOCAL关键词是否被指定而定。不使用LOCAL
时，当出现重复关键字值时，会发生错误，并且剩下的文本文件被忽略。使用LOCAL时，
默认的运行情况和IGNORE被指定时的情况相同；这是因为在运行中间，服务器没有办法中
止文件的传输。

如果您希望在载入运行过程中忽略外键的限制，您可以在执行LOAD DATA前发送一个SET FOREIGN_KEY_CHECKS=0语句

注释

当您有许多索引时，这通常会使LOAD DATA INFILE大大加快。通常，LOAD DATA INFILE的速度会非常快，但是在某些极端情况下，您可以在把文件载入到表中之前使用ALTER TABLE...DISABLE KEYS关闭LOAD DATA INFILE，或者在载入文件之后使用ALTER TABLE...ENABLE KEYS再次创建索引，使创建索引的速度更快

LOAD DATA INFILE &SELECT…INTO OUTFILE

LOAD DATA INFILE是SELECT…INTO OUTFILE的补语,要从一个表中把数据
写入一个文件中，应使用SELECT…INTO OUTFILE。要读取文件，放回到表中，
应使用LOAD DATA INFILE。FIELDS和LINES子句的语法对于两个语句是一样的。
两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。

如果您指定了一个FIELDS子句，则每个亚子句（TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY）也是自选的。不过，您必须指定其中至少一个。如果您不指定FIELDS
子句，则默认值为假设您写下如下语句时的值：

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

如果您不指定LINES子句，则默认值为假设您写下如下语句时的值：

LINES TERMINATED BY '\n' STARTING BY '

换句话说，当读取输入值时，默认值会使LOAD DATA INFILE按如下方式运行：

用例如下：

示例１：

mysql> LOAD DATA INFILE '/tmp/test.txt'
     -> INTO TABLE test LINES STARTING BY "xxx";

使用此语句，您可以读入包含有如下内容的文件：

xxx"row",1
something xxx"row",2
并只得到数据("row",1)和("row",2)。

示例 2 ：
IGNORE number LINES选项可以被用于在文件的开始处忽略行。例如，您可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行
mysql> LOAD DATA INFILE ‘/tmp/test.txt’
-> INTO TABLE test IGNORE 1 LINES;

示例 3 ：当您联合使用SELECT…INTO OUTFILE和LOAD DATA INFILE来从一个数据库中把数据写入一个文件中，然后再读取文件，返回到数据库中时，用于两个语句的field-和line-handling 选项必须匹配。否则，LOAD DATA INFILE不会正确地理解文件的内容。
假设您使用SELECT…INTO OUTFILE来编写一个的文件，字段由逗号分隔：

mysql> SELECT * INTO OUTFILE 'data.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM table2;

要读取由逗号分隔的文件并返回，则正确的语句应该是：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->   FIELDS TERMINATED BY ',';

如果您尝试使用以下所示的语句读入文件，则不会运行，因为该语句命令LOAD DATA INFILE寻找位于字段之间的制表符：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

结果很可能是，每个输入行被理解为一个单一字段。

###LOAD DATA INFILE也可以被用于读取从外源中获取的文件。

示例 4 ：一个dBASE格式的文件具有以逗号分隔并且包含在双引号中的字段。如果文件中的各行以新行为结尾，则此处所示的语句描述了您可以用于载入文件的field-和line-handling选项：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

所有field-或line-handling选项都可以指定一个空字符串(‘’) 如果字符串不是空的，
则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须为单一字符。FIELDS TERMINATED BY, LINES STARTING BY和LINES TERMINATED BY值可以超过一个字符。例如，要编写由回车/换行成对字符作为结尾的行，或读取包含这类行的文件，则应指定一个LINES TERMINATED BY ‘\r\n’子句。

FIELDS [OPTIONALLY] ENCLOSED BY用于控制字段的引号。对于（SELECT…INTO OUTFILE），如果您忽略了词语OPTIONALLY，则所有的字段都被包含在ENCLOSED BY字符串中。此处展示了此类输出的一个示例（使用逗号作为字段分隔符）：

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

如果您指定了OPTINALLY，则ENCLOSED BY字符只被用于包含具有字符串数据类型
（比如CHAR, BINARY, TEXT或ENUM）的列中的值：

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

...

实际生产中用到 的和字段对应关系

load data infile '/data/tmp/load/test.vp' replace into table mdm_biz_list_interface fields terminated by ',' LINES TERMINATED BY '\n'  ( $cols ) ;

总结

load data 对于mysql 来讲 就相当于oracle的 sql loader ，这些都是作为一个DBA
的基本工作技能。 mark一下，记做笔记  ...