如何将MySQL风格的问号`?`绑定参数转换为Postgres风格的`$ 1`绑定参数 [英] How to convert MySQL-style question mark `?` bound parameters to Postgres-style `$1` bound parameter

问题描述

我正在将现有项目从MySQL转换为Postgres.代码中有很多原始SQL文字使用?作为占位符，例如

I am converting an existing project from MySQL to Postgres. There are quite a few raw SQL literals in the code that use ? as a placeholder, e.g.

  SELECT
    id
  FROM
    users
  WHERE
    name = ?

但是我得到这个错误:

DB query error: error: operator does not exist: character varying = ?

我不想将所有现有的SQL从?转换为Postgres风格的运算符，例如$1.

I don't want to convert all my existing SQL from ? to postgres-style operators like $1.

是否可以通过某种方式让node-postgres接受问号，或者是否可以将实用程序转换为postgres样式参数?

Is there some way of having node-postgres accept the question marks instead, or an utility that can convert to postgres style params?

请注意，某些基于正则表达式的黑客行为是不可接受的，因为问号可以在引号内，也可以反斜杠转义到任何深度.

Note that some sort of Regex-based hack is not acceptable because question marks can be inside quotes, or backslash escaped to any depth.

推荐答案

是否可以通过某种方式让node-postgres接受问号?

Is there some way of having node-postgres accept the question marks instead?

否.而且?和$1语法之间没有直接对应关系，因为后者暗示了参数的重用，而?不允许使用.例如，使用? ? ?表示您具有3个格式化参数，而$1 $2 $2表示您具有两个格式化参数.

NO. And there is no direct correspondence between ? and $1 syntax, because the latter implies parameter re-use, while ? doesn't allow it. For example, using ? ? ? implies that you have 3 formatting parameters, while $1 $2 $2 implies that you have two formatting parameters.

还是可以转换为postgres样式参数的实用程序?

or an utility that can convert to postgres style params?

不太可能，因为没有直接的对应关系，所以转换只能是单向的，这将使这种实用程序相当无用.您可以用一个正则表达式自己替换所有内容，并用$ + index + 1替换每个?.

Not likely, since there is no direct correspondence, the conversion is possible only one-way, which would make such an utility fairly useless. You can replace everything yourself, with a single regular expression, replacing each ? with $ + index + 1.

我不想将所有现有的SQL从?转换为Postgres风格的运算符，例如$1.

I don't want to convert all my existing SQL from ? to postgres-style operators like $1.

您实际上没有太多选择.必须要做的.此外，由于重用了参数以及可选的扩展名，因此$1比?更具灵活性.例如， pg-promise 很好地扩展了它们，并带有经常需要的各种格式修饰符:^，~，:json，:csv等...

You don't really have much choice in this. It has to be done. Besides, $1 is way more flexible than ?, due to parameter re-use, plus optional extensions. For example, pg-promise extends them very nicely, with various formatting modifiers that are needed frequently: ^, ~, :json, :csv, etc...

请注意，某些基于正则表达式的黑客行为是不可接受的，因为问号可以在引号内，也可以反斜杠转义到任何深度.

Note that some sort of Regex-based hack is not acceptable because question marks can be inside quotes, or backslash escaped to any depth.

与编写用于单向正确转换的实用程序的时间相比，手动转换SQL所花费的时间可能更少.

You will likely spend less time converting your SQL by hand, than the time to write an utility for the one-way proper conversion.

这篇关于如何将MySQL风格的问号`?`绑定参数转换为Postgres风格的`$ 1`绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！