MySQL variable (local variable/global variable) usage description

In common: case-insensitive, both variables

Different points: Global variables are read-only in the system and can be modified in a configuration file

Terminology classification for MySQL variables:

1. User variable: Starting with "@" in the form of "@ Variable name"
The user variable is bound to the MySQL client and is set to a variable that only takes effect on the client used by the current user
2. Global variables: When defined, it appears in the following two forms, set global variable name or SET @ @global. Variable Name
Takes effect for all clients. Global variables can be set only with super permissions

3. Session variables: Valid only for connected clients.

4. Local variables: The scope is between begin and end statement blocks. Variables set in the statement block
Declare statements are specifically used to define local variables. A SET statement is a variable that sets different types, including session variables and global variables

the difference between the terms of popular understanding:

User-defined variables are called user variables. In this sense, both session and global variables can be user-defined variables. Just whether they are valid for the current client or for all clients. Therefore, user variables include session variables and global variables

The difference between a local variable and a user variable is two: 1. The user variable begins with "@". The local variable does not have this symbol. 2. Define variables differently. The user variable uses the SET statement, and the local variable uses the Declare statement to define 3. Scope of action. Local variables are valid only between Begin-end statement blocks. After the Begin-end statement block runs out, the local variable disappears.

So, finally, the hierarchical relationship between them is that the variables include local variables and user variables. User variables include session variables and global variables.

With a memo, set @var if global or session is not specified, the user variable is defined by default
Define user variables in two ways:

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

2. ": =". Select is often used in this way
Summary: Using select and set to set the difference between variables, set can use both of these forms to set variables. The select can only use the form ": =" to set the variable
Practice accumulation: User variables will automatically disappear after the MySQL client exits. Then I open the client and use "select @a;" To show that the changed value is null. Description, undefined variable initialization is null

Problems in the real world

Set the effect of constants on the configuration of Group_concat ():
SET @ @GROUP_CONCAT_MAX_LEN =4
The syntax for setting is mentioned in the manual:
SET [Session | GLOBAL] Group_concat_max_len = val;

can show variables;

Mysql> Show variables;

Look at the value of the global variable:

Mysql> SELECT @ @version;
+-----------+
| @ @version |
+-----------+
| 5.5.14 |
+-----------+
1 row in Set (0.00 sec)

Local variable Assignment

Method One:

The code is as follows	Copy Code
mysql> Set @name = "Zhouyinghou"; Query OK, 0 rows Affected (0.00 sec) To view the value of a local variable Mysql> Select @name; +-------------+ | @name | +-------------+ | Zhouyinghou | +-------------+ 1 row in Set (0.00 sec)

Method Two:

You can also assign a value with select and output it

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

Method Three:

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 Four:

This statement can also be written like this:

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) Also note: @name: =name

and into @name

into @name can only accept one row of values

The code is as follows	Copy Code
Mysql> select name from Zyh into @name; ERROR 1172 (42000): result consisted of the 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 Zhouhuan the names of two adjacent people

If you follow Oracle's instructions, 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= ' Zhouhuan '); ERROR 1241 (21000): Operand should contain 1 column (s) You can use variable assignment to query in MySQL: Mysql> Select id from zyh where name= ' Zhouhuan ' into @id; Query OK, 1 row Affected (0.00 sec) Mysql> Select @id; +------+ | @id   | +------+ |    2 | +------+ 1 row in Set (0.00 sec) M Ysql> select name from Zyh where id= @id-1 or id= @id +1; +-------------+ | name        | +-------------+ | zhouyinghou | | kaka        | +-------------+ 2 rows in Set (0.00 sec)