使用 SET 变量进行 MySQL 查询

Question

我试图通过设置一些变量来清理 Go 调用 MySQL 查询的方式，然后再使用围绕单个值的大量 case 语句运行查询。我尝试运行的查询在控制台上运行良好，但在SELECT通过 Go 运行它时因语法问题而失败。这样的事情可能吗？

func (d *DB) SelectByUserId(uid string, srt string, pg, lim int) ([]Inventory, error) {

    query := `

    SET @user_id := ?,

        @orderBy := ?;

    SELECT

        *

    FROM

        inventory

    WHERE

        user_id = @user_id

    ORDER BY

        (CASE WHEN @orderBy = 'type,asc' THEN type END),

        (CASE WHEN @orderBy = 'type,desc' THEN type END) DESC,

        (CASE WHEN @orderBy = 'visible,asc' THEN visible END),

        (CASE WHEN @orderBy = 'visible,desc' THEN visible END) DESC,

        (CASE WHEN @orderBy = 'create_date,asc' THEN create_date END),

        (CASE WHEN @orderBy = 'create_date,desc' THEN create_date END) DESC,

        (CASE WHEN @orderBy = 'update_date,asc' THEN update_date END),

        (CASE WHEN @orderBy = 'update_date,desc' THEN update_date END) DESC

    LIMIT ?,?;

    `

    

    rows, err := d.Query(

        query,

        uid,

        srt,

        pg*lim,

        lim,

    )

    if err != nil {

        return nil, err

    }

    defer rows.Close()

    result := make([]Inventory, 0)

    for rows.Next() {

        var inv Inventory

        if err := rows.Scan(

            &inv.Id,

            &inv.UserId,

            &inv.Type,

            &inv.Name,

            &inv.Description,

            &inv.Visible,

            &inv.CreateDate,

            &inv.UpdateDate); err != nil {

            return result, err

        }

        result = append(result, inv)

    }

    if err = rows.Err(); err != nil {

        return result, err

    }

    return result, nil

}

现在，如果我取出 SET 部分并将所有@变量替换为?，然后srt像下面这样多次传递变量，这一切都有效。但真的不想有查询调用，例如：

rows, err := d.Query(

        query,

        uid,

        srt,

        srt,

        srt,

        srt,

        srt,

        srt,

        srt,

        srt,

        pg*lim,

        lim)

Answer 1

对于那些感兴趣的人，我已经通过一些更新解决了我的问题。连接时在DSN上有设置?...&multiStatements=true&interpolateParams=true添加以上内容后，我开始收到有关排序规则的新错误 ( Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='。我检查并转换了数据库和表utf8mb4_general_ci，一切都按预期工作。感谢那些提供解决方案的人，但这是我们最终选择的路线。

Answer 2

与大多数查询接口一样，该Query()函数一次只能执行一个 SQL 语句。MySQL 的准备语句不适用于多查询。SET您可以通过在一次调用中执行语句，然后在第二次调用中执行语句来解决此问题SELECT。但是您必须注意确保它们在同一个数据库连接上执行，否则连接池很可能在不同的连接上运行它们。所以你需要做类似的事情：conn, err := d.Conn(context.TODO())conn.QueryContext(context.TODO(), "SET ...")conn.QueryContext(context.TODO(), "SELECT ...")或者，更改准备 ORDER BY 的方式，这样就不需要用户定义的变量。我这样做的方法是在 Go 代码中而不是在 SQL 中构建 ORDER BY 语句，使用字符串映射来确保使用有效的列和方向。如果输入不在地图中，则将默认顺序设置为主键。validOrders := map[string]string{    "type,asc":         "type ASC",    "type,desc":        "type DESC",    "visible,asc":      "visible ASC",    "visible,desc":     "visible DESC",    "create_date,asc":  "create_date ASC",    "create_date,desc": "create_date DESC",    "update_date,asc":  "update_date ASC",    "update_date,desc": "update_date DESC",}orderBy, ok := validOrders[srt]if !ok {    orderBy = "id ASC"}query := fmt.Sprintf(`    SELECT ...    WHERE user_id = ?    ORDER BY %s    LIMIT ?, ?`, orderBy)这对于 SQL 注入是安全的，因为函数输入不会插入到查询中。它是从我的地图中插入到查询中的值，并且该值在我的控制之下。如果有人试图输入一些恶意值，它不会匹配我地图中的任何键，所以它只会使用默认排序顺序。

Answer 3

除非驱动程序实现特殊接口，否则查询在执行前首先在服务器上准备好。因此 Bindvars 是特定于数据库的：MySQL：使用？上面显示的变体PostgreSQL：使用枚举的 $1、$2 等 bindvar 语法SQLite：接受两者？和 $1 语法Oracle：使用 :name 语法MsSQL：@（随你用）我想这就是为什么你不能用 query() 做你想做的事。