我有一个这样的查询:
SELECT
foo.bar
FROM
foo
WHERE
foo.bang = 0
AND (
CASE
WHEN ? = 2 THEN foo.baz IS NOT NULL
WHEN ? = 1 THEN foo.baz IS NULL
ELSE ? NOT IN (1, 2)
END
)
AND (
(? = 0)
OR
(foo.bang = ?)
)
其中占位符(?
(用作滤波器参数的输入:
my $query = $aboveQuery;
my ( $results ) = $dbh->DBI::db::selectall_arrayref(
$query,
{ Slice => {} },
$bazFilter,
$bazFilter,
$bazFilter,
$bangFilter,
$bangFilter,
);
这是有效的,但对读者不太友好
MySQL支持@
变量,这将提高可读性:
SET @bazFilter = ?; -- passed in via selectall_arrayref or execute;
SET @bangFilter = ?;
SELECT
foo.bar
FROM
foo
WHERE
foo.bang = 0
AND (
CASE
WHEN @bazFilter = 2 THEN foo.baz IS NOT NULL
WHEN @bazFilter = 1 THEN foo.baz IS NULL
ELSE @bazFilter NOT IN (1, 2)
END
)
AND (
(@bangFilter = 0)
OR
(foo.bang = @bangFilter)
);
我想做这样的事情:
my $query = $aboveAtVariablizedQuery;
my ( $results ) = $dbh->DBI::db::selectall_arrayref(
$query,
{ Slice => {} },
$bazFilter,
$bangFilter,
);
但MyISAM显然不会在一个查询中执行多个语句。
我的google fu让我失望了。
有没有一种好的方法可以将@variables与占位符混合匹配?
分别执行每条语句,使用execute
而不是其中一个fetch方法。
@变量是连接的本地变量,因此线程之间不存在污染问题。
如果目标是拥有一个没有重复替换的单一查询,那么考虑:
SELECT foo.bar
FROM ( SELECT baz = ?, bang = ? ) AS init
JOIN foo
WHERE foo.bang = 0
AND (
CASE
WHEN init.baz = 2 THEN foo.baz IS NOT NULL
WHEN init.baz = 1 THEN foo.baz IS NULL
ELSE init.baz NOT IN (1, 2)
END
)
AND (
(init.bang = 0)
OR
(foo.bang = init.bang)
);