21.MySQL之系统变量与用户自定义变量

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录

本节讲述MySQL中的系统变量与用户自定义变量的定义及用法

变量

先来看一个图：

系统变量

• 系统变量分为全局系统变量（global）和会话系统变量（session）。
• 每一个客户机成功连接服务器后，都会产生与之对应的会话。会话期间，服务实例会在服务器内存中生成与该会话对应的会话系统变量。这些会话系统变量的初始值就是全局系统变量值的复制。为了标记不同的会话，会话系统又新增了一些变量，这些变量是全局系统变量没有的。
• 会话系统的特点在于，它仅仅用于定义当前会话的属性，会话期间对某个会话系统变量值的修改，不会影响到其他会话同一个会话系统变量的值。
• 全局系统变量的特点在于，它是用于定义MySQL服务实例的属性、特点。当某个会话对某个全局系统变量值的修改会导致其他会话中同一全局系统变量值的修改。
• 全局系统变量对所有会话系统变量生效，会话系统变量包括全局系统变量。
• 系统变量以’@@’开头

查看系统变量的值

• 查看全局系统变量：show global variables;
• 查看当前会话的会话系统变量：show session variables;或者show variables;
• 查看某个全局系统变量：show global variables like ‘变量名’;
• 查看某个会话系统变量：*show session variables like ‘变量名’;

举个栗子

mysql> show global variables like 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | utf8  |
+----------------------+-------+
1 row in set, 1 warning (0.21 sec)

mysql> show session variables like 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | utf8  |
+----------------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> # 修改character_set_client的值，再次查看global与session的变化
mysql> set character_set_client = gbk;
Query OK, 0 rows affected (0.16 sec)

mysql> show session variables like 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | gbk   |
+----------------------+-------+
1 row in set, 1 warning (0.04 sec)

mysql> show global variables like 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | utf8  |
+----------------------+-------+
1 row in set, 1 warning (0.00 sec)

设置系统变量的值

重新设置全局系统变量的值：
- set @@global.全局系统变量名 = 值;
- set global 全局变量名 = 值;

重新设置会话系统变量：
- set @@session.会话系统变量 = 值;
- set session 会话系统变量名 = 值;
- set 会话系统变量名 = 值;

用户自定义变量

  用户自定义变量分为用户会话变量（以@开头）以及局部变量（不以@开头）。

会话系统变量

  当定义了会话系统变量，在会话期间，该会话系统变量一直有效。不同会话之间的会话系统变量是不能相互访问的。

1. 定义用户变量与赋值（两者是同时进行的）
   
   • set方法：set @变量1 = 值1, 变量2 = 值2…;
   • select方法：select @变量1 := 值1, 变量2 := 值2…;
   • select方法二：select 表 into @变量2, into @变量2…;

举个栗子

mysql> # set方法
mysql> set @name = 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> select @name;
+-------+
| @name |
+-------+
| mysql |
+-------+
1 row in set (0.02 sec)

mysql> # select方法一
mysql> select @age = 18;
+-----------+
| @age = 18 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.15 sec)

mysql> # select方法二
mysql> select count(*) from goods into @goodsnum;
Query OK, 1 row affected (0.15 sec)

mysql> select @goodsnum;
+-----------+
| @goodsnum |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

局部变量

  declare命令专门用于定义局部变量及对应的数据结构。局部变量必须定义在存储程序中（如函数、触发器、存储过程以及事件），并且局部变量的作用范围仅仅局限于存储程序中，脱离存储程序没有丝毫意义。局部变量主要用于下面三种场合：
- 定义在存储程序的begin-end语句块之间。此时，先使用declare定义，并且指定其数据类型。然后用set或select为其赋值。
- 当局部变量作为存储过程或者函数的参数使用，此时不用declare定义，但是需要指出参数的数据类型。
- 局部变量用于sq语句中。

举个例子

mysql> delimiter $  # 重置命令结束标记
mysql> create procedure aaa(in a int)
    -> modifies sql data
    -> begin
    ->  declare s1 int;
    ->  set a = 1;
    ->  select count(*) from goods into s1;
    -> end$
Query OK, 0 rows affected (0.00 sec)

tips

• “set”命令不会导致my.ini配置文件的内容发生改变
• select方法两种定义用户会话变量的区别是，方法一会产生结果集，方法二不会产生结果集。