Can You Execute MySQL Queries with SET Variables when Using Go\'s Query() Method?

MySQL Queries with SET Variables in Go

Background:

The user is attempting to execute a complex MySQL query using Go that involves setting user variables before running the query. The query successfully executes from the console, but it fails with a syntax error when run through Go. The user is questioning the possibility of using SET variables with Go's Query() method.

Solution:

The user's issue was resolved by making the following modifications:

1. DSN Configuration:

   • Added ?multiStatements=true&interpolateParams=true to the DSN used when connecting to the database. This allows the execution of multiple statements in a single call and enables parameter interpolation.

2. Database and Table Conversion:

   • Converted the database and relevant tables to utf8mb4_general_ci collation. This resolved an error related to collation mismatch.

Code:

With these modifications, the code provided by the user should work as expected:

<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 ?,?;
    `

    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
}</code>

By modifying the DSN configuration and converting the database and tables to the appropriate collation, the user can successfully execute the query in question while avoiding the syntax error.

The above is the detailed content of Can You Execute MySQL Queries with SET Variables when Using Go\'s Query() Method?. For more information, please follow other related articles on the PHP Chinese website!