好的,我已经确认可以在PHP上正常使用。
$ php --version PHP 5.6.16 (cli) (built: Dec 30 2015 15:09:50) (DEBUG)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';
该脚本正在工作:
prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval '?'"); $stmt->execute(array("2 seconds"));
这里是命名占位符:
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文档,
Example#6无效使用占位符:
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]%")); ?>
这是更新的代码:
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中使用命名占位符?