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

插入 TBL (col1, col2, col3) 值 (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屋！