Can MySQL's Select Statement Reliably Use and Redefine User Variables Within a Single Statement?

Defining and Using Variables within a Select Statement

In MySQL, it is generally not advisable to assign values to user variables and read those values within the same statement. While it may appear to work in certain scenarios, this behavior is not guaranteed and can vary depending on the specific statement's elements and MySQL server version.

For example, consider the following statement:

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

In this case, the expected result would be that the second column returns the value of 2 multiplied by the sum of the item column. However, MySQL may evaluate the statement in a different order, resulting in a NULL value for the second column.

The reason behind this behavior is that MySQL does not guarantee the evaluation order of expressions involving user variables. By modifying the statement slightly, such as introducing a GROUP BY clause, the evaluation order might change.

However, there is a workaround to achieve the desired functionality by using a subquery:

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

In this subquery, the variable @z is assigned the sum of the item column and then selected along with its doubled value. This approach ensures that the variable's value is correctly evaluated and used in the second column.

The above is the detailed content of Can MySQL's Select Statement Reliably Use and Redefine User Variables Within a Single Statement?. For more information, please follow other related articles on the PHP Chinese website!