duanhuan5409
duanhuan5409
2014-06-22 17:10

用切片执行SQL准备的语句

已采纳

I've wrote an function (In Go, of course) that inserting map[string]interface{} to mysql via this library.

Explanation of the code below:

  1. The functions receives string called table and map[string]interface{} called data.
  2. I separate the data to keys (variable called columns) and values (variable called values).
  3. I generate from the columns variable called column_text that will look like this: first_name, last_name, birth_day, date_added
  4. I generate from the values variable called variable_text that will look like this: ?, ?, ?, ?
  5. I open mysql connection: db, err := sql.Open("mysql", "user:pass@/database")
  6. I create prepared statement: stmt, err := db.Prepare("INSERT INTO " + table + " ( " + columns_text + " ) VALUES ( " + values_text + " )")
  7. I execute the prepare statement. but I have a problem. the number of the columns and values changes all the time, and the stmt.Exec() command can't receive an slice (Array) like this: stmt.Exec(values), only the values alone like this: stmt.Exec(values[0], values[1], values[2]...)

The question:

I'm coming from PHP, where PDO::Statement could receive an array when executing. How can I execute the statement with the slice (Array)? (If I can do it with different library, please write the name of the library and how to use it, thank you!)

The code:

func insertToDB(table string, data map[string]interface{}) {
columns := make([]interface{}, 0, len(data))
values := make([]interface{}, 0, len(data))

for  key, _ := range data {
   columns = append(columns, key)
   values = append(values, data[key])
}

columns_text := ""
i := 0
of := len(data)

for i < of {
    column := columns[i].(string)

    if i == 0 {
        columns_text = column
    } else {
        columns_text = columns_text + ", " + column
    }

    i++
}

fmt.Println(columns_text + " = " + table)

values_text := ""

i = 0

for i < of {

    if i == 0 {
        values_text = "?"
    } else {
        values_text = values_text + ", ?"
    }

    i++
}

fmt.Println(values_text)
fmt.Println(values)
fmt.Println(data)

db, err := sql.Open("mysql", "root:root@/bacafe")
if err != nil {
    return -1, err
}
defer db.Close()

stmtIns, err := db.Prepare("INSERT INTO " + table + " ( " + columns_text + " ) VALUES ( " +  values_text + " )")
if err != nil {
    return -1, err
}
defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

result, err := stmtIns.Exec(values...)
if err != nil {
    return -1, err
} else {
    insertedID, err := result.LastInsertId()
    if err != nil {
        return -1, err
    } else {
        return int(insertedID), nil
    }
}
}

EDIT: I've edited the function above and it works perfectly now.

Thank you!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douxie3625 douxie3625 7年前

    You are on the right track however Stmt.Exec takes args ...interface{}, so for your specific example you need to change 2 things:

    ......
    values := make([]interface{}, 0, len(data))
    ......
    //adding ... expands the values, think of it like func.apply(this, array-of-values) in 
    // javascript, in a way.
    _, err = stmtIns.Exec(values...) 
    
    点赞 评论 复制链接分享