Perl DBI使用mysql本机多重插入功能插入多行 [英] Perl DBI insert multiple rows using mysql native multiple insert ability

问题描述

任何人都可以看到Perl的DBI类型模块，该模块可以轻松利用MySQL的多插入语法

Has anyone seen a DBI-type module for Perl which capitalizes, easily, on MySQL's multi-insert syntax

insert into TBL (col1, col2, col3) values (1,2,3),(4,5,6),...?

我还没有找到允许我执行此操作的界面.我发现的唯一一件事是遍历我的数组.与将所有内容都放入一行并让MySQL处理相比，此方法似乎没有那么理想.我在IE google上找不到任何文档，这说明了我没有编写自己的代码来做到这一点.

I've not yet found an interface which allows me to do that. The only thing I HAVE found is looping through my array. This method seems a lot less optimal vs throwing everything into a single line and letting MySQL handle it. I've not found any documentation out there IE google which sheds light on this short of rolling my own code to do it.

TIA

推荐答案

有两种方法.您可以根据数组的大小多次插入(?, ?, ?).文本操作将类似于:

There are two approaches. You can insert (?, ?, ?) a number of times based on the size of the array. The text manipulation would be something like:

my $sql_values = join( ' ', ('(?, ?, ?)') x scalar(@array) );

然后将数组展平以调用execute().我会避免这种方式，因为需要完成棘手的字符串和数组操作.

Then flatten the array for calling execute(). I would avoid this way because of the thorny string and array manipulation that needs to be done.

另一种方法是开始一个事务，然后多次运行一个插入语句.

The other way is to begin a transaction, then run a single insert statement multiple times.

my $sql = 'INSERT INTO tbl (col1, col2, col3)';
$dbh->{AutoCommit} = 0;
my $sth = $dbh->prepare_cached( $sql );
$sth->execute( @$_ ) for @array;
$sth->finish;
$dbh->{AutoCommit} = 1;

这比第一种方法要慢一些，但仍然避免了重新分析语句.它还避免了第一个解决方案的微妙操作，同时仍然是原子的，并允许优化磁盘I/O.

This is a bit slower than the first method, but it still avoids reparsing the statement. It also avoids the subtle manipulations of the first solution, while still being atomic and allowing disk I/O to be optimized.

这篇关于Perl DBI使用mysql本机多重插入功能插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！