Mysql user-defined Variables User custom variable SET or DECLARE

in MySQL, we can save a value or a query result in a user-defined variable, and then in the following statement in the application.
set defines variables;SET @var_name: = expr [, @var_name = expr] ....
SET @var_name = expr [, @var_name = expr] ....
Note:① here is ": =" or "=", but "=" has the same meaning in other statement statements, is easily confused, and sometimes goes wrong. It is strongly recommended to use ": =". ② in the statement, can be used directly with @var_name = expr definition, do not advocate this, the equivalent of not declaring direct use.
Here are some examples: a simple definition that shows

mysql>SET @t1=1, @t2=2, @t3:=4;mysql>SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;+------+------+------+--------------------+| @t1 | @t2 | @t3 | @t4: = @t1 + @t2 + @t3 |+------+------+------+----------    ----------+|    1 |    2 |                  4 | 7 | +------+------+------+--------------------+

Someone use them for rank

set @pos     :=    0;   #这里用等号, it feels like a logical judgment, and the result is wrong   
select @pos: [email protected]+1 as Rank,name From players ORDER by score DESC;   

Print only the 100th users

  
 

   
  
 
   
 
    
SET@counter:=0;SELECTusers.* fromUsers having    (@counter:=@counter+1)% -=0ORDER byuser_id;
 
   
 
  
  
 

Save query result values value:

SELECT @total: = COUNT (*)     from  table_name;  # Simicolon Split single statement SELECT     table_name.id    Count (*) as ' count ',    count (*)/     (SELECT         @total) As percent from    Table_name,where 1=1group to Year (birthday) ORDER by year (birthday)

Note that the SQL statement above looks logical, but it is the same as the execution of the following statement (possibly MySQL internal optimization)

Select     table_name.id    count (*) as ' count ',    count (*)/     (select

COUNT ( *)  

  from

  table_name)  

From    Table_name,where 1=1group by year (birthday) ORDER by year (birthday)

Some other examples:

http://www.mysqldiary.com/user-defined-variables/

Questions:

The variable here can only hold a result value, how can I temporarily save a select out of the result set.

Of course, the simple way is to create a table/view, or a temporary table, there is a good way? Pending study.

Declare declares the variable and then assigns the value

DECLARE @var_name  Var_type

Here's an example:

Example 1:

DECLARE @total INT DECLARE @total_distinct intselect     @total: =count (lice_no)    #using ": =" Notationfrom    table_name;    Select @total_distinct: =count (distinct lice_no)   table_nameSELECT @total-@total_distinct

Example 2:

DECLARE @register_count int;declare @total_count INT; SELECT     @register_count: = count (1) from    T1 WHERE ID > 10;

    SELECT     @total_count: = count (1) from    T1;    SELECT     (@register_count * @total_count) as Ratio2

DECLARE and SET differences:

 
   
  

  
    
   
DECLARE must specify type, and set is not required
  
    
   
The set defines a user-defined variable, which is the session sensitive; The variable declared by DECLARE is usually a local variable, and its effective interval is in the declared function or stored procedure.
  
    
   
After the variable is not used, you can oh that select var_name: = Init_value. Set to the initial value. Convenient for later use, avoid the variable value in the next reference to the previous error caused by the old finger.
  
    
   
Define global variables should be set global @var_name the latter set @ @GLOBAL. var_name
 
   
  

Note and Appendix: here the scope of the use of variables should be clear. Official Introduction Website: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
Reprint Please specify source: http://blog.csdn.net/acema/article/details/37114379