如何将三个Perl数组中的数据插入到一个MySQL表中?

I have 3 different arrays in Perl (namely A, B and C). Now, I have a table in mysql which also has 3 fields.

我在Perl中有3个不同的数组(即A、B和C)。

What I am trying to do is get all the contents of array A in first field of mysql table, contents of array B into second field and so on. I tried doing this using foreach loops but it works fine for the first array but does not insert anything for the second and third array.

我要做的是把数组A的所有内容放在mysql表的第一个字段中，把数组B的内容放到第二个字段中等等。我尝试使用foreach循环来实现这一点，但是对于第一个数组来说，它很好用，但是对于第二个和第三个数组，它没有插入任何内容。

The code is used is as below:

使用的代码如下:

foreach my $a (@a) {

    my $sql = "insert into es(a) VALUES(\"$a\")";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";
}


foreach my $b (@b) {

    my $sql = "insert into es(b) VALUES(\"$b\")";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";

}

and similarly for third. The column a of the table gets populated correctly but there is no data for column b and c in the table. What am I doing wrong.

同样为第三。表的a列得到正确填充，但是表中没有关于b和c列的数据。我做错了什么?

1 个解决方案

#1

I guess you're not very familiar with relational databases. What you've done is:

我想你对关系数据库不是很熟悉。你所做的是:

iterate over @a
遍历@a
prepare a new statement for each item in @a
为@a中的每个项准备一个新的语句
add one line per insert with only the a value in it; that means:
- you create a dataset for each item of @a
- 为@a的每个项创建一个数据集
- the columns b and c are NULL.
- b和c列为空。
每插入一行，只添加a值;这意味着:为@a的每一项创建一个数据集，b和c列为空。

Now you do that for @b and @c likewise. That is not very efficient.

现在对@b和@c也这样做。这不是很有效。

DBI is designed to help you out here. You should consider the following rules (guidelines):

DBI是用来帮助你的。你应考虑以下规则(指引):

Always use DBI's quote method or better placeholders. That saves you the hassle of addind quotes yourself.
总是使用DBI的引号方法或更好的占位符。这就省去了你自己引用addind引用的麻烦。
If there's a loop and several INSERTs or UPDATEs, always prepare your query outside of the loop and just execute it in the loop.
如果有一个循环和几个插入或更新，请始终在循环之外准备查询，并在循环中执行查询。

Let's take a look at your problem then. I assume @a, @b and @c all have the same number of items, and you want one line per index of @a, @b and @c. So if you have this data:

让我们来看看你的问题。我假设@a、@b和@c都有相同数量的条目，您希望@a、@b和@c的每个索引都有一行。如果你有这样的数据:

my @a = (1, 2, 3);
my @b = qw(foo bar baz)
my @c = (999, 998, 997);

My bet is that you want it to look like this in the db:

我打赌你希望它在db中是这样的:

a b   c
1 foo 999
2 bar 998
3 baz 997

We therefore need to combine your three INSERTs into one statement. That can be done by iterating over all of them at once. We can use the each_array function from List::MoreUtils to handle the iteration for us. We'll also add the guidelines from above to the code.

因此，我们需要将您的三个插入合并到一个语句中。这可以通过一次遍历它们来实现。我们可以从List::MoreUtils中使用each_array函数来处理我们的迭代。我们还将把上面的指导原则添加到代码中。

use List::MoreUtils qw(each_array);
my $dbh = DBI->connect(); # connect to db here

# prepare the INSERT statement once
my $sth_insert = $dbh->prepare('INSERT INTO es SET a=?, b=?, c=?')
  or die $dbh->errstr;

# create the array iterator
my $ea = each_array(@a, @b, @c);
# iterate over all three arrays step by step
while ( my ($val_a, $val_b, $val_c) = $ea->() ) {
  $sth_insert->execute($val_a, $val_b, $val_c) or die $dbh->errstr;
}

#1