理解MySQL的会话变量、局部变量和全局变量

理解MySQL的会话变量、局部变量和全局变量

1.MySQL变量分类

根据作用范围不同，分为会话用户变量和局部变量。
会话用户变量：作用域和会话变量一样，只对当前连接会话有效 。
局部变量：只在 BEGIN 和 END 语句块中有效，局部变量只能在存储过程和存储函数中使用 。
全局变量：在MySQL服务器启动运行后，系统内置变量 。

2.变量定义

（1）会话变量：

MySQL 编码规范，MySQL 中的用户变量以一个 “@” 开头，可以与关键字区分。
@varn @varc 用户会话变量：

mysql> set @varn=100,@varc='AAA';
Query OK, 0 rows affected (0.00 sec)

mysql> select @varn,@varc ;
+-------+-------+
| @varn | @varc |
+-------+-------+
|   100 | AAA   |
+-------+-------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9
10
11
12

（2）局部变量

loc_n loc_c ，在存储过程中定义，局部变量。

delimiter $$
create procedure  pro_test_var()
BEGIN
DECLARE loc_n INTEGER ;
DECLARE loc_c varchar(10) ;

set loc_n=10;
set loc_c='BBB';

set @varn=1 ,@varc='CCC';
select @varn,@varc ;
select loc_n,loc_c ;
 
END
$$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在存储过程中可以调用修改用户会话变量。执行效果：

mysql> call pro_test_var();
+-------+-------+
| @varn | @varc |
+-------+-------+
|     1 | CCC   |
+-------+-------+
1 row in set (0.00 sec)

+-------+-------+
| loc_n | loc_c |
+-------+-------+
|    10 | BBB   |
+-------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

在会话中无法查询局部变量：

mysql> select loc_n,loc_c ;
ERROR 1054 (42S22): Unknown column 'loc_n' in 'field list'
mysql> select @varn,@varc ;
+-------+-------+
| @varn | @varc |
+-------+-------+
|     1 | CCC   |
+-------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

3.用户会话变量限制

（1）不能做存储过程调用参数

在存储过程中做入口参数，提示语法错误

create procedure pro_test_callvar(in @varc varchar(10))

mysql> delimiter $$
mysql> create procedure  pro_test_callvar(in @varc varchar(10))
    -> BEGIN
    -> DECLARE loc_n INTEGER ;
    -> DECLARE loc_c varchar(10) ;
    -> 
    -> set loc_n=10;
    -> set loc_c='BBB';
    -> 
    -> set @varn=1 ,@varc='CCC';
    -> select @varn,@varc ;
    -> select loc_n,loc_c ;
    ->  
    -> END
    -> $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@varc varchar(10))
BEGIN
DECLARE loc_n INTEGER ;
DECLARE loc_c varchar(10) ;

se' at line 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

（2）不能用于fetch

CREATE DEFINER=`root`@`%` PROCEDURE `pro_test_fetch_cursor`()
begin
  #declare var_tel varchar(15);
  declare done int default 0 ;
  declare cur_tel cursor for select t.hm_detail from check_cmcc t order by t.hm_detail limit 10;
  #游标结标志，必须在游标声明后声明！！！否则报错
  declare continue handler for not found set done = 1 ;

  #使用游标前打开游标
  open cur_tel ;
  #循环的标签名称，要和end loop 对应。
  getloop :  loop
    # 将游标变量赋值到用户会话变量  
    fetch cur_tel  into  @var_tel ;
		
    if done = 1 
      then leave getloop ;
    end if ;

  end loop getloop;
  close cur_tel ;

end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

提示错误信息：

定义局部变量 declare var_tel varchar(15)，就可以使用fetch 。

CREATE DEFINER=`root`@`%` PROCEDURE `pro_test_fetch_cursor`()
begin
  declare var_tel varchar(15);
  declare done int default 0 ;
  declare cur_tel cursor for select t.hm_detail from check_cmcc t order by t.hm_detail limit 10;
  #游标结标志，必须在游标声明后声明！！！否则报错
  declare continue handler for not found set done = 1 ;

  #使用游标前打开游标
  open cur_tel ;
  #循环的标签名称，要和end loop 对应。
  getloop :  loop
      
    fetch cur_tel  into var_tel ;
	
			
    if done = 1 
      then leave getloop ;
    end if ;

  end loop getloop;
  close cur_tel ;

end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4. 全局变量

由系统提供，在整个数据库有效，用法：@@global.var_name
注意：
如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session 。

-- 查看全局变量 
show global variables; 
SHOW GLOBAL VARIABLES LIKE '%sort%';
mysql> SHOW GLOBAL VARIABLES LIKE '%sort_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_sort_buffer_size | 1048576   |
| myisam_sort_buffer_size | 134217728 |
| sort_buffer_size        | 4194304   |
+-------------------------+-----------+
3 rows in set (0.00 sec)

-- 查看某全局变量 
select @@global.sort_buffer_size ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

修改全局变量：

mysql> select  @@global.sort_buffer_size;
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
|                   8388608 |
+---------------------------+
1 row in set (0.00 sec)

mysql> set @@global.sort_buffer_size=4194304;
Query OK, 0 rows affected (0.00 sec)

mysql> select  @@global.sort_buffer_size;
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
|                   4194304 |
+---------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

5. 变量理解

（1）会话用户变量

作用域：当前会话
定义位置：会话的任何地方
语法：加@符号，不用指定类型

（2）局部变量

作用域：定义程序块的begin到end
定义位置：begin到end 结束中的第一次声明
语法：一般不用加@，需要指定数据类型

（3）全局变量

作用域：MySQL服务器每次启动将为所有的全局变量赋初始值，针对于所有会话（连接）有效，不能跨服务器重启 。
定义位置：系统内置
语法：加前缀 @@global