The definition of variables in MySQL and the use of variable assignments

Tag: color is medium tar local str local SSI Global

Description: Now on the market defined variables of the tutorial and books are basically stored procedures are described, but the stored procedure variables can only be used in begin...end block, and ordinary variable definition and use are said to be less, for such problems can only be found in official documents to be explained.

Objective

There are two ways to define variables in a MySQL stored procedure:

1. Use set or select to assign a value directly, the variable name begins with @

For example:

Set @var = 1

You can declare anywhere in a session that the scope is the entire session, called the user variable.

2. Variables declared with the DECLARE keyword can only be used in stored procedures, called stored procedure variables, such as:

Declare int default 0

It is used primarily in stored procedures, or in storage-pass parameters.

The difference between the two is:

Variables declared with declare are initialized to NULL when the stored procedure is called. The session variable (that is, the variable at the start of the @) is not reinitialized, in a session, it is initialized only once, and then within the session is the result of the last calculation, which is equivalent to the global variable within the session.

Main content

• Local variables
• User variables
• Session variables
• Global variables

Session variables and global variables are called system variables.

A, local variable, valid only in the current Begin/end code block

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. The Declare statement is specifically used to define local variables, and you can use default to describe the defaults. The SET statement is to set variables of different types, including session variables and global variables.
Local variable definition syntax form

 
declare var_name [, var_name]... data_type [ DEFAULT value ];

  

	
   


  

For example, add the following sentence to the Begin/end statement block, accept the A/b variable passed in by the function and add it to the C variable through the SET statement.

The SET statement syntax formset var_name=expr [, var_name=expr]...;The SET statement can be used for assignment of local variables, as well as for declaration and assignment of user variables.

 
 
declare c int default 0;
set c=a+b;
select c as C;

Or use Select ... into ... Form Assignment

Select  into statement sentence:selectcol_name[,... ] into Var_name[,... [where ... ] ];

Example:

 
declare v_employee_name varchar(100);
declare v_employee_salary decimal(8,4);

select employee_name, employee_salary
into v_employee_name, v_employee_salary
from employees
where employee_id=1;

User variables, user variables are valid throughout the process of linking the client to the DB instance.

User variables in MySQL do not need to be declared in advance, when you use them, you can use "@variable name" directly.

The first usage: set @ num = 1; or set @num: = 1; // To create and initialize variables using the set statement, use @num variables directly

The second usage: select @num: = 1; or select @num: = field name from table name where ...,

The select statement is generally used to output user variables, such as select @variable name, used to output data whose data source is not a table.

Note that the above two types of assignment symbols can use "=" or ": =" when using set, but must use ": = assignment" when using select

User variables are related to the database connection. For variables declared in the connection, after the user variables are created in the stored procedure, the variables will disappear when the database instance is disconnected.

Variables declared in this connection cannot be used in another connection.

The user variable's variable name has the form @varname.

The name must begin with @.

You need to use the set statement when declaring a variable. For example, the following statement declares a variable named @a.

set @a = 1;

Declare a variable named @a and assign it a value of 1. The variable in MySQL is not strictly restricted to the data type. Its data type changes at any time according to the value you assign to it. (Declare variables are declared in SQL SERVER, and data types are strictly restricted.)

We can also use select statements to assign values to variables.

such as:

 
set @name = ‘‘;
select @name: = password from user limit 0,1;
#Get a record of the password field value from the data table to the @name variable. Output to the query result set after execution. 

(Note that the equals sign is preceded by a colon, and the trailing limit 0,1 is used to limit the returned result, which can be 0 or 1.) Equivalent to top 1 inside SQL Server)

If you write directly:select @name:=password from user;

If the query returns multiple values, the value of the @name variable is the value of the password field of the last record.

The user variable can be used for the current entire connection, but when the current connection is broken, the user variables that it defines will disappear.

User variables are defined using the following (we do not need to use the DECLARE keyword to define user variables, which can be used directly), and the variable names must begin with @:

#definition
select @variable name or select @variable name: = field name from table name where filter statement;
set @variable name;
#Assign @num for the variable name and value for the value
set @ num = value; or select @num: = value;

There are two ways to assign values to user variables, one is to use the "=" number directly, and the other is to use the ": =" number. The difference is that when you assign a user variable using the SET command, both methods are available, and when you use the SELECT statement to assign a value to a user variable, you can only use the ": =" method, because in the SELECT statement, the "=" Declare statement is specifically used to define a local variable. The SET statement is to set variables of different types, including session variables and global variables.

例如：

begin
#Routine body goes here ...
#select c as c;
declare c int default 0;
set @ var1 = 143; #Define a user variable and initialize it to 143
set @ var2 = 34;
set c = a + b;
set @ d = c;
select @sum: = (@ var1 + @ var2) as sum, @dif: = (@ var1- @ var2) as dif, @d as C; #Use user variables. @ var1 represents the variable name

set c = 100;
select c as CA;
end

#Execute the following statement in the query
call `order` (12,13); #Execute the stored procedure defined above
select @ var1; #See if the defined user variables can be output after the stored procedure is executed, and the results can output the user variables @ var1, @ var2.
select @ var2; 

After executing the order stored procedure, the newly created VAR1,VAR2 user variable in the stored procedure can be output with a SELECT statement, but the local variable c defined inside the stored procedure is not recognized.

System variables:

The system variables are divided into global variables and session variables.

Global variables are automatically initialized by the server to default values when MySQL is started, and these defaults can be changed by changing the My.ini file.

Session variables are initialized by MySQL each time a new connection is established. MySQL copies the values of all the current global variables in one copy. To be a session variable.

(That is, if you do not manually change the value of the session variable and the global variable after the session has been established, the values of all these variables are the same.) ）

The difference between a global variable and a session variable is that the modification of the global variable affects the entire server, but the modification of the conversation variable affects only the current session (that is, the current database connection).

We can use

Show session variables;

Statement to output all session variables (which can be abbreviated to show variables, without specifying whether to output global variables or session variables), the session variable is output by default. ) If you want to output all global variables:

Show global variables

Some system variables have values that can be changed dynamically using statements, but some system variables have a read-only value.

For those system variables that can be changed, we can make changes using the SET statement.

System variables have two @ in front of the variable name;

If you want to change the value of a session variable , use the statement:

 
set session varname = value;
or set @@session.varname = value;

Like what:

mysql> set session sort_buffer_size = 40000;
Query OK, 0 rows affected (0.00 sec)
Use select @@ sort_buffer_size; output to see what the changed value is.
If you want to change the value of a global variable, change the session to global:
set global sort_buffer_size = 40000;
set @@ global.sort_buffer_size = 40000;

However, to change the value of a global variable, you need to have super privileges.

(Note that Root is just a built-in account, not a permission, and this account has all the permissions in the MySQL database.) Any account as long as it has this permission named Super, you can change the value of the global variable, just as any user can call load_file or into Outfile,into dumpfile,load data infile as long as they have file permissions. ）

With the SELECT statement we can query the value of a single session variable or global variable :

 
 
select @@session.sort_buffer_size
select @@global.sort_buffer_size
select @@global.tmpdir

Any of the above mentioned sessions can be replaced with the local keyword.

Like what:

 
select @@ local.sort_buffer_size
local is a synonym for session.

Whether you are setting a system variable or querying the value of a system variable, as long as you do not specify whether it is a global variable or a session variable. are treated as session variables.

Like what:

 
set @@sort_buffer_size = 50000; 
select @@sort_buffer_size; 

None of the above specifies whether it is a blobal or a session, so it is all treated as a session.

Third, session variables

The server maintains a series of session variables for each connected client. When a client connects to a DB instance, the client's session variable is initialized with the current value of the corresponding global variable. 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. session variables are scoped to the user variable and are limited to the current connection. When the current connection is broken, all session variables that it sets are invalidated.

There are three ways to change the value of a session variable by setting a session variable:

set session var_name = value;
set @@ session.var_name = value;
set var_name = value; #The default session keyword is considered session by default

View all session variables
show session variables; 

There are three ways to view a session variable:

select @@var_name;
select @@session.var_name;
show session variables like "%var%";

Any of the above mentioned sessions can be replaced with the local keyword.

Like what:

Select @ @local . sort_buffer_size Local is a synonym for the session. 

Iv. Global Variables

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.

To set a global variable, there are two different ways:

set global var_name = value; // Note: global cannot be omitted here. According to the manual, if you do not specify GLOBAL, SESSION, or LOCAL when setting variables with the set command, SESSION is used by default.
set @@ global.var_name = value; // Same as above 

To view all global variables

To see a global variable, there are two different ways:

 
select @@global.var_name;
show global variables like “%var%”;

	 

Reference:

http://blog.csdn.net/JQ_AK47/article/details/52087484 (the above content is transferred from this article)

http://blog.csdn.net/seteor/article/details/17682551

Http://blog.csdn.net/jssg_tzw/article/details/39703889?utm_source=tuicool&utm_medium=referral

Http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html

http://blog.163.com/[email protected]/blog/static/173612348201162595425697/

Definition of variables in MySQL and assignment of variables (go)