好的,我确认这个在PHP中可以明确地工作。
$ php --version
PHP 5.6.16 (cli) (built: Dec 30 2015 15:09:50) (DEBUG)
<pdo version>
pdo_pgsql
PDO Driver for PostgreSQL enabled
PostgreSQL(libpq) Version 9.4.0
Module version 1.0.2
Revision $Id: fe003f8ab9041c47e97784d215c2488c4bda724d $
我希望使用PDO在PHP中重新创建以下SQL:
UPDATE relationships SET status = 4 WHERE created > NOW() - interval '2 seconds';
这个脚本正在工作:
<?php
$db = new PDO('pgsql:dbname=db;host=localhost;user=stevetauber');
$stmt = $db->prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval '?'");
$stmt->execute(array("2 seconds"));
这是使用命名占位符的示例:
<?php
$db = new PDO('pgsql:dbname=db;host=localhost;user=stevetauber');
$stmt = $db->prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval ':blah'");
$stmt->execute(array(":blah" => "2 seconds"));
这会导致出现错误:
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: :blah in ... line 5
根据 PHP文档,现在来看,
示例#6 占位符的无效使用:
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name']));
// placeholder must be used in the place of the whole value
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
?>
这是更新后的代码:
<?php
$db = new PDO('pgsql:dbname=db;host=localhost;user=stevetauber');
$stmt = $db->prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - :blah");
$stmt->execute(array(":blah" => "interval '2 seconds'"));
这会产生数据库错误(没有脚本错误):
ERROR: operator does not exist: timestamp with time zone > interval at character 51
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: UPDATE relationships SET status = 4 WHERE created > NOW() - $1
PDO在这里做了一些奇怪的事情,因为:
# select NOW() - interval '2 seconds' as a , pg_typeof(NOW() - interval '2 seconds') as b;
a | b
-------------------------------+--------------------------
2015-12-30 18:02:20.956453+00 | timestamp with time zone
(1 row)
那么我如何在PostgreSQL和interval中使用命名占位符呢?
UPDATE relationships SET status = 4 WHERE created > NOW() - INTERVAL :bla
这个查询语句绑定参数:bla
为2秒钟
,你觉得怎么样? - julpUPDATE relationships SET status = 4 WHERE created > NOW() - :bla::interval
,其中:bla
绑定为2秒
)。 - julp