如何将数据写入文件以加载到MySQL数据库？

Question

我遇到了一些I/O瓶颈，将解析的数据插入到MySQL数据库中。因此，我不需要解析文件并将每个记录分别插入数据库，而是将记录写入文件(如CSV)，然后手动将文件加载到数据库中。

我如何使用Perl来处理这个问题？下面是当前将数据插入数据库的代码，但我希望以一种格式将数据写入文件，以便以后尽可能地将数据插入到MySQL中。

#INSERT INTO ANNOUNCE TABLE
foreach my $au (@ANNOUNCED) {

    my $val=$au->[0];
    my $IP = $prefix->ip();
    my $subnetmask = $prefix->mask();


    $Announce_update->execute($IP,$subnetmask,$UpdateKey);

}

Answer 1

要从外部文件加载数据，MySQL提供了LOAD DATA INFILE命令。LOAD DATA INFILE在输入文件格式时相当灵活，允许您指定分隔符、EOL字符、字段是否被引用等。它与案文:CSV协同工作，可以用来输出包含数据的分隔文件。

首先，将数据写入文件：

use Text::CSV;

my $csv = Text::CSV->new({ eol => "\n" }) or die Text::CSV->error_diag();

my $infile = "/path/to/file";
open my $fh, ">", $infile or die $!;

for my $i (0..$#ANNOUNCED)
{
   # Don't end last line with '\n' or we'll get a garbage row when we load
   # to the database
   $csv->eol(undef) if $i == $#ANNOUNCED;

   # Generate the data to insert for this row

   # Write to file
   $csv->print($fh, [ $IP, $subnetmask, $UpdateKey ]);
}

# Close file handle to flush the buffer
close $fh;

请注意，您必须在写入文件句柄后关闭该文件句柄，否则MySQL可能无法获取所有数据。

接下来，加载文件：

my $query = "LOAD DATA LOCAL INFILE '$infile' INTO TABLE table
             FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
             LINES TERMINATED BY '\\n'";

eval {
   # $dbh is an already open database handle
   my $rows_inserted = $dbh->do($query);
};
die $@ if $@;

LOCAL关键字影响文件是否位于MySQL服务器上。如果要加载到本地主机上的数据库，则可以关闭LOCAL关键字，这听起来可能有违直觉(使用LOCAL，客户端将文件的副本发送到服务器的临时目录，服务器从那里读取文件，因此它是服务器本地的)。如果没有LOCAL关键字，该文件必须对所有人都具有可读性(即至少0644对*nix的权限，因为您还必须对它进行写入)。MySQL还将根据是否使用LOCAL在不同的位置查找相对路径；有关详细信息，请参阅文档。

上面的查询假设您的表只有三列。如果有更多内容，则需要指定要为哪些列插入数据，按照CSV中出现的顺序排列，例如：

my $query = "LOAD DATA LOCAL INFILE '$infile' INTO TABLE table
             FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
             LINES TERMINATED BY '\\n'
             (ip, subnetmask, updatekey)";

复合刀片

如果您一次插入大量行，LOAD DATA INFILE是目前最快的方法。然而，对于较小的插入，写入和读取临时文件的额外I/O开销(特别是如果使用LOCAL选项)比使用这种复合插入要慢：

# Inserts 3 rows at once
INSERT INTO table VALUES (foo, foo, foo), (bar, bar, bar), (baz, baz, baz)

对于许多行，LOAD DATA INFILE比复合插入的速度更快。对于我的数据库和应用程序，我做了一些分析，发现这个数字大约是100，但对您来说几乎肯定是不同的。我编写了一个函数，根据要插入的数据行数选择最有效的方法：

sub insert_rows {
    my $data = shift; # Reference to an AoA

    my $num_rows = $#{ $data };

    if ($num_rows < 100) {
        # Generate compound insert statement
    }
    else {
        # LOAD DATA INFILE
    }
}

请注意，max_allowed_packet的默认值在MySQL 5.7中仅为1MB。如果复合insert语句超过此值，您将得到一个Packet too large错误，插入将失败。您可以将其调整到最大1GB，但到那时，您可能已经达到了LOAD DATA INFILE效率更高的临界值。

Answer 2

在这一点上，使用案文:CSV可能会使您的生活更轻松。如果您的系统尚未安装该模块，请安装该模块，然后将这一行添加到您的脚本中：

use Text::CSV ;

注释掉代码中的数据库插入位：

 # $dbh->prepare("SET FOREIGN_KEY_CHECKS = 0;")->execute();
 # $Announce_update->execute($IP,$subnetmask,$UpdateKey);

然后尝试获取您想要的数据：

 my @csvdata = ($IP,$subnetmask,$UpdateKey); # assuming data is in these vars 
                                              # and they aren't references used 
                                              # by $Announce_update somehow

然后将其写入某个地方的CSV文件(从文档中摘录)，您将“手动”加载这些文件(我假设您是指使用MySQL控制台或CLI工具)。

  my $csv = Text::CSV->new ( { binary => 1 } )  
  open $fh, ">:encoding(utf8)", "csvdata.csv" or die ;
  $csv->print ($fh, $_) for @csvdata;

希望这能帮上忙--尽管手头还有点短。即使成功了，我也不确定你这样做是否会让你的生活更轻松;-)