Perl:使用一个MySQL调用更新多行 [英] Perl: Update Multiple Rows with one MySQL Call

问题描述

似乎不可能，但是嘿，我也想问一问，我可能是错的.想知道perl是否可以使用一个MySQL调用来更新多行，而我正在使用DBI.

Seems that it may not be possible, but hey I might as well ask, I could be wrong. Was wondering if there's anyway for perl to update multiple rows using one MySQL call, I'm using DBI.

任何帮助或反馈将不胜感激，在MSSQL中可以通过ASP和ASP.net实现此功能，因此想知道是否也可以在MySQL上通过perl实现.

Any help or feedback would be greatly appreciated, this is possible in MSSQL through ASP and ASP.net so was wondering if also possible through perl on MySQL.

感谢您的反馈！

推荐答案

首先也是最重要的一点，绝对不要将变量直接插值到SQL字符串中.这使得SQL注入攻击成为可能.即使这些变量不是来自用户输入的，也仍然存在可能会破坏数据的危险错误的可能性.

First and most important, you absolutely should not interpolate variables directly into your SQL strings. That leaves open the possibility of SQL injection attacks. Even if those variables don't come from user input, it leaves open the possibility of dangerous bugs that can screw up your data.

MySQL DBD驱动程序确实支持多条语句，尽管默认情况下已将其作为安全功能关闭.请参见类方法下的mysql_multi_statements DBD :: mysql文档中的第二部分.

The MySQL DBD driver does support multiple statements, though it's turned off by default as a safety feature. See mysql_multi_statements under the Class Methods section in the DBD::mysql documentation.

但是，更好的解决方案是使用准备好的语句和占位符值，该解决方案可以立即解决这两个问题并且更易于移植.

But a much better solution, which solves both problems at once and is more portable, is to use prepared statements and placeholder values.

my $sth = $dbh->prepare("UPDATE LOW_PRIORITY TableName SET E1=?,F1=? WHERE X=?");

然后，按某种循环获取数据:

Then, get your data in a loop of some sort:

while( $whatever) { 
    my ( $EC, $MR, $EM ) = get_the_data();
    $sth->execute( $EC, $MR, $EM );
}

您只需要准备一次语句，并且占位符值将被DBD驱动程序替换(并保证正确引用).

You only need to prepare the statement once, and the placeholder values are replaced (and guaranteed to be properly quoted) by the DBD driver.

在 DBI文档中了解有关占位符的更多信息..

Read more about placeholders in the DBI docs.

这篇关于Perl:使用一个MySQL调用更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！