Q: Can MySQL temporary variables be used in the WHERE clause?

Yes. MySQL user-defined variables (such as@var) can be referenced in the WHERE clause.

A user-defined variable is a reference to whatever value is currently assigned to it when the expression is evaluated.

In a query, predicates in the WHERE clause are evaluated before expressions in the SELECT list.

That is, when accessing rows,@varwill be evaluated as a Boolean expression; for each candidate row, the expression will be evaluated and only if This row is returned only if the result is TRUE.

If you are to provide this variable with a value greater than or equal to 10, all rows will be returned before the statement is executed.

Q: How to filter the results based on the value of@var?

You really can't. (In fact, this is what your original query is doing.)

You can include predicates inotherexpressions outside of@var; these expressions can be derived from the value assigned to@var.

As an option, you can return an expression in the SELECT list and then use theHAVINGclause to filter the returned rows. (Note: TheHAVINGclause is evaluated after the result set is prepared; unlike theWHEREclause, which is evaluated when the row is accessed.)

Another option is to use an inline view to prepare the result set, and then the outer query can use a WHERE clause on the returned expression.

But strictly speaking, these methods apply predicates to expressions other than@var; they don't actually test the value in@var.

You need to assign an alias and test it in theHAVINGclause:

SELECT id, @var := id * 2 AS id_times_2 FROM user HAVING id_times_2 < 10

Note that if you are just using a formula to filter, rather than passing the internal results from one row to the next, you don't need this variable at all. You can write:

SELECT id, id * 2 AS id_times_2 FROM user HAVING id_times_2 < 10