作者  罗小波
沃趣科技高级MySQL数据库工程师

load data语句详解

• 背景

  • 数据库版本：MySQL 5.7.18
  • 服务器信息：本地到处导入在10.10.30.241上演示，local远程导入在10.10.30.250上演示
  • 数据库参数配置：
    * 双一，secure_file_priv=''，log-bin，binlog_format=row，隔离级别RC，sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  • 参考资料：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

• 制造测试数据
  

1、语法解析

• 查看语法帮助信息
• 
  

• load data语句加载的数据源可以是mysqldump导出的纯文本数据文件，也可以是使用SELECT … INTO OUTFILE '/path/xx.txt';语句生成的单表纯文本数据文件，或者其他的方式生成的txt（只要生成的纯文本数据列按指定分隔符分割的纯文本数据文件即可）

• 从上面的帮助信息可以看到整个load data语句的语法结构，其中load data infile 'file.txt' into table tb_name; 是最基本的使用语句结构，其余的都为可选子句

1.1. 必选子句或关键字

• load data语句简单示例

• 如果文本文件中的数据字段与表结构中的字段定义顺序相同，则直接使用如下语句载入即可

• 如果文本文件中的数据字段与表结构中的字段定义顺序不同，则使用如下语句指定载入表中的字段顺序

1.2. 可选子句或关键字

• 以下演示部分只针对部分子句或关键字做演示，并不是全部，悉知

• 如果load data语句使用了LOW_PRIORITY关键字，则在碰到其他会话操作相同表时，则会延迟执行LOAD DATA语句，直到其他会话操作表结束为止。这仅影响使用表级锁定的存储引擎（如MyISAM，MEMORY和MERGE），对于innodb存储引擎不起作用，因为innodb引擎是行级锁，对于load data新插入的不同的数据行之间的操作不会发生冲突。本小节不做演示，更多信息参考链接：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

• 如果要载入的文本文件不在mysql server数据库本身的本地磁盘，客户端也不是从mysql server本机登录的，则需要使用local关键字，指定mysql server从client host本地加载该文件，需要mysql server端使用local_infile=true(或者设置为1，不设置时默认为1)启动，以及客户端连接mysql server时也使用local_infile=true(或者设置为1，不指定时默认为1)连接才能使用，server和client必须都开启这个参数才能使用local关键字，任意一个关闭都不能使用

• 使用local关键字与不使用local关键字时load data语句加载文本文件的流程

  • 如果指定了LOCAL，则该客户端程序在客户端主机上读取load data语句需要的文件并将其发送到服务器。该文件可以使用完整路径名称来指定其位置。也可以使用相对路径，使用相对路径时，路径前缀为使用客户端程序时的工作目录，当使用带有LOAD DATA的LOCAL关键字时，会在mysql server端的临时目录中创建该文件的副本(注意：这个场景下该文件的路径不是由tmpdir或slave_load_tmpdir的值指定的，而是操作系统的临时目录/tmp，并且在MySQL server中这个存放副本的路径是不可配置的（通常，系统临时目录是Linux系统上的/tmp，Windows上的C:\WINDOWS\TEMP），要注意，如果在该临时目录下创建load文件的副本时发现磁盘空间不够，会导致LOAD DATA LOCAL语句执行失败。在客户端主机读取文本文件定位规则是如果指定的是绝对路径，则使用绝对路径，如果是相对路径，则在登录mysql server时的工作路径下查找，找不到就报错：ERROR 2 (HY000): File 'test3.txt' not found (Errcode: 2 - No such file or directory)
  • 如果未指定LOCAL，则该文件必须位于mysql server端主机上，并由服务器直接读取。服务器使用以下规则来定位文件：
    * 如果文件名是绝对路径名， mysql server将按照给定的路径读取文件
    * 如果文件名是一个相对路径名，则mysql server将在server端的datadir下搜索该文件，如果load data语句指定了库名，则在datadir的指定库名下搜索文本文件，如果没有指定库名，则在默认数据库下搜索文本文件（load data语句没有指定库名时要正确执行语句必须先使用use db语句切库，so，这个就是默认库）
    * 如果datadir下还找不到就报错：ERROR 13 (HY000): Can't get stat of '/datadir/xiaoboluo/test3.txt' (Errcode: 2 - No such file or directory)

• PS：

  • 非LOCAL方式只会加载位于mysql server上的文本文件。出于安全考虑，此类操作要求您具有FILE特权。而且，非本地加载操作也受到secure_file_priv系统变量的设置。如果变量值是非空目录名称，则要加载的文件必须位于该变量指定的目录中。如果变量值为空（这是不安全的，这个时候server本地导入由file权限控制，client远程主机的文件导入由server和client端的local_infile选项共同控制），则该文件只能由服务器读取。
  • 使用LOCAL比让服务器直接访问文件要慢一些，因为文件的内容必须通过客户端的连接发送到服务器。另一方面，您不需要FILE权限来加载本地文件。可以使用local子句
  • 使用local关键字时碰到唯一键值冲突时的处理方式与IGNORE关键字相同，忽略冲突的行

• 使用LOCAL会影响错误处理行为：
• 使用LOAD DATA INFILE，数据解析碰到重复键时默认情况下会终止操作

• 使用LOAD DATA LOCAL INFILE，数据解析碰到重复键时将发出警告，并且操作继续进行，因为server端无法在客户端操作期间停止传输文件。此时处理重复键与指定IGNORE关键字相同（即忽略主键冲突的数据行）

• REPLACE和IGNORE关键字控制对唯一键值冲突行的处理：

  • 如果指定了REPLACE关键字，则输入行将覆盖现有行。换句话说，与主键或唯一索引冲突的数据行将被执行覆盖写入，如果同时使用了local关键字，则与没有使用local关键字行为相同
  • 如果指定了IGNORE关键字，则与唯一键值冲突的数据行将被丢弃，如果同时使用了local关键字，则与没有使用local关键字行为相同
  • 如果不指定REPLACE和IGNORE任一选项，则行为取决于是否指定LOCAL关键字。没有LOCAL，则发现主键或唯一索引冲突时就报错终止load data语句执行，并忽略文本文件的其余部分的载入。如果使用了LOCAL关键字，则local关键字的默认行为与指定IGNORE时相同，这是因为server端无法在操作期间停止客户端的文件传输（不使用REPLACE与IGNORE关键字时的错误处理，详见1.2.2.2小节）
• 下面对使用REPLACE与IGNORE关键字进行演示
• 如果文本文件中的数据字段有与表结构中的唯一索引、主键索引冲突的，则使用REPLACE关键字，该关键字会对冲突的数据进行覆盖（内部转换为UPDATE，见后续章节示例）

• 也可以使用IGNORE关键字忽略冲突的行（注意，这里的ignore与ignore number lines子句中的ignore作用不同，不要搞混淆）

• LOAD DATA支持使用PARTITION选项显式分区选择，其中包含一个或多个分区，子分区或两者名称的逗号分隔列表。当使用此选项时，如果文件中的任何行无法插入到列表中指定的任何分区或子分区中，则该语句将失败，并显示错误，找到与给定分区集不匹配的行，本小节不做演示，更多信息参考链接：https://dev.mysql.com/doc/refman/5.7/en/load-data.html

• 导入的文本数据文件名必须以文字字符串形式给出。在Windows上，在路径名称中指定反斜杠为正斜杠或双倍反斜杠。 character_set_filesystem系统变量控制文件名的解释

• 服务器使用由character_set_database系统变量指定的字符集来解释文件中的内容。SET NAMES语句和character_set_client系统变量的设置不影响文件内容的字符集解析。如果输入文件的内容使用的字符集与server的默认值字符集不同，则建议先使用load data的CHARACTER SET子句指定解析文本文件内容的字符集

• LOAD DATA INFILE将文件中的所有字段以相同的字符集进行解析，而不管加载字段列的数据类型定义的字符集如何。为了正确解释文件内容，您必须确保使用正确的字符集进行导出数据和导入数据。例如，如果您使用mysqldump -T或通过在mysql中执行SELECT … INTO OUTFILE语句导出数据文件时，建议使用–default-character-set选项指定一个对应数据的字符集，以便使用Load data语句导入数据时不会发生字符集错乱（使用mysqlimport的–default-character-set指定导出数据时的字符集，使用mysql命令行客户端的–default-character-set指定导出数据时的字符集，注：不能指定ucs2,utf16,utf16le或utf32字符集来加载数据文件）
• 本小节不做演示，更多信息参考链接：https://dev.mysql.com/doc/refman/5.7/en/load-data.html