Can I Use a User-Defined Variable in a Single MySQL SELECT Statement?

Can I Define a Variable within a SELECT and Use It in the Same Statement?

The possibility of defining and utilizing a variable within the same SELECT statement is often questioned. Consider the following example:

SELECT 
    @z:=SUM(item),
    2*@z
FROM
    TableA;

In such a scenario, the second column consistently returns NULL. This is peculiar because a similar operation using a stored procedure functions as expected:

SELECT 
    @z:=someProcedure(item),
    2*@z
FROM
    TableA;

Explanation

The MySQL documentation explicitly states that assigning a value to a user variable and reading it within the same statement is unreliable. The order of evaluation for user variables is undefined and may vary based on statement elements and MySQL Server versions. Therefore, the expected results cannot be guaranteed.

Solution

To achieve the desired outcome, you can employ a subquery:

select @z, @z*2
from (SELECT @z:=sum(item)
      FROM TableA
     ) t;

The above is the detailed content of Can I Use a User-Defined Variable in a Single MySQL SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!