使用 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

对于那些感兴趣的人，我已经通过一些更新解决了我的问题。

1. 连接时在DSN上有设置?...&multiStatements=true&interpolateParams=true

2. 添加以上内容后，我开始收到有关排序规则的新错误 ( 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() 做你想做的事。