Mysql User-Defined Variables User-Defined variable SET or DECLARE

In MySQL, we can store a value or a user-defined variable saved in a query result, and then apply the following statement.
SET defines the variable; SET @ var_name: = expr [, @ var_name = expr]...
SET @ var_name = expr [, @ var_name = expr]...
Note:① ": =" Or "=" is used here, but "=" has the same meaning in other statement statements, which is easy to confuse and sometimes leads to errors.We strongly recommend that you use ": =". ② In the statement, @ var_name = expr can be directly used for definition. This is not recommended and is equivalent to directly used without declaration.
The following are some examples: simple definition, display

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; # Here we use the equal sign. It feels like logical judgment and the result is incorrect.
SELECT @ pos: = @ pos + 1 as rank, name FROM players order by score DESC;

Print only the 100th users

 
 

  
  
 
   
 
    
SET @counter:=0;SELECT    users.*FROM    usersHAVING    (@counter:=@counter+1)%100=0ORDER BY    user_id;
 
   
 
  
 
 

Save the query result value:

SELECT @ total: = COUNT (*) FROM table_name; # simicolon split the join statement SELECT table_name.id COUNT (*) AS 'Count', count (*)/(SELECT @ total)AS percent FROM table_name, WHERE 1 = 1 group by year (birthday) order by year (birthday)

Note that the preceding SQL statement looks clear in logic, but it has the same execution effect and time as the following statement (maybe MySQL has been optimized internally)

SELECT     table_name.id    COUNT(*) AS 'count',    COUNT(*) /     (SELECT 

        COUNT(*) 

     FROM 

        table_name) AS percent 

FROM    table_name,WHERE 1=1GROUP BY YEAR(birthday)ORDER BY YEAR(birthday)

Other examples:

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

Question:

The variable here can only save one result value. How can we temporarily save a select result set.

Of course, the simple method is to create a table/view, or a temporary table. Is there a good way? To be studied.

DECLARE declares the variable and then assigns a value

DECLARE @var_name  var_type

Here is 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)   #using ":=" noationsFROM table_name; SELECT @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

Difference between DECLARE and SET:

 
 

  
  
DECLARE must specify the type, but SET does not.
  
  
SET defines User-Defined variables and Session Sensitive variables. The variables declared by DECLARE are generally local variables, and their effective ranges are declared functions or stored procedures.
  
  
The GLOBAL variable should be set global @ var_name and SET @ GLOBAL. var_name.
 
 

Note and Appendix: the scope of use of variables should be clear here. Official Website: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html