在PHP和PostgreSQL中使用命名占位符与间隔时间失败

Question

在PHP和PostgreSQL中使用命名占位符与间隔时间失败

7

好的，我确认这个在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中使用命名占位符呢？

- Steve Tauber

为什么要使用命名占位符？根据PDO维基的描述，只有在需要复杂查询或者已经有一个键与表字段名称相等的关联数组时才使用命名占位符。否则，普通占位符更简单易用。 - david strachan

UPDATE relationships SET status = 4 WHERE created > NOW() - INTERVAL :bla 这个查询语句绑定参数 :bla 为 2秒钟，你觉得怎么样？ - julp

@davidstrachan 我已经大大简化了查询以简化问题。我的真实查询非常长且复杂。 - Steve Tauber

@julp Postgres需要引号，该格式存在错误。 - Steve Tauber

经过测试，就像Daniel Vérité所说，“至少”需要一个转换(UPDATE relationships SET status = 4 WHERE created > NOW() - :bla::interval，其中:bla绑定为2秒)。 - julp

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vérité · Accepted Answer

占位符是用于纯值的，而不是装饰有单位（或其他任何东西）的值。

要在占位符中表达“interval '2 seconds'”，有两个选项：

在查询中写入“:secs * interval '1 second'”并将“:secs”绑定到php中的数字

或者编写：“cast(:mystring as interval)”，并将“:mystring”绑定到字符串“'2 seconds'”。它将通过显式转换动态解释。

在使用psql命令行客户端进行实验以与PDO驱动程序进行比较时，请使用postgres本机“$N”占位符的“PREPARE”和“EXECUTE”SQL语句，而不是在查询中已经写入参数值。当设置“PDO::ATTR_EMULATE_PREPARES”为false时，这将与PHP驱动程序基本上执行的操作相匹配。

在您的问题的最后部分，在psql中尝试这样做（只需简化即可不需要表）：

select now() > now() - interval '2 seconds';

它可以正常工作并返回“t”（true）。

但是如果您尝试了这个：

prepare p as select now() > now() - $1;

如果出现以下错误：

ERROR: operator does not exist: timestamp with time zone > interval

这与PDO的prepare/execute出现的错误相同。

另一方面，下面的方法可以正常工作：

=> prepare p as select now() > now() - interval '1 second'*$1;
PREPARE

=> execute p(2);
?column? 
----------
t