Can MySQL temporary variables be used in WHERE clause?

Question

In MySQL, can temporary variables be used in the WHERE clause?

For example, in the following query:

SELECT `id`, @var := `id` * 2 FROM `user`

@var successfully set to twice the value of `id`

However, if I try to filter the result set to only include results where @var is less than 10:

SELECT `id`, @var := `id` * 2 FROM `user` WHERE @var < 10

Then I don't get any results.

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

Answer 1

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, @var < 10< 10 will 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 in other expressions 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 the HAVING clause to filter the returned rows. (Note: The HAVING clause is evaluated after the result set is prepared; unlike the WHERE clause, 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.

Answer 2

You need to assign an alias and test it in the HAVING clause:

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