mysql临时变量吗,可以在WHERE子句中使用MySQL临时变量吗？

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 is 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 get no results.

How can I filter the results based on the value of @var?

解决方案

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're just using the formula for filtering, not for communicating internal results from one row to the next, you don't need the variable at all. You can write:

SELECT id, id * 2 AS id_times_2

FROM user

HAVING id_times_2 < 10