PerlDBI:使用OR语句的绑定变量数量不均(当需要y时使用x绑定变量调用)

Question

任务定义：使用OR从两个不同的列中获取数据。

问题：在使用普通(MySQL(查询时，PerlDBI由于bind变量的数量不均匀而抛出异常。

让我们假设以下DB模式：

customer    vpn_primary_ip   vpn_secondary_ip
1000        1.1.1.1          2.2.2.2
1001        3.3.3.3          NULL
1002        4.4.4.4          5.5.5.5
1003        NULL             6.6.6.6

旁注：由于存储ip地址的列是不可预测的，因此我使用OR运算符组合对vpn_primary_ipANDvpn_secondary_ip列的搜索。普通SQL查询如下：

SELECT
customer,
vpn_primary_ip,
vpn_secondary_ip,
FROM
table
WHERE
vpn_primary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' )
OR  
vpn_secondary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' );

上面的查询给出了以下(适当的(结果：

+----------+-----------------+------------------+
| customer | vpn_primary_ip  | vpn_secondary_ip |
+----------+-----------------+------------------+
|   1000   | 1.1.1.1         | 2.2.2.2          |
|   1002   | 4.4.4.4         | 5.5.5.5          |
|   1003   | NULL            | 6.6.6.6          |
+----------+-----------------+------------------+

与Perl DBI:相同的SQL查询

my @ip_addresses = ('1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6');
my $sth = $dbh->prepare (
"SELECT
customer,
vpn_primary_ip,
vpn_secondary_ip,
FROM
table
WHERE
vpn_primary_ip IN ( @{[join',', ('?') x @ip_addresses]} )
OR  
vpn_secondary_ip IN ( @{[join',', ('?') x @ip_addresses]} )"
);
$sth->execute(@ip_addresses);

抛出以下异常：

DBD::mysql::st execute failed: called with 4 bind variables when 8 are needed at get_vpn_customers line 211, <DATA> line 1.
DBD::mysql::st execute failed: called with 4 bind variables when 8 are needed at get_vpn_customers line 211, <DATA> line 1.

使其工作的唯一想法是将@ip_addresss传递给执行方法两次：

$sth->execute(@ip_addresses, @ip_addresses);

问题：这是正确的方法吗？还是还有其他方法，比如说，最佳或更好的实践？

Answer 1

$sth->execute(@ip_addresses, @ip_addresses);

这是正确的方法。DBI所知道的是，您已经向它传递了一个包含八个绑定点的SQL查询。因此，它需要传递给execute()方法的八个匹配值。

Perl、DBI或MySQL无法知道绑定值是重复的。

Answer 2

其他可能的解决方案是在$sth->execute()之前将SQL查询按摩到可工作状态

use strict;
use warnings;
use feature 'say';
my @ip_addresses = ('1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6');
my $query = "
SELECT
customer,
vpn_primary_ip,
vpn_secondary_ip,
FROM
table
WHERE
vpn_primary_ip IN ( @{[join',', ('?') x @ip_addresses]} )
OR  
vpn_secondary_ip IN ( @{[join',', ('?') x @ip_addresses]} )
";
say $query;
my $ip_addresses;
my $flag = 0;
for (@ip_addresses) {
$ip_addresses .= ', ' if $flag;
$ip_addresses .= "'$_'";
$flag = 1;
}
$query = "
SELECT
customer,
vpn_primary_ip,
vpn_secondary_ip,
FROM
table
WHERE
vpn_primary_ip IN ( $ip_addresses )
OR  
vpn_secondary_ip IN ( $ip_addresses )
";
say $query;

输出

SELECT
customer,
vpn_primary_ip,
vpn_secondary_ip,
FROM
table
WHERE
vpn_primary_ip IN ( ?,?,?,? )
OR
vpn_secondary_ip IN ( ?,?,?,? )

SELECT
customer,
vpn_primary_ip,
vpn_secondary_ip,
FROM
table
WHERE
vpn_primary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' )
OR
vpn_secondary_ip IN ( '1.1.1.1', '4.4.4.4', '5.5.5.5', '6.6.6.6' )