英文:
Execute sql prepared statement with slice
问题
我写了一个函数(当然是用Go语言),通过这个库将map[string]interface{}
插入到MySQL中。
下面是代码的解释:
- 函数接收名为table的
string
和名为data的map[string]interface{}
。 - 我将data分成键(变量名为columns)和值(变量名为values)。
- 我从columns变量生成了一个名为columns_text的字符串,它看起来像这样:
first_name, last_name, birth_day, date_added
。 - 我从values变量生成了一个名为values_text的字符串,它看起来像这样:
?, ?, ?, ?
。 - 我打开了MySQL连接:
db, err := sql.Open("mysql", "user:pass@/database")
。 - 我创建了一个预处理语句:
stmt, err := db.Prepare("INSERT INTO " + table + " ( " + columns_text + " ) VALUES ( " + values_text + " )")
。 - 我执行了预处理语句,但是我遇到了一个问题。列和值的数量总是在变化,而且
stmt.Exec()
命令不能接收像这样的切片(数组):stmt.Exec(values)
,只能接收单独的值,像这样:stmt.Exec(values[0], values[1], values[2]...)
。
问题:
我之前使用PHP,PDO::Statement可以在执行时接收一个数组。我该如何使用切片(数组)执行这个语句?(如果可以使用不同的库,请写出库的名称和使用方法,谢谢!)
代码:
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
}
}
}
编辑:我已经编辑了上面的函数,现在它完美地工作了。
谢谢!
英文:
I've wrote an function (In Go, of course) that inserting map[string]interface{}
to mysql via this library.
Explanation of the code below:
- The functions receives
string
called table andmap[string]interface{}
called data. - I separate the
data
to keys (variable called columns) and values (variable called values). - I generate from the columns variable called column_text that will look like this:
first_name, last_name, birth_day, date_added
- I generate from the values variable called variable_text that will look like this:
?, ?, ?, ?
- I open mysql connection:
db, err := sql.Open("mysql", "user:pass@/database")
- I create prepared statement:
stmt, err := db.Prepare("INSERT INTO " + table + " ( " + columns_text + " ) VALUES ( " + values_text + " )")
- 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
得分: 7
你走在正确的道路上,然而Stmt.Exec
接受args ...interface{}
作为参数,所以对于你的具体示例,你需要改变两个地方:
......
values := make([]interface{}, 0, len(data))
......
//添加...可以展开values,可以将其类比为在JavaScript中的func.apply(this, array-of-values)。
_, err = stmtIns.Exec(values...)
英文:
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...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论