How to set a custom variable in a query?

Question

MySQL

User variables can be set via 'set', but is it possible to set a variable directly inside, for example select, on the fly, as they say?

I saw the simplest example SELECT @t1:=(@t2:=1)[email protected]:=4,@t1,@t2,@t3, but I don't want to select them, I want to use them in a query, perform operations on them.

For example, I want to take every fifth item from the database, how can I set @a inside this query?

select * from products where (@a:[email protected]+1) % 5 = '0'

I understand that this can be done by product id or in some other way, I'm not interested in solving the query, but in answering my question.

Answer 1

A variable can be declared in a subquery. For example:

SELECT f1, @i := @i + 1
FROM table1
JOIN (SELECT @i := 0) t

To use it applicable to your task, you need to number the rows in the subquery, and then select the necessary ones according to the condition. Something like that:

SELECT f1, f2
FROM
  (
     SELECT f1, f2, @i := @i + 1 AS num
     FROM table1
     JOIN (SELECT @i := 0) t
     ORDER BY f3
  ) sel
WHERE sel.num % 5 = 0

Answer 2

SELECT * FROM table WHERE (@a:=IF(@a,@a,0))>-1 AND (@a:[email protected]+1) % 5 = 0