在PostgreSQL中使用PDO时如何忽略问号作为占位符 [英] How to ignore question mark as placeholder when using PDO with PostgreSQL

问题描述

此问题可以被视为此

This question can be considered as duplicate of this Question. It does point to the same problem with PDO. But its workaround solution is a bit different as the target differ. I will post there the workaround for JSONB and the link to the PHP ticket.

当我准备以下查询时:

SELECT * FROM post WHERE locations ? :location;

发生以下警告:

警告:PDO :: prepare():SQLSTATE [HY093]:无效的参数编号:xx行上的/path/file.php中的混合命名和位置参数

Warning: PDO::prepare(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in /path/file.php on line xx

问号是有效的 PostgreSQL运算符，但PDO认为它是占位符.

The question mark is an valid PostgreSQL operator but PDO condsider it as a placeholder.

是否有适当的方法将PDO配置为忽略问号作为占位符?

Is there a proper way to configure PDO to ignore question mark as placeholders?

我将在下面发布解决方法.希望有更好的方法

修改 我在PHP错误跟踪系统上添加了门票

Edit I add a ticket at PHP bug tracing system

推荐答案

这是解决我的问题的方法.它通过使用PostgreSQL函数替换?运算符来解决该问题.

This is a workaround to my problem. It solve the problem by using PostgreSQL function to replace the ? operator.

我不太喜欢它，因为它不能使PDO更符合PostgreSQL.但是我没有找到真正的解决方案.

I don't really like it because it does not make PDO more compliance to PostgreSQL. But I found no real solution.

CREATE FUNCTION json_key_exists(JSONB,TEXT) RETURNS BOOLEAN LANGUAGE SQL STABLE AS $f$
    SELECT $1 ? $2
$f$;

现在我可以使用查询了:

And now I can use the query:

SELECT * FROM post WHERE json_key_exists(locations, :location);

该变通办法是来自freenode #postgresql的神话般的RhodiumToad

The workaround was suggested by the fabulous RhodiumToad from freenode #postgresql

如@Abelisto所建议的那样，由于jsonb_exists(jsonb, text)是avialabe

As @Abelisto suggested, there is no need to create the function above as jsonb_exists(jsonb, text) is avialabe

这篇关于在PostgreSQL中使用PDO时如何忽略问号作为占位符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！