How to Resolve Syntax Errors in Go for MySQL Queries Using SET Variables?

MySQL Queries with SET Variables in Go

In Go, it's possible to use SET statements before running MySQL queries. However, there are certain syntax requirements that must be met. This article explores an issue encountered when attempting to execute such queries and provides a solution.

The original code snippet involves defining a function that selects records based on a user ID and a sorting parameter. It uses SET statements to assign values to variables before executing the query. However, this approach resulted in a syntax error when run through Go.

The error can be attributed to the way the query is constructed. Specifically, the SET statements need to be encapsulated in parentheses and placed before the SELECT statement. Additionally, the MySQL DSN should include the settings "multiStatements=true" and "interpolateParams=true."

The following updated code snippet resolves the issue:

<code class="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 ?,?;
    `

    dsn := d.dsn + "?multiStatements=true&interpolateParams=true"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }
    defer db.Close()

    rows, err := db.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
}</code>

By enclosing the SET statements in parentheses and enabling multi-statements and parameter interpolation in the DSN, the query successfully executes in Go without any syntax errors. Additionally, converting the database and tables to utf8mb4_general_ci collation resolves any potential issues related to character set compatibility.

The above is the detailed content of How to Resolve Syntax Errors in Go for MySQL Queries Using SET Variables?. For more information, please follow other related articles on the PHP Chinese website!