Golang为“ CREATE TABLE”准备了语句

问题描述：

I'm finding that create table statements are not recognizing wildcard characters I want to insert. How would I do something like with a prepared statement in golang?

                    stmt, err := tx.Prepare(`
                    CREATE TABLE table_number_$1 (
                            guid character varying(64) NOT NULL,
                            number integer,
                            name character varying(64),
                            PRIMARY KEY (guid),
                            CONSTRAINT some_onstraint
                            CHECK ((number = $2))
                    )`)
                    if err != nil {
                            return err 
                    }   
                    defer stmt.Close()
                    if _, err := stmt.Exec(
                            string(table_number),
                            table_number; err != nil {
                            tx.Rollback()
                            return err 
                    }

The error I get when I print it out is: sql: expected 0 arguments, got 2

EDIT: obviously I know I could build the string like normal, but was wondering if there's a built in way.

我发现create table code>语句无法识别我要插入的通配符。 p>

  stmt，err：= tx.Prepare（`
 CREATE TABLE table_number_ $ 1（
引导字符变化（  64）NOT NULL，
数字整数，
名称字符变化（64），
主键（guid），
约束some_onstraint 
 CHECK（（number = $ 2））
）`）
 if err  ！= nil {
 return err 
} 
延迟stmt.Close（）
如果_，err：= stmt.Exec（
 string（table_number），
 table_number; err！= nil {
 tx  .Rollback（）
返回错误
} 
  code>  pre> 
 
 打印出来的错误是：
  sql：预期为0个参数，得到2个 code>  p> 
 
 
编辑：\显然，我知道我可以像正常情况那样构建字符串，但是想知道是否存在内置方式。  / p> 
  div>

答

It's not possible in PostgreSQL because:

PostgreSQL's bind parameters may only be used for literals, not identifiers. That's because the parser has to know what the identifiers are in order to parse the query correctly, but bind parameters are only sent after parsing. So you can't use parameters for table names etc.
PostgreSQL doesn't support bind parameters in utility statements (anything other than insert/update/delete/select) anyway.

Some drivers support client-side parameter substitution and escaping for identifiers via a different placement parameter syntax, but as far as I can tell Go's does not. So you'll have to use string interpolation, carefully. Remember to always enclose the identifier in "double quotes" and double any embedded quotes, so a table name my "table"! becomes "my ""table!" in SQL.

答

You can't use parameters for table or column names in SQL. You will have to escape the variable part manually and build the table name in code and inject it in the SQL string before sending it to the database.

As well, the PostgreSQL engine is built in such a way that it does not allow variables at any place in a CREATE statement. This means that the engine does will not accept any parameters when running such a query.

Golang为“ CREATE TABLE”准备了语句

相关推荐