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

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

理解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>

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
$$

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

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)

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

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)

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

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

提示错误信息
在这里插入图片描述
定义局部变量 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

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 ;

修改全局变量

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)