unable to form bulk upsert query

jmoiron / sqlx

general purpose extensions to golang's database/sql

http://jmoiron.github.io/sqlx/

MIT License

16.03k stars 1.07k forks source link

unable to form bulk upsert query #722

Open lenonqing opened 3 years ago

lenonqing commented 3 years ago

in v 1.3.3

query := "INSERT INTO TABLE_VALUES (`tid`, `uid`, `count`, `deposit`, `ver`) VALUES (:tid, :uid, :count, :deposit, :ver) ON DUPLICATE KEY UPDATE `tid`=VALUES(`tid`), `uid`=VALUES(`uid`), `count`=VALUES(`count`), `deposit`=VALUES(`deposit`), `ver`=VALUES(`ver`)"
args := []map[string]interface{}{
        {"tid": 1, "uid": 1, "count": 1, "deposit": 0, "ver": 0},
        {"tid": 2, "uid": 1, "count": 1, "deposit": 0, "ver": 0},
    }
query, argsOut, err := sqlx.Named(query, args)
_, err = db.ExecContext(ctx, query, argsOut...)

got sql: expected 5 arguments, got 10 error.

gurza commented 3 years ago

@lenonqing you can call bulk upset with []map[string]interface{} directly. It works if you need quick solution.

q := "INSERT INTO role(type,value) VALUES (:type, :value)"

pp := []map[string]interface{}{
    {"type": "type1", "value": "val1"},
    {"type": "type2", "value": "val2"},
    {"type": "type3", "value": "val3"},
}

res, err := db.NamedExec(q, pp)

colinrgodsey commented 3 years ago

it'd be nice to fix this. if you put anything after the VALUES(...) segment, the bulk insert seems to fail. This makes it impossible to use any ON CONFLICT ... forms of the insert.