www.baike369.com
百科369 > MySQL教程 > MySQL使用LOAD DATA INFILE方式导入文本文件

MySQL使用LOAD DATA INFILE方式导入文本文件


MySQL使用LOAD DATA INFILE方式导入文本文件

MySQL中,可以使用LOAD DATA INFILE命令将文本文件导入到MySQL数据库中。基本的语法格式如下:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'filename.txt'
[REPLACE | IGNORE] INTO TABLE tablename [OPTION] [IGNORE number LINES] [(col_name,...)]

LOAD DATA INFILE语句以非常高的速度从一个文本文件中读取记录行并插入到一个表中。

如果LOCAL关键词被指定,文件将从客户端主机读取;如果LOCAL没有被指定,文件则必须位于服务器上。由于安全性的原因,当读取位于服务器端的文本文件时,文件必须处于数据库目录或可被所有人读取的地方。同时,为了对服务器端的文件使用LOAD DATA INFILE语句,你必须在服务器主机上有FILE权限。只有当你没有以--local-infile=0选项启动mysqld,或你没有禁止你的客户端程序支持LOCAL的情况下,LOCAL才会工作。

如果你指定关键词LOW_PRIORITY,LOAD DATA语句的执行将会被延迟,直到没有其它的客户端正在读取表。

如果你对一个MyISAM表指定关键词CONCURRENT,那么当LOAD DATA正在执行时,其它的线程仍可以从表中检索数据。使用这个选项时,如果同时也有其它的线程正在使用表,这当然会有一点影响LOAD DATA的执行性能。

使用LOCAL将比让服务器直接访问文件要慢一些,因为文件的内容必须从客户端主机传送到服务器主机。而在另一方面,你不再需要有FILE权限用于装载本地文件。

你也可以使用mysqlimport实用程序装载数据文件;它通过发送一个LOAD DATA INFILE命令到服务器来动作。--local选项使得mysqlimport从客户端主机读取数据文件。如果客户端与服务器支持压缩协议,你可以指定--compress选项,以在较慢的网络中获得更好的性能。

当从服务器主机定位文件时,服务器使用下列规则:

  • 如果给定一个完整的路径,服务器使用该路径名。
  • 如果给定一个有一个或多个前置构件的相对路径,服务器以相对服务器的数据目录搜索文件。
  • 如果给定一个没有前置构件的文件名,服务器从当前数据库的数据库目录搜寻文件。

filename.txt参数指定了要导入数据的文本文件的路径和名称;

REPLACE和IGNORE关键词控制对与现有的记录在唯一键值上重复的记录的处理。如果你指定REPLACE,新的记录行将替换有相同唯一键值的现有记录行;如果你指定IGNORE,将跳过与现有的记录行在唯一键值上重复的输入记录行;如果你没有指定任何一个选项,当重复键值出现时,将会发生一个错误,文本文件的剩余部分也将被忽略。

如果你使用LOCAL关键词从一个本地文件中读取数据,在此操作过程中,服务器没有办法停止文件的传送,因此缺省的处理方式就好像是IGNORE被指定一样。

tablename参数表示即将导入的数据表的名称;OPTION为可选参数选项,OPTION部分的语法包括FIELDS和LINES子句,其可能的取值有:

  • FIELDS TERMINATED BY '字符串':设置字符串为字段之间的分隔符,可以为单个或多个字符,默认值是制表符“\t”。
  • FIELDS ENCLOSED BY '字符':设置字符来括住字段的值,只能为单个字符。默认情况下不使用任何符号。
  • FIELDS OPTIONALLY ENCLOSED BY '字符':设置字符来括住CHAR、VARCHAR和TEXT等字符型字段,只能为单个字符。默认情况下不使用任何符号。
  • FIELDS ESCAPED BY '字符':设置转义字符,只能为单个字符。默认值为“\”。
  • LINES STARTING BY '字符串':设置每行数据开头的字符,可以为单个或多个字符。默认情况下不使用任何字符。
  • LINES TERMINATED BY '字符串':设置每行数据结尾的字符,可以为单个或多个字符。默认值是“\n”。在Windows操作系统中,则为“\r\n”。
  • IGNORE number LINES:表示忽略文件的前number行记录。
  • (col_name):字段列表。根据字段列表中的字段和顺序来加载记录。
  • SET column=expr:将指定的列column进行相应地转换后再加载,使用expr表达式来进行转换。

实例1

使用LOAD DATA命令将F:\backup\employee.txt文件中的数据导入到example数据库中的employee表。操作步骤如下:

首先要确定已经将employee表中的数据导出到了employee.txt文件中。如下图所示:

employee.txt文件中的内容

然后可以执行下面的步骤。

1. 将employee表中的数据全部删除。执行结果如下:

将employee表中的数据全部删除的操作效果

通过查询可以看出,employee表已经没有了数据记录。就是数据表employee存在,但是表中没有任何记录。

2. 使用LOAD DATA命令从employee.txt文件中还原数据。SQL代码如下:

LOAD DATA INFILE 'F:/backup/employee.txt' INTO TABLE example.employee
      FIELDS
          TERMINATED BY '\、'
          OPTIONALLY ENCLOSED BY '\"'
      LINES
          STARTING BY '\>'
          TERMINATED BY '\r\n';

首先查看employee.txt文件中的内容,然后使用OPTION选项中的FIELDS和LINES子句过滤掉记录中多余的字符,即可将数据记录准确还原到employee表中。

如果在employee.txt文件中包含了中文字符,使用上面的语句则会输出乱码。此时,加入CHARACTER SET gbk语句即可解决这一个问题。修改SQL代码如下:

LOAD DATA INFILE 'F:/backup/employee.txt' INTO TABLE example.employee
      CHARACTER SET gbk
      FIELDS
          TERMINATED BY '\、'
          OPTIONALLY ENCLOSED BY '\"'
      LINES
          STARTING BY '\>'
          TERMINATED BY '\r\n';

执行结果如下:

使用LOAD DATA命令从employee.txt文件中还原数据的操作效果

3. 查询employee表中的数据记录。如下图所示:

查询employee表中的数据记录的操作效果

可以看到,语句执行成功以后,原来的数据重新恢复到了employee表中。

如果第一次还原的记录不准确,则需要清除记录后,修改LOAD DATA命令中的FIELDS或LINES子句,然后再次进行还原,直到准确为止。


实例2

如果employee表中已经存在记录时,应该怎么做呢?下面在实例1的基础上,第二次使用LOAD DATA命令将F:\backup\employee.txt文件中的数据导入到example数据库中的employee表。操作步骤如下:

1. 执行LOAD DATA命令的SQL代码如下:

LOAD DATA INFILE 'F:/backup/employee.txt'
      REPLACE
      INTO TABLE example.employee
      CHARACTER SET gbk
      FIELDS
          TERMINATED BY '\、'
          OPTIONALLY ENCLOSED BY '\"'
      LINES
          STARTING BY '\>'
          TERMINATED BY '\r\n';

执行结果如下:

第二次使用LOAD DATA INFILE方式导入文本文件的操作效果

2. 第二次查询employee表中的数据记录。如下图所示:

第二次查询employee表中的数据记录的操作效果

我们可以看出,REPLACE关键词可以控制对与现有的记录在唯一键值上重复的记录的处理。如果指定了REPLACE,新的记录行将替换有相同唯一键值的现有记录行。


提示

使用MySQL的LOAD DATA INFILE命令,可以实现不同字符集之间的转换。

Copyright© 2011-2016 www.baike369.com All Rights Reserved