MySQL 快速地从文件中插入大量数据记录的方法

最新推荐文章于 2021-05-28 14:11:42 发布

VIP_Neo

最新推荐文章于 2021-05-28 14:11:42 发布

阅读量685

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/fireblue1990/article/details/48251833

MySQL 专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了 MySQL 中 LOAD DATA INFILE 命令的使用方法，包括如何从文本文件中高速读取数据到数据库表，以及 LOCAL、REPLACE 和 IGNORE 等关键词的作用。此外还讨论了安全性和权限问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

LOAD DATA INFILE 命令语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [FIELDS

        [TERMINATED BY '\t']

        [OPTIONALLY] ENCLOSED BY '']

        [ESCAPED BY '\\' ]

    [LINES TERMINATED BY '\n']

    [IGNORE number LINES]

    [(col_name,...)]

为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用LOAD DATA INFILE，在服务器主机上你必须有file的权限。

如果你指定关键词LOW_PRIORITY，LOAD DATA语句的执行被推迟到没有其他客户读取表后。

使用LOCAL将比让服务器直接存取文件慢些，因为文件的内容必须从客户主机传送到服务器主机。在另一方面，你不需要file权限装载本地文件。

REPLACE和IGNORE关键词控制对现有的唯一键记录的重复的处理。如果你指定REPLACE，新行将代替有相同的唯一键值的现有行。如果你指定IGNORE，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键键时，出现一个错误，并且文本文件的余下部分被忽略时。

如果你使用LOCAL关键词从一个本地文件装载数据，服务器没有办法在操作的当中停止文件的传输，因此缺省的行为好像IGNORE被指定一样。

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'

换句话说，缺省值导致读取输入时，LOAD DATA INFILE表现如下：

在换行符处寻找行边界
在定位符处将行分进字段
不要期望字段由任何引号字符封装
将由“\”开头的定位符、换行符或“\”解释是字段值的部分字面字符

相反，缺省值导致在写入输出时，SELECT ... INTO OUTFILE表现如下：

在字段之间写定位符
不用任何引号字符封装字段
使用“\”转义出现在字段中的定位符、换行符或“\”字符
在行尾处写换行符

注意，为了写入FIELDS ESCAPED BY '\\'，对作为一条单个的反斜线被读取的值，你必须指定2条反斜线值。

IGNORE number LINES选项可被用来忽略在文件开始的一个列名字的头：

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;

当你与LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE将一个数据库的数据写进一个文件并且随后马上将文件读回数据库时，两个命令的字段和处理选项必须匹配，否则，LOAD DATA INFILE将不能正确解释文件的内容。假定你使用SELECT ... INTO OUTFILE将由逗号分隔的字段写入一个文件：

mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt'
           FIELDS TERMINATED BY ','
           FROM ...

为了将由逗号分隔的文件读回来，正确的语句将是：

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能被用来读取从外部来源获得的文件。例如，以dBASE格式的文件将有由逗号分隔并用双引号包围的字段。如果文件中的行由换行符终止，下面显示的命令说明你将用来装载文件的字段和行处理选项：

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

任何字段或行处理选项可以指定一个空字符串('')。如果不是空，FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须是一个单个字符。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超过一个字符。例如，写入由回车换行符对（CR+LF）终止的行，或读取包含这样行的一个文件，指定一个LINES TERMINATED BY '\r\n'子句。

示例：

mysql> load data local infile 'data.txt' into table test fields terminated by ' ' (timestamp, clientID, objectID, size, method, http, status, type, region, server);