Go Select In 使用 uuid 字符串列表

Question

我有一个 uuid 字符串列表，我想用它来过滤查询。如果我像这样循环遍历列表中的元素，我可以使查询工作：

for i, fileUID := range fileUIDs {

    db.Exec("DELETE FROM files WHERE uid = $1::uuid", fileUID)

}

但我想使用列表让它工作：

db.Exec("DELETE FROM files WHERE uid IN $1::uuid[]", fileUIDs)

这可能吗？我似乎无法让它工作。

我尝试了如何使用 Golang 在 SQL 中执行 IN 查找中的解决方案？但我pq: syntax error at or near ","在使用普通?或pq: syntax error at or near "::"使用?:uuid. 我使用了以下内容：

fileUIDArgs := make([]interface{}, len(fileUIDs))

for i, fileUID := range fileUIDs {

    fileUIDArgs[i] = interface{}(fileUID)

}

//also tried using "?::uuid"

myPsql := "DELETE FROM files WHERE uid IN (" + "?" + strings.Repeat(",?", len(uidStrings)-1) + ")"

db.Exec(myPsql, fileUIDArgs...)

Answer 1

使用 fmt。确保您的 uuids 不包含任何SQL-injection。ary := []string{&nbsp; &nbsp; "1442edc8-9e1f-4213-8622-5610cdd66790",&nbsp; &nbsp; "0506ca17-d254-40b3-9ef0-bca6d15ad49d",&nbsp; &nbsp; "e46f3708-6da5-4b82-9c92-f89394dffe5d",&nbsp; &nbsp; "fb8bf848-73a2-4253-9fa3-e9d5e16ef94a",&nbsp; &nbsp; "84691fa5-3391-4c02-9b16-82389331b7ac",&nbsp; &nbsp; "adba3c9d-b4ab-4e62-a650-414970645be7",}query := fmt.Sprintf(`DELETE FROM files WHERE uid IN ('%s'::uuid);`,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;strings.Join(ary, "'::uuid,'"))db.Exec(query) // etc摆脱潜在的 SQL 注入：ary := []string{ /* list of uuids */ }query := `DELETE FROM files WHERE uid IN (`aryInterfaces := make([]interface{}, len(ary))for i, v := range ary {&nbsp; &nbsp; query += "$" + strconv.FormatInt(int64(i+1), 10)&nbsp; &nbsp; if i < len(ary)-1 {&nbsp; &nbsp; &nbsp; &nbsp; query += ","&nbsp; &nbsp; }&nbsp; &nbsp; aryInterfaces[i] = v}query += ")"db.Exec(query, aryInterface...)奖励 Postgresql 使用$1, $2, $3etc 而不是?, ?, ?. 这是一个小辅助函数，这是它的概念证明。

Answer 2

这是一个老问题，但为了将被引导到这里的人，如果您使用的是 postgres db，您可以使用这种更简单的方法：DELETE FROM files WHERE uid=ANY($1);$1是一组 uuid。所以你的查询变成：toBeDeleted:= []uuid.UUID{....}_, err = tx.Exec("UDELETE FROM files WHERE uid=ANY($1);",toBeDeleted)//or_, err = tx.Exec("UDELETE FROM files WHERE uid=ANY($1);",pq.Array(toBeDeleted))要么应该为你工作。