Go客户端中的select查询中的SQL变量未更新

Question

I am running the following queries in SQL.

SET @thisid=0;
SET @serial=0;

SELECT @serial := IF((@thisid != `places`.`id`), @serial + 1, @serial) as `serial`, @thisid := `places`.`id`, `places`.`id` FROM `places`;

The variable @serial basically increments only if the new id is not the same as the last one. Upon running these queries in the terminal and printing the values of @serial and @thisid, the value received is @thisid='id6' @serial=6.

I executed this query in my go code:

if _, err = repo.db.ExecContext(ctx, "SET @thisid=0;"); err != nil {
    return
}

if _, err = repo.db.ExecContext(ctx, "SET @serial=0;"); err != nil {
    return
}

rows, err = repo.db.QueryContext(ctx, fmt.Sprintf(
    "SELECT @serial := IF((@thisid != `places`.`id`), @serial + 1, @serial) as `serial`, @thisid := `places`.`id`, `places`.`id` FROM `places`;",
))
if err != nil {
    fmt.Println("error here")
    return
}

if err = repo.db.QueryRow("SELECT @serial").Scan(&that); err != nil {
    return
}

if err = repo.db.QueryRow("SELECT @thisid").Scan(&this); err != nil {
    return
}

Upon printing the values of @thisid and @serial, the value of @thisid is received the same where the value of @serial is received as 0. It doesn't seem to update dynamically.

Answer 1

Go uses a connection pool, which means each query may happen on a different connection. variables like that are scoped to the connection. If you need them to last between queries, you need to use a transaction to ensure you stay within the same connection.