英文:
How to use `where in` with slice of ints
问题
从http://jinzhu.me/gorm/advanced.html#sql-builder中，我应该能够使用WHERE IN一次性更新多行，只需使用单个(?)并将切片传递给单个?，而不是使用WHERE IN (?,?,?,?)。
jinzhu.me的示例如下：db.Exec("UPDATE orders SET shipped_at=? WHERE id IN (?)", time.Now, []int64{11,22,33})。这里是gorm测试的一个示例，显示它的工作原理。https://github.com/jinzhu/gorm/blob/021d7b33143de37b743d1cf660974e9c8d3f80ea/main_test.go#L449
然而，这对我来说不起作用：
var availableIds []int
for _, p := range v.Products {
availableIds = append(availableIds, p.Id)
}
log.Println(availableIds)
db.Exec("UPDATE product_supplier SET deleted_at=? WHERE supplier_id = ? AND sku NOT IN (?)", time.Now(), 3, availableIds)
输出：
2016/04/19 07:48:44 [336 338 337 306 329 94 79 43 57 313 108 122 126 127 124 125 123 221 93 330 335 333 312]
(sql: expected 2 arguments, got 25)
当我尝试硬编码时，我遇到了相同的问题：
db.Exec("UPDATE product_supplier SET deleted_at=? WHERE supplier_id = ? AND sku NOT IN (?)", time.Now(), 3, []int{313, 108})
输出：
(sql: expected 2 arguments, got 4)
解决方案：
实际上，代码并没有错误。是我自己弄错了-我的实际代码中有一个多余的参数。我只是没有在stackoverflow上正确翻译它。我的错。
英文:
From http://jinzhu.me/gorm/advanced.html#sql-builder, I should be able to update multiple rows using WHERE IN with a single (?) and passing a slice to the single ? as opposed to WHERE IN (?,?,?,?).
Example from jinzhu.me as follows: db.Exec("UPDATE orders SET shipped_at=? WHERE id IN (?)", time.Now, []int64{11,22,33}). Here is an example of gorm's tests showing it working. https://github.com/jinzhu/gorm/blob/021d7b33143de37b743d1cf660974e9c8d3f80ea/main_test.go#L449
This doesnt work for me however:
var availableIds []int
for _, p := range v.Products {
availableIds = append(availableIds, p.Id)
}
log.Println(availableIds)
db.Exec("UPDATE product_supplier SET deleted_at=? WHERE supplier_id = ? AND sku NOT IN (?)", time.Now(), 3, availableIds)
Output:
2016/04/19 07:48:44 [336 338 337 306 329 94 79 43 57 313 108 122 126 127 124 125 123 221 93 330 335 333 312]
(sql: expected 2 arguments, got 25)
When I try hardcoding, I get the same issue:
db.Exec("UPDATE product_supplier SET deleted_at=? WHERE supplier_id = ? AND sku NOT IN (?)", time.Now(), 3, []int{313, 108})
Output:
(sql: expected 2 arguments, got 4)
Solution:
The code is not actually bugged at all. Was me being silly - I had an extra parameter in my actual code than I should have. I just didn't translate it for stack overflow correctly. My bad.
答案1
得分: 2
prepare功能的特性使得你无法将切片作为参数传递。
在Go语言中，db.Exec的实现是首先prepare查询（包括变量占位符），然后再发送参数。
如果你想知道为什么prepare会阻止你传递切片，请阅读这个答案。
作为解决方法，在你的程序中需要将与切片大小相等的占位符连接起来，生成如下的查询语句：
... WHERE supplier_id = ? AND sku NOT IN (?, ?, ?, ?)
示例代码：
Ids := []int{1, 2, 3}
query := "WHERE supplier_id = ? AND sku NOT IN (" + genVar(len(Ids)) + ")"
db.Exec(query, 3, Ids)
更新：
现在看来，Gorm的db.Exec方法并没有使用DBMS的prepare功能，而是在驱动程序中连接字符串。
我的诊断结果是可能在依赖项中出现了一些问题。
你是否按照http://jinzhu.me/gorm/中的说明导入了Gorm？
英文:
The natural of prepare functionality prevents you from passing a slice as an argument.
The implementation of db.Exec in Go is to first prepare the query (including the variable placeholders) and then send the arguments.
If you wonder why prepare prevents you from passing a slice, read this answer.
As a workaround, the same amount of placeholders as the size of your slice need to be concatenated in your program, which should be generating a query like this:
... WHERE supplier_id = ? AND sku NOT IN (?, ?, ?, ?)
Example code:
Ids := []int{1, 2, 3}
query := "... WHERE supplier_id = ? AND sku NOT IN (" + genVar(len(Ids)) + ")"
db.Exec(query, 3, Ids)
UPDATE:
It turns Gorm's implementation of db.Exec method is not using prepare functionality from DBMS, but it concatenate strings in the driver.
My diagnosis now is that there might be something wrong in the dependencies.
Are you importing Gorm like it shows in http://jinzhu.me/gorm/ ?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论