MySQL: reusing user defined variables in a single SELECT statement

I did a search on stackoverflow but couldn't find the exact answer to my question, so please help me if you can.

"I want" to build a SQL statement like this:

  @tax1 := (complicated calculation formula),
  @owe := (another complicated calculation formula),
  IF(@owe=0, 0,@tax1/@owe)
FROM ...


However, in the MySQL documentation regarding user-defined variables , it advises against it:

Generally, you should never assign a value to a user variable and read the value within the same statement. You may get the expected results, but this is not guaranteed. The order of evaluation of expressions with user-defined variables is undefined and can be changed based on the elements contained in this operator; In addition, this order is not guaranteed to be the same between MySQL Server versions. In SELECT @a, @a: = @a + 1, ..., you might think that MySQL will evaluate @a first and then do the second lesson. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to choose an execution plan with a different order of evaluation.

My goal is to avoid copying and pasting very complex formulas because it makes it difficult to read, and if every formula changes, I need to make sure it's changed in all places - BUT I really know, Work well.

Also, I know ALIAS doesn't work because aliases only work in GROUP BY, HAVING and ORDER clauses.

I read in some other posts to execute a subquery first, to calculate @ tax1 and @owe first, and then use another query to combine the results. BUT I think the performance might be less efficient than just copying and pasting these formulas in place.

Does anyone have a suggestion on what they will do? Or am I sticking with the choice between readability and performance?

Thanks in advance.


source to share

2 answers

Yes, the only way to do this in SQL without user-defined variables is to write a derived subquery of the table. Then you can use column aliases to refer to the results of these complex expressions:

SELECT tax1, owe, IF(owe=0, 0,tax1/owe) AS ratio
    (complicated calculation formula) AS tax1,
    (another complicated calculation formula) AS owe
  FROM ...
) AS _sub


MySQL has some issues with subquery optimization, but the derived table case is not one of the bad cases.

A warning about MySQL and subqueries is the use of subqueries in range conditions in the WHERE clause. MySQL cannot figure out that the subquery is constant, and it will re-evaluate the subquery as a dependent subquery even if it is not needed.



I regularly use variables in select statements for sequential scoring, aggregation, group statistics, and data classification.

This is an example:

MySQL Create a list of top X records for each category by concatenating each record with its previous difference

create table if not exists 
        closemovers engine=memory 
        , date 
        , close 
        , rank 
        , prevclose 
        , sign 
        , cumm 
        ( select 
                , `date` 
                , `close` 
                , @rank := if( @code = code , @rank + 1 , 1) as rank 
                , @prevclose := if( @code = code , cast( @prclose as decimal(10,3) ), null) as prevclose 
                , if(@code = code, sign( @prclose - close), NULL) as sign 
                , @cumm := if(@code = code and @psign = sign(@prclose - close), @cumm + 1 , 1) as cumm
                , @psign := sign(@prclose - close) 
                , @code := code
                , @prclose := close 
        order by 
            code, date 
         ) as ranked 
                using (code) 
        rank < 10 
        and sign is not null ;


I hope this can give you a hint



All Articles