sql - 如何将数组设置为SQL查询的参数

Question

假设某个前端应用程序将带有此类主体的POST请求发送到后端(Go)应用程序:

{
    "city": "London",
    "gender": ["Female", "Male"],
    "has_family": [0, 1]
}

如您所见，请求主体具有两个数组。我试图将这些数组设置为SQL查询。就我而言，我对 ClickHouse数据库进行SQL查询。我将 go-clickhouse包用于此任务。

我试过这样的代码，但是会引发错误:

sql: converting argument $2 type: unsupported type []string, a slice of string

另外，我尝试将数据类型从 []string更改为 []interface{}。不幸的是，在这种情况下，它会引发如下错误:

sql: converting argument $2 type: unsupported type []interface {}, a slice of interface

代码:

var Controller = func(responseWriter http.ResponseWriter, request *http.Request) {
    type RequestBody struct {
        City string `json:"city"`
        Gender []string `json:"gender"`
        HasFamily []int `json:"has_family"`
    }

    requestBody := RequestBody{}

    decoder := json.NewDecoder(request.Body)

    if err := decoder.Decode(&requestBody); err != nil {
        fmt.Println(err)
        return
    }

    rows, err := database.ClickHouse.Query("SELECT * FROM TABLE_NAME WHERE lower(CITY) = lower($1) AND GENDER IN ($2) AND HAS_FAMILY IN ($3);", requestBody.City, requestBody.Gender, requestBody.HasFamily)
    if err != nil {
        fmt.Println(err)
        return
    }

    // other code
}

Answer 1

尝试用requestBody.Gender包装requestBody.HasFamily和clickhouse.Array()。并尝试将$n更改为?。

代码:

rows, err := database.ClickHouse.Query(
    "SELECT * FROM TABLE_NAME WHERE lower(CITY) = lower(?) AND GENDER IN (?) AND HAS_FAMILY IN (?);",
    requestBody.City,
    clickhouse.Array(requestBody.Gender),
    clickhouse.Array(requestBody.HasFamily),
)

更多信息: https://godoc.org/github.com/mailru/go-clickhouse#Array