(ten) the variables in MySQL

Defining Variables

The syntax is as follows

declare var_name[,...] type [default value];

The MYSQL variable definition can only be defined within a stored procedure or function, unlike Oracle/sql Server. The variable can only be scoped to the Begin...end block. The variable definition must be written at the beginning of the compound statement and before any other statements. You can declare multiple variables of the same type at once. You can use default to assign defaults.declare只能用在局部变量定义中。

#举例declare v_test int default 10;

Setting Variables

Way One

set var_name=expr,[,var_name2=expr ...];#举例set v_test=15;

Way Two

select col_name[,...] into var_name[,...] table_expr;#举例select sid into @a from test1;

If a col_name (such as a SID) returns multiple rows of values, @a finally only goes to the last row of values.

Variable Classification Local variables (do not need to add @)

Local variables are generally used in SQL statement blocks, such as the begin/end of stored procedures. Its scope is limited to the statement block, and the local variable disappears after the statement block has finished executing. Local variables are generally declared with declare, and default values can be used to describe them.

create procedure add(in a int,in b int)begin    declare c int default 0;#c定义的局部变量    set c = a + b;    select c as c;end;

user variable (one @)

User variables are scoped to a wider range than local variables. User variables can be used for the current entire connection, but when the current connection is broken, the defined user variables will disappear.
User variables are defined in the following way: @ variable name

#举例set @a = 1;set @b = 2;select @sum:=(@a + @b), @dif:=(@a - @b);

Results

Session variables (two @@)

The server maintains a series of session variables for each connected client. When the client connects, the client's session variables are initialized with the current values of the corresponding global variables. Setting a session variable does not require special permissions, but the client can only change its own session variables, not the session variables of other clients. 会话变量的作用域与用户变量一样，仅限于当前连接. When the current connection is broken, all session variables that it sets are invalidated.

 #设置会话变量有如下三种方式: set  session  var_name =  Value ;  set  @@session . Var_name = value ;  set  var_name = value ; #查看一个会话变量也有如下三种方式: select  @ @var_name;  select  @@session . Var_name;  show  session  Variables like   "%var%" ;   

Global Variables (two @@)

Global variables affect the overall operation of the server. When the server starts, it initializes all global variables to their default values. These default values can be changed in the options file or in the options specified on the command line. To change global variables, you must have super permissions. Global Variables Act on the entire life cycle of the server, but not across reboots. That is, the global variables for all settings are invalidated after the reboot. For the global variable to continue to take effect after it restarts, the appropriate configuration file needs to be changed.

#要设置一个全局变量，有如下两种方式：set global var_name = value;set @@global.var_name = value; //同上#要想查看一个全局变量，有如下两种方式：select @@global.var_name;show global variables like "%var%";

Note: Global cannot be omitted here. According to the manual, the set command sets the variable without specifying global, session, or local, and the session is used by default.

Reference

http://blog.csdn.net/lxgwm2008/article/details/7738306
Http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html

(ten) the variables in MySQL