文章目录
  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官方文档学习,并记录一下

    工具使用语法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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
    1. 如果给定了一个绝对的路径名称,则服务器使用此路径名称。
    2. 如果给定了带有一个或多个引导组件的相对路径名称,则服务器会搜索相对于服务器数据目录的文件。
    3. 如果给定了一个不带引导组件的文件名称,则服务器会在默认数据库的数据库目录中寻找文件。
    注释

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

    ####REPLACE & IGNORE

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

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

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

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

    注释
    1
    当您有许多索引时,这通常会使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
    子句,则默认值为假设您写下如下语句时的值:

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

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

    1
    LINES TERMINATED BY '\n' STARTING BY '

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

    用例如下:

    示例1:

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

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

    1
    2
    3
    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选项:

    1
    2
    3
    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
    
    ...
    

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

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

    总结

    1
    2
    load data 对于mysql 来讲 就相当于oracle的 sql loader ,这些都是作为一个DBA
    的基本工作技能。 mark一下,记做笔记 ...
    文章目录
    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. 实际生产中用到 的和字段对应关系
  • 总结