MySQL培训教程：批量导入数据命令

文章来源：更新时间:2016/7/15 15:05:29

在线老师点击咨询：

最新学讯：近期OCP认证正在报名中，因考试人员较多请尽快报名获取最近考试时间，报名费用请联系在线老师，甲骨文官方认证，报名从速！

我要咨询

MySQL培训教程：批量导入数据命令，通常，创建完新数据库后，我们就可以从旧的MySQL数据库将数据导入到新库中。在图书数据库中，假设一个卖主发给我们一个磁盘，磁盘内的一个纯文本文件中列出了他们所有的图书信息。每本书的记录是单独的一行，竖线把每个记录的字段分开。下面是一个虚构的卖主数据文本文件：

ISBN|TITLE|AUTHOR LAST|AUTHOR FIRST|COPYRIGHT DATE|
067973452X|Notes from Underground|Dostoevsky|Fyodor|August 1994|
...

显然，真实的卖主数据文件中包含的字段要比上面列出的记录中的字段多，但对于展示该功能的示例而言，这已经足够了。第一行是记录中字段的描述。不需要提取第一行，对于我们而言，它只是对字段的说明。因此，输入SQL语句时可让MySQL将其忽略。

就数据而言，我们必须关注以下几个问题。首先，字段并没有按表中的顺序排列。我们必须让MySQL知道将要导入的数据的顺序，以便做调整。另一个问题是，文本文件中既包含books表的数据，也有authors表的数据。这个问题比较麻烦，但还是可以解决的。我们仅用一条SQL语句就可以提取作者的信息，然后再运行一个单独的SQL语句导入图书信息。在开始操作前，我们先将名为books.txt的卖主文件复制到临时目录(例如：/tmp)。现在，可以从MySQL客户机运行LOAD DATA INFILE语句了：

LOAD DATA INFILE '/tmp/books.txt' REPLACE INTO TABLE authors
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
TEXT_FIELDS（col1, col2, col3, col4, col5）
SET author_last = col3, author_?rst = col4
IGNORE col1, col2, col5, 1 LINES;

首先需要指出，尽管本书已多次出现IGNOREnLINES子句，但MySQL4.1及之前的版本并不支持与列相关的TXT_FIELDS和IGNORE子句。使用IGNORE1LINES，文本文件中包含列标题的第一行信息将被忽略。回到SQL语句的第一行，我们已经指定了将要导入的文件名以及将被装载数据的表名。REPLACE标记与前面提到的REPLACE语句的作用相同。当然，姓名字段没有设置成惟一值，就MySQL而言将不会出现任何重复数据的问题。在实际情况下，你还是应该修改数据表以防止作者姓名有重复。

在第二行中，我们指定每个字段以竖线作为结束符，每行以回车(\r)换行(\n)结束。这是对MS-DOS系统下的文本文件而言的。UNIX下仅以换行符作为行的结束。在第三行SQL语句中，我们为每个字段创建一个别名。在第四行语句中，基于前一行语句给出的别名，我们给表中将要接收数据的列设置别名。最后一行语句中，我们通知MySQL忽略不想要的列，以及第一行信息，因为这些被略去的内容不包含数据信息。

如果你使用的旧版本MySQL不支持忽略列的操作，则需执行几个额外的步骤。有几种不同的实现方式。如果将被装载数据的表不是很大，则可以采用一个简单的方法，就是为authors表添加三个额外的临时列，这些列用于接收文本文件中想要略去的字段值以便以后将其删除。实现上述操作的SQL语句如下所示：

ALTER TABLE authors
ADD COLUMN col1 VARCHAR（50）,
ADD COLUMN col2 VARCHAR（50）,
ADD COLUMN col5 VARCHAR（50）;
LOAD DATA INFILE '/tmp/books.txt' REPLACE INTO TABLE authors
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
（col1, col2, author_last, author_?rst, col5）;
ALTER TABLE authors
DROP COLUMN col1,
DROP COLUMN col2,
DROP COLUMN col5;

执行这些语句也可达到预期的目的，但并没有前面的SQL语句那样简单明了。上述第二个SQL语句中IGNORE子句指定了将被忽略的行。该语句的最后一行列出了authors表中的所有列，这些列将接收数据，并且按照数据导入的顺序排列。第三个SQL语句结束从卖主的文本文件中导入数据的操作，现在我们使用DROP语句删除临时列以及列中的数据。DROP语句执行后就不能撤消了。因此要慎用该语句。

如果我们想从文本文件中将作者信息列复制到authors表中，则需要先装载books表中的数据，得到每本书正确的author_id值，通过下列语句可实现：

LOAD DATA INFILE '/tmp/books.txt' IGNORE INTO TABLE books
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'
TEXT_FIELDS（col1, col2, col3, col4, col5）
SET isbn = col1, title = col2,
pub_year = RIGHT（col5, 4）,
author_id =
（SELECT author_id
WHERE author_last = col3
AND author_?rst = col4）
IGNORE col3, col4, 1 LINES;

在这个SQL语句中，我们添加了几个方法来获取所需结果。在第五行中，我们使用字符串函数RIGHT()从copyright字段中提取年份(copyright字段中包含年和月)。RIGHT()函数提取了指定的第二个参数col5中的后四个字符。在第六行中，通过一个子查询获取了基于authors表的author_id值，author表中作者的姓和名字分别与各自的别名相匹配。圆括号中列的查询结果将被赋给author_id列。

最后，我们令MySQL忽略col3、col4以及列的标题行。第一行中的IGNORE标记告知MySQL忽略出错信息，不需要替换重复行，继续执行SQL语句。使用早期的MySQL版本完成这项工作需要像前面示例中提到那样，建立临时列或临时表。实际上，使用临时表分段导入数据是一种谨慎的方法。上述步骤验证完毕后，即可执行INSERT......SELECT语句了。