Go中的错误MySQL

Question

This code is working:

stmt, err := db.Prepare("UPDATE `test` SET `score` = ? WHERE id = ?")
CheckErr(err)
_, err = stmt.Exec(value, id)

But when I change my code, it doesn't work:

stmt, err := db.Prepare("UPDATE `test` SET ? = ? WHERE id = ?")
CheckErr(err)
_, err = stmt.Exec("score", value, id)

What's the problem?

Answer 1

For better or worse, parameters can only be used for literal constants inside a query. These are generally comparison values in the where clause, sometimes constants in the select or set clauses -- and less often in other parts of the query.

Identifiers are not literal constants. In fact, none of the following are:

• database, table, and column names
• function names
• operators (such as +)
• keywords (such as asc/desc in order by)

Unfortunately, to implement these "dynamically", you need to munge the query string, by directly modifying the string. That is rather yucky, but there no alternatively.

One of the benefits of this approach is that it allows the database to store and then re-use the query plan. Eliminating the compilation phase can be an important performance gain for very fast queries.

EDIT:

I do not really know go, but the idea is:

sql := "UPDATE `test` SET [col] = ? WHERE id = ?"

sql = strings.replace(sql, "[col]", "score")

stmt, err := db.Prepare(sql)
CheckErr(err)
_, err = stmt.Exec(value, id)

In other words, directly change the query string for identifiers. Continue to use parameters for values.