Mysql variables (local variables/global variables)

Mysql variables are divided into local variables @ and global variables @. They are all in different sizes, but the usage of the two variables is slightly different. Next I will introduce the production usage.

Commonalities: case-insensitive, all variables

Difference: global variables are read-only in the system and can be modified in the configuration file.

Glossary of mysql variables:

1. User variable: Start with "@", in the form of "@ variable name"
The user variable is bound to the mysql client. The set variable takes effect only for the client currently in use.
2. GLOBAL variables: The global variables are defined in the following two forms: set GLOBAL variable name or set @ global. variable name.
Valid for all clients. You can set global variables only when you have super permissions.

3. session variable: valid only for connected clients.

4. Local variable: The scope of the variable is between the in and end statement blocks. Variable set in the statement Block
The declare statement is used to define local variables. The set statement is used to set different types of variables, including session variables and global variables.

Differences between terms:

User-Defined variables are called user variables. In this case, both session variables and global variables can be User-Defined variables. The difference is that they are effective for the current client or for all clients. Therefore, user variables include session variables and global variables.

The differences between local variables and user variables are as follows: 1. User variables start. The local variable does not have this symbol. 2. Different variables are defined. User variables use the set statement, and local variables use the declare statement to define 3. scope of action. The local variable is valid only between the in-end statement blocks. After the begin-end statement block is run, the local variable disappears.

Therefore, the hierarchical relationship between them is: variables include local variables and user variables. User variables include session variables and global variables.

NOTE: If set @ var does not specify GLOBAL or SESSION, user variables are defined by default.
You can define user variables in two ways:

1. "=", such as set @ a = 3, @ a: = 5

2. ": = ". Select is often used in this way
Conclusion: The difference between using select and set to set variables is that set can be set in the above two forms. Select can only set variables in the form of ": = ".
Practice accumulation: User variables automatically disappear after the mysql client exits. Then I open the client and use "select @ a;" to display the changed value as null. The initialization of undefined variables is null begin.

Actual Problems

Set the configuration impact of Constants on group_concat:
SET @ GROUP_CONCAT_MAX_LEN = 4
The syntax mentioned in the manual is as follows:
SET [SESSION | GLOBAL] group_concat_max_len = val;

 

Show variables;

Mysql> show variables;

Take a look at the value of the global variable:

Mysql> select @ version;
+ ----------- +
| @ Version |
+ ----------- +
| 5.5.14 |
+ ----------- +
1 row in set (0.00 sec)

Assignment of local variables

Method 1:

The Code is as follows:	Copy code
Mysql> set @ name = "zhouyinghou "; Query OK, 0 rows affected (0.00 sec) View the value of a local variable Mysql> select @ name; + ------------- + | @ Name | + ------------- + | Zhouyinghou | + ------------- + 1 row in set (0.00 sec)

Method 2:

You can also use select to assign values and output values.

The Code is as follows:	Copy code
Mysql> select @ sex: = "male "; + -------------- + | @ Sex: = "male" | + -------------- + | Male | + -------------- + 1 row in set (0.00 sec)

Method 3:

Select can also be extracted from a table

The Code is as follows:	Copy code
Mysql> drop table zyh; Query OK, 0 rows affected (0.09 sec) Mysql> create table zyh (id int, name char (20 )); Query OK, 0 rows affected (0.19 sec) Mysql> insert into zyh values (1, 'zhouyinghou '); Query OK, 1 row affected (0.09 sec) Mysql> select @ name: = name from zyh; + ------------- + | @ Name: = name | + ------------- + | Zhouyinghou | + ------------- + 1 row in set (0.00 sec)

Method 4:

This statement can also be written as follows:

The Code is as follows:	Copy code
Mysql> select name from zyh into @ name; Query OK, 1 row affected (0.00 sec)

1 row affected indicates that the data has not been updated.

The Code is as follows:	Copy code
Mysql> select @ name ->; + ------------- + | @ Name | + ------------- + | Zhouyinghou | + ------------- + 1 row in set (0.00 sec) Note: @ name: = name

And into @ name

Into @ name can only accept one row of Value

The Code is as follows:	Copy code
Mysql> select name from zyh into @ name; ERROR 1172 (42000): Result consisted of more than one row Mysql> select @ name: = name from zyh; + ------------- + | @ Name: = name | + ------------- + | Zhouyinghou | | Zhouhuan | + ------------- + 2 rows in set (0.00 sec) Mysql> select @ name ->; + ---------- + | @ Name | + ---------- + | Zhouhuan | + ---------- + 1 row in set (0.00 sec)

Example:

Query the names of two adjacent persons in zhouhuan

If you follow the oracle writing method, you can:

The Code is as follows:

Copy code

Mysql> select name from zyh where id in (select id-1, id + 1 from zyh where name = 'zhouhuanc '); ERROR 1241 (21000): Operand shoshould contain 1 column (s) Mysql can be queried by assigning values to variables: Mysql> select id from zyh where name = 'zhouhuanc' into @ id; Query OK, 1 row affected (0.00 sec) Mysql> select @ id; + ------ + | @ Id | + ------ + | 2 | + ------ + 1 row in set (0.00 sec) Mysql> select name from zyh where id = @ id-1 or id = @ id + 1; + ------------- + | Name | + ------------- + | Zhouyinghou | | Kaka | + ------------- + 2 rows in set (0.00 sec)