MySQL的SQL语句 - 数据操作语句（9）- LOAD DATA 语句(1)

数据库杂货铺 2021-04-12

428

LOAD DATA 语句

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

LOAD DATA 语句高速将文本文件中的行读入表中。LOAD DATA 是 SELECT ... INTO OUTFILE 语句的补充。要将数据从表写入文件，请使用 SELECT ... INTO OUTFILE 语句。要将文件数据读回表中，请使用 LOAD DATA。对于这两个语句，FIELDS 和 LINES 子句的语法是相同的。

还可以使用 mysqlimport 实用程序加载数据文件；mysqlimport 通过向服务器发送 LOAD DATA 语句来操作。

分区表支持

通过使用 PARTITION 选项和一个或多个以逗号分隔的分区、子分区的名称的列表，LOAD DATA 支持显式分区选择。使用此选项时，如果无法将文件中的某行插入列表中指定的分区或子分区中，则语句将失败并返回错误：Found a row not matching the given partition set。

输入文件名、位置和内容解析

文件名必须以文本字符串的形式给出。在 Windows 上，将路径名中的反斜杠指定为正斜杠或双反斜杠。character_set_filesystem 系统变量控制文件名字符集的解析。

服务器使用 character_set_database 系统变量指示的字符集来解析文件中的信息。SET NAMES 和 character_set_client 的设置不影响输入的解析。如果输入文件的内容使用与默认值不同的字符集，通常最好使用 CHARACTER SET 子句指定文件的字符集。binary 字符集指定“no conversion”。

LOAD DATA 将文件中的所有字段解释为具有相同的字符集，无论字段值加载到的列是什么数据类型。要正确解释文件内容，必须确保使用正确的字符集写入文件。例如，如果使用 mysqldump -T 或在 mysql 中通过发出 SELECT ... INTO OUTFILE 命令来写数据文件，一定要使用 --default-character-set 选项，以便输出文件使用 LOAD DATA 语句加载数据文件时使用的字符集。

注意

无法加载使用 ucs2、utf16、utf16le 或 utf32 字符集的数据文件。

如后面所述，LOCAL 修饰符影响文件的预期位置和错误处理。只有在服务器和客户端都配置为允许时，LOCAL 才起作用。例如，如果 mysqld 是在禁用 local_infile 系统变量的情况下启动的，则 LOCAL 不起作用。

LOCAL 修饰符会影响文件的查找位置：

● 如果指定了 LOCAL，则客户端程序从客户端主机上读取该文件并将其发送到服务器。可以给出文件完整路径名，指定其确切位置。如果指定相对路径，则是指相对于客户端程序的路径名称。

当 LOCAL 和 LOAD DATA 一同使用时，会在 MySQL 服务器存储临时文件的目录中创建文件的副本。如果此目录中缺少足够的空间用于复制，可能会导致 LOAD DATA LOCAL 语句失败。

● 如果未指定 LOCAL，则文件必须位于服务器主机上，并由服务器直接读取。服务器使用以下规则查找文件：

■ 如果文件名是绝对路径名，服务器将按指定的位置使用它。

■ 如果文件名是包含一个或者多个前导成份的相对路径名，则服务器将相对于数据目录搜索该文件。

■ 如果是没有指定前导成份的文件名，服务器将在默认数据库的数据库目录中查找该文件。

在不用 LOCAL 的情况下，这些规则意味着从服务器的数据目录中读取名为 ./myfile.txt 的文件，从默认数据库的数据库目录中读取 myfile.txt 文件。例如，如果 db1 是默认数据库，那么下面的 LOAD DATA 语句将从 db1 的数据库目录中读取 data.txt 文件，即使语句显式地将文件加载到 db2 数据库中的表中：

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

注意

服务器还使用非 LOCAL 规则来为 IMPORT TABLE 语句定位 .sdi 文件。

非 LOCAL 加载操作读取服务器上的文本文件。出于安全原因，此类操作要求具有 FILE 权限。此外，非 LOCAL 加载操作也受 secure_file_priv 系统变量设置的影响。如果变量值是非空的目录名，则要加载的文件必须位于该目录中。如果变量值为空（这是不安全的），则文件只需服务器可读取就行。

使用 LOCAL 比让服务器直接访问文件要慢一些，因为文件内容必须由客户端发送到服务器。另一方面，加载本地文件不需要 FILE 权限。

LOCAL 也会影响错误处理：

● 使用 LOAD DATA，数据解析和重复键错误会终止操作。

● 使用 LOAD DATA LOCAL，数据解析和重复键将变成警告，并且操作将继续，因为服务器无法在操作过程中停止文件的传输。对于重复键错误，这与指定 IGNORE 相同，将在本节后面进一步解释 IGNORE。

复制注意事项

对于基于语句的复制，LOAD DATA 被认为是不安全的。如果在 binlog_format=STATEMENT 时使用 LOAD DATA 语句，则会在应用更改的复制从属服务器上创建一个包含该数据的临时文件。如果服务器上的二进制日志加密启用，请注意此临时文件未加密。当需要加密时，请确保使用基于行的或混合的二进制日志记录格式，这不会创建临时文件。

并发性注意事项

如果使用 LOW_PRIORITY 修饰符，LOAD DATA 语句的执行将被延迟，直到没有其他客户端从表中读取数据。这只影响只使用表级锁定的存储引擎（如 MyISAM、MEMORY 和 MERGE）。

如果对满足并发插入条件的 MyISAM 表指定 CONCURRENT 修饰符（即中间不包含空闲块），则在执行 LOAD DATA 语句时，其他线程可以从表中检索数据。即使没有其他线程同时使用表，此修饰符也会对 LOAD DATA 的性能产生一些影响。

重复键处理

REPLACE 和 IGNORE 修饰符控制对与现有行唯一键值重复的输入行的处理：

● 如果指定 REPLACE，则输入行将替换现有行。

● 如果指定 IGNORE，则将丢弃与现有行唯一键值上重复的行。

● 如果两个修饰符都不指定，则行为取决于是否指定了 LOCAL 修饰符。如果没有 LOCAL，则当找到重复键值时会发生错误，并且忽略文本文件的其余部分。如果有 LOCAL，默认行为与指定 IGNORE 时相同；这是因为服务器无法在操作过程中停止文件的传输。

索引处理

要在加载操作期间忽略外键约束，请在执行 LOAD DATA 之前执行 SET foreign_key_checks = 0 语句。

如果对空 MyISAM 表执行 LOAD DATA 语句，则所有非唯一索引都将在单独的批处理中创建（类似于 REPAIR TABLE）。通常，当有多个索引时，这会使 LOAD DATA 的速度更快。在某些极端情况下，在向表加载文件数据之前通过使用 ALTER TABLE ... DISABLE KEYS 关闭索引，在加载之后使用 ALTER TABLE ... ENABLE KEYS 重建索引，这样可以更快地创建索引。

字段和行处理

FIELDS 和 LINES 子句的语法对于 LOAD DATA 和 SELECT ... INTO OUTFILE 语句是相同的。这两个子句都是可选的，但如果同时指定了这两个子句，则 FIELDS 必须位于 LINES 之前。

如果指定了 FIELDS 子句，那么它的每个子句（TERMINATED BY, [OPTIONALLY] ENCLOSED BY 和 ESCAPED BY）也是可选的，只是必须至少指定其中一个。这些子句的参数只能包含ASCII字符。

如果未指定 FIELDS 或 LINES 子句，则默认值与以下内容相同：

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

SQL 语句字符串中的反斜杠是 MySQL 转义字符。要指定字面量反斜杠，必须为要指定两个反斜杠。转义序列 '\t' 和 '\n' 分别指定制表符和换行符。

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

● 在换行符处查找行边界。

● 不跳过任何行前缀。

● 在制表符处将行拆分为字段。

● 不要期望字段用引号括起来。

● 将前面带有转义符\的字符解释为转义序列。例如，\t、\n 和 \\ 分别表示制表符、换行符和反斜杠。

相反，默认值会导致 SELECT ... INTO OUTFILE 输出时，按如下方式操作：

● 在字段之间输出制表符。

● 不将字段括在任何引用符号内。

● 使用 \ 转义字段值中出现的制表符、换行符或 \ 的实例。

● 在行尾写换行符。

注意

对于在 Windows 系统上生成的文本文件，正确的文件读取可能需要 LINES TERMINATED BY '\r\n' 语句，因为 Windows 程序通常使用两个字符作为行终止符。某些程序（如 WordPad）可能在写入文件时 \r 用作行终止符。若要读取此类文件，请使用 LINES TERMINATED BY '\r' 语句。

如果所有输入行都有一个要忽略的公共前缀，则可以使用 LINES STARTING BY 'prefix_string' 语句跳过前缀及其前面的任何内容。如果一行不包含前缀，则跳过整行。假设执行以下语句：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

如果数据文件如下所示：

xxx"abc",1
something xxx"def",2
"ghi",3

结果行将是 ("abc",1) 和 ("def",2)。文件中的第三行被跳过，因为它不包含前缀。

IGNORE number LINES 选项可用于忽略文件开头的行。例如，可以使用 IGNORE 1 LINES 跳过包含列名的初始标题行：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

配合 LOAD DATA 用 SELECT ... INTO OUTFILE 语句将数据库中的数据写入文件，然后将文件读回数据库，这两个语句的字段和行处理选项必须匹配。否则，LOAD DATA 将无法正确解析文件的内容。假设使用 SELECT ... INTO OUTFILE 语句以逗号作为字段分隔符写入文件：

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

要读取逗号分隔的文件，正确的语句是：

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

如果尝试使用如下所示的语句读取文件，则它将无法工作，因为它指示 LOAD DATA 在字段之间查找制表符：

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

可能的结果是，每个输入行都会被解释为一个字段。

LOAD DATA 可用于读取从外部数据源获取的文件。例如，许多程序可以以逗号分隔值（CSV）格式导出数据，这样行中的字段用逗号分隔，并用双引号括起来，起始行为列名。如果此类文件中的行由回车符/换行符对终止，则此处显示的语句说明了用于加载文件的字段和行处理选项：

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

如果输入值不必要用引号括起来，请在 ENCLOSED BY 选项之前使用 OPTIONALLY。

任何字段或行处理选项都可以指定空字符串('')。如果不为空，则 FIELDS [OPTIONALLY] ENCLOSED BY 和 FIELDS ESCAPED BY 值必须是单个字符。FIELDS TERMINATED BY、LINES STARTING BY 和 LINES TERMINATED BY 值可以是多个字符。例如，若要写入由回车符/换行符对终止的行，或要读取包含此类行的文件，请指定 LINES TERMINATED BY '\r\n' 子句。

要读取包含笑话的文件，这些笑话由%%组成的行分隔，可以执行以下操作

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

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"

如果指定 OPTIONALLY，则 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

在字段值中出现 ENCLOSED BY 字符时，通过在它们前面加上 ESCAPED BY 字符来转义。另外，如果指定一个空的 ESCAPED BY 值，则可能无意中生成无法由 LOAD DATA 正确读取的输出。例如，如果转义符为空，刚才显示的输出将如下所示。请注意，第四行中的第二个字段在引号后面包含一个逗号，它（错误地）终止了该字段：

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

对于输入，如果存在 ENCLOSED BY 字符，则从字段值剥离。（无论是否指定了 OPTIONALLY，都会这样处理；OPTIONALLY 对输入解析没有影响。）前面紧跟转义符的 ENCLOSED BY 字符被解析为当前字段值的一部分。

如果字段以 ENCLOSED BY 字符开头，则仅当后跟字段或行 TERMINATED BY 序列时，该字符才会被识别为字段值的终止。为了避免歧义，字段值中的 ENCLOSED BY 字符出现次数可以加倍，并解析为该字符的单个实例。例如，如果指定了 ENCLOSED BY '"'，则按如下所示处理引号：

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY 控制如何读写特殊字符：

● 对于输入，如果 FIELDS ESCAPED BY 字符不为空，则会删除该字符的出现，并将之后的字符作为字段值的一部分。一些两个字符序列例外，其中第一个字符是转义字符。下表显示了这些序列（使用\作为转义符）。本节后面将介绍对 NULL 处理的规则。

字符	转义序列
\0	ASCII NUL (X'00')
\b	退格符
\n	换行符
\r	回车符
\t	制表符
\Z	ASCII 26 (Control+Z)
\N	NULL

如果 FIELDS ESCAPED BY 字符为空，则不进行转义。

● 对于输出，如果 FIELDS ESCAPED BY 字符不为空，则用于在输出时为以下字符加前缀：

■ FIELDS ESCAPED BY 字符。

■ FIELDS [OPTIONALLY] ENCLOSED BY 字符。

■ 如果未指定 ENCLOSED BY 或者为空，则用 FIELDS TERMINATED BY 和 LINES TERMINATED BY 的第一个字符。

■ ASCII 0（转义符后面实际写入的是 ASCII 0，而不是零值字节）。

如果 FIELDS ESCAPED BY 字符为空，则不转义任何字符，并且 NULL 输出为 NULL，而不是 \N。指定空转义字符可能不是一个好主意，尤其是当数据中的字段值包含刚刚给出的列表中的任何字符时。

在某些情况下，字段和行处理选项相互作用：

● 如果 LINES TERMINATED BY 是空字符串，FIELDS TERMINATED BY 值非空，则行也以 FIELDS TERMINATED BY 字符结束。

● 如果 FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 都是空字符（''），对于固定行格式，字段之间不使用分隔符（但仍可以使用行终止符），相反，使用足够宽的字段来读取和写入列值，以容纳字段中的所有值。对于 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT，无论声明的显示宽度是多少，字段宽度分别为4、6、8、11和20。

LINES TERMINATED BY 仍用于分隔行。如果一行不包含所有字段，则其余列将设置为其默认值。如果没有行终止符，则应将其设置为''。在这种情况下，文本文件必须包含每行的所有字段。

固定行格式也会影响 NULL 值的处理，如后面所述。

注意

如果使用多字节字符集，固定大小格式不起作用。

NULL 值的处理因使用的 FIELDS 和 LINES 选项而异：

● 对于默认的 FIELDS 和 LINES 值，NULL 被写入字段值 \N 用于输出，字段值 \N 被读取为 NULL 作为输入（假设 ESCAPED BY 字符为\）。

● 如果 FIELDS ENCLOSED BY 不为空，则包含字面量 NULL 作为其值的字段将作为 NULL 值读取。这与用 FIELDS ENCLOSED BY 字符括起来的字段中的单词 NULL 不同，后者被读取为字符串 'NULL'。

● 如果 FIELDS ESCAPED BY 字符为空，则将 NULL 写成单词 NULL。

● 对于固定行格式（当 FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 都为空时使用此格式），NULL将作为空字符串写入。这会导致表中的空值和空字符串在写入文件时无法区分，因为它们都是作为空字符串写入的。如果需要在读回文件时区分这两种格式，则不应使用固定行格式。

尝试将 NULL 加载到 NOT NULL 列中会导致为该列分配隐式默认值并发出警告，或者在严格 SQL 模式下报错。

LOAD DATA 不支持某些情况：

● 固定大小的行（FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 都为空）以及 BLOB 或 TEXT 列。

● 如果指定一个与另一个相同的分隔符或另一个分隔符的前缀，则 LOAD DATA 无法正确解析输入。例如，以下 FIELDS 子句将引发问题：

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

● 如果 FIELDS ESCAPED BY 为空，如果字段值包含 FIELDS ENCLOSED BY 或者 LINES TERMINATED BY 字符，后面跟随 FIELDS TERMINATED BY 值的情况，则会导致 LOAD DATA 过早停止读取字段或行。发生这种情况是因为 LOAD DATA 无法正确确定字段或行值的结束位置。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据操作语句（9）- LOAD DATA 语句(1)

评论