(10) mysql variables and 10 mysql Variables

(10) mysql variables and 10 mysql Variables
Define Variables

Syntax:

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

MYSQL variable definitions can only be defined in stored procedures or functions, unlike Oracle/SQL Server. This variable can only be used in begin... In the end block. The variable definition must begin with a compound statement and be prior to any other statement. Multiple variables of the same type can be declared at a time. You can use default to assign the default value.Declare can only be used in the definition of local variables.

# Declare v_test int default 10;

Set Variables

Method 1

Set var_name = expr, [, var_name2 = expr...]; # example set v_test = 15;

Method 2

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

If col_name (such as sid) returns multiple rows, @ a will only go to the last row.

Variable Classification Local variable (@ is not required @)

Local variables are generally used in SQL statement blocks, such as the begin/end of the stored procedure. Its scope is limited to the statement block. After the statement block is executed, the local variable disappears. Local variables are generally declared using declare. default can be used to describe the default value.

Create procedure add (in a int, in B int) begin declare c int default 0; # set c = a + B; select c as c; end;

User variable (one @)

User variables have a wider scope than local variables. User variables can act on the current entire connection, but when the current connection is disconnected, all user variables defined by the user variables will disappear.
User variables are defined as follows: @ variable name

# For example, set @ a = 1; set @ B = 2; select @ sum: = (@ a + @ B), @ dif: = (@ a-@ B );

Result

Session variable (two @@)

The server maintains a series of session variables for each connected client. When the client is connected, use the current value of the corresponding global variable to initialize the client session variable. You do not need special permissions to set session variables, but the client can only change its own session variables, rather than the session variables of other clients.The scope of session variables is the same as that of user variables and is limited to the current connection.. When the current connection is disconnected, all session variables it sets will be invalid.

# Set session var_name = value; set @ session. var_name = value; set var_name = value; # There are three ways to view a session variable: 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 the default value. These default values can be changed in the options file or the options specified in the command line. To change global variables, you must have the SUPER permission. Global variables apply to the entire lifecycle of the server, but cannot be restarted across regions. That is, all the global variables set after the restart are invalid. To make global variables take effect after restart, you need to change the corresponding configuration file.

# Set global var_name = value; set @ global. var_name = value; // same as above # to view a global variable, you can select @ global. var_name; show global variables like "% var % ";

Note: The global here cannot be omitted. According to the Manual, if GLOBAL, SESSION, or LOCAL is not specified when the set command is used to set variables, 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