go-sql does not replace placeholders in my SQL statements

Hello,

I am using go-sql with the go-sql-driver/mysql driver following go-database-sql's tutorial to update a MariaDB database. From the mysql driver README and this blog I assume that this should work.

However, my ? placeholders aren't being replaced for some reason and I cannot figure out why. Here's a piece of code where it occurs:

func updateDatabase(user *User, tx *sql.Tx) {
    update, err := tx.Prepare("UPDATE locations SET ?=?+? WHERE city=? AND state=? AND country=?")
    if err != nil {
        log.Println(err)
        tx.Rollback()
        return
    }
    defer update.Close()

    for k, v := range user.licenses {
        if v > 0 {
            _, err := update.Exec(k, k, v, user.city, user.state, user.country)
            if err != nil {
                log.Println(err)
                tx.Rollback()
                return
            }
        }
    }

    tx.Commit()
}

Can someone help me?

---

评论：

YEPHENAS:

You can't use placeholders as column names in prepared statements. Placeholders are for values.

fallenunia:

That explains a lot. What is the best way to have variable columns? fmt.Sprintf? There is no user generated input since it is a quick script of sorts.

yRZ6UqzkuYJhKrf4HY3K:

Build the query string dynamically which is usually not recommended for security reasons but if there is no user input, it is probably okay.  Also, you could just SET all the possible columns even though only some will have changed.

tmornini:

Which is a polite way of saying "don't do that!"

riking27:

Yeah you basically need to go through a hardcoded list, check if any of them changed, and append to multiple slices.

for .... {
    ....
    columns = append(columns, colName) // colName MUST come from a string constant
    values = append(columns, newValue)
}
if len(columns) == 0 {
    return
}
query = sqlPartialUpdatePart1 + strings.Join(columns, "=?,") + "=?" + sqlPartialUpdatePart2
....
values = append(values, city, state, country)
stmt.Exec(values...)

jtsylve:

It's sort of annoying, but some drivers use different placeholders than ?.  I know at least one of the postgres drivers uses $1, $2, $3, etc.  Perhaps this is the case here?