Mysql user-defined Variables User custom variable SET or DECLARE

Source: Internet
Author: User

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:
 
   
  
  1. DECLARE must specify type, and set is not required
  2. 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.
  3. 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.
  4. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.