Reputation: 53
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.
Upvotes: 0
Views: 372
Reputation: 79794
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.
Upvotes: 1
Reputation: 1271241
Your query is really arbitrary. MySQL does not guarantee the order of evaluation of expressions in a select
. Nor does it guarantee the ordering of the result set.
So, I think you want:
select p.*,
(@rn := if(@id = id, @rn + 1,
if(@id := id, 1, 1)
)
) as serial
from (select p.*
from places p
order by p.id
) p cross join
(select @id := 0, @rn := 0) params;
Upvotes: 1