MYSQL基础之浅聊 变量

在前面聊存储过程和函数的时候简单的用了赋值变量，现在聊一下变量

在MYSQL数据库的存储过程和函数种，可以使用变量存储查询或者计算中间的数据，或者输出最终的结果数据。MYSQL种的变量分为系统变量和自定义变量

系统变量

看其名知道其意思，就是这些是系统自己定义好的，而不是用户定义的，属于服务器层面。

系统变量分类

启动MYSQL服务，生成MYSQL服务器实例期间，MYSQL将为MYSQL服务器内存种系统变量赋值，这些系统变量定义了当前MYSQL服务实例种的属性，特赠。这些系统变量的值包含：编译MYSQL时参数的默认值，配置文件种的参数等，可以在官网文档中查询系统变量

然而系统变量也分全局系统变量（添加globla关键字）和会话系统变量（添加session关键字）。有时候会讲全局系统变量称之为全局变量，有时也会把会话吸引变量称为local变量。如果不写默认时会话级变量。补充你一点静态变量（在MYSQL服务实例运行期间它们的值不能使用set动态修改）属于特殊的全局系统变量。

每一个MSYQL客户机成功连接MYSQL服务器后，都会产生与之对应的会话。会话期间，MYSQL服务实例会在MYSQL服务器内存种生成与这次对话对应的会话变量，这些会话系统变量的初始值都是全局系统变量值的复制。

• 全局系统变量针对所有的会话（连接）有效，但不能跨重启。
• 会话系统变量针对于当前会话（连接）有效。会话期间，当前会话对于某个会话系统变量的修改不会影响其它会话同一个会话系统变量的值。
• 当然其中一个会话修改全局系统变量值会影响其它会话的全局系统变量修改。

当然有些系统变量只能时全局的，例如：

• max_connections: 设置服务器最大的连接数量的。

但是有些系统系统变量可以全局也可以是会话，例如：

• character_set_client : 用户设置客户端的字符集

有些系统变量作用域只能是当前会话，比如：

• pseudo_thread_id: 用户标记当前会话的MYSQL连接的ID.

查看系统变量 通过SHOW

既然有系统变量，自然可以通过文档进行查询，但是有时候最方便的方式还是直接在MYSQL服务上查看。

# 查看所有全局变量
SHOW GLOBAL  VARIABLES ;

# 如果查看某个变量
SHOW GLOBAL  VARIABLES  LIKE '%标识符%';

# 查看所有的会话变量
SHOW SESSION VARIABLES; # 或者是  SHOW VARIABLES;

# 如果查看某个变量
SHOW SESSION  VARIABLES  LIKE '%标识符%'; # 或者是  SHOW VARIABLES  LIKE '%标识符%';

可以看出在会话变量中有些是全局变量，毕竟全局变量也是会在会话变量中出现的。

查看执行系统变量

在为MYSQL编码规范，MYSQL中的系统变量是以两个@开头

• @@global仅用于标记全局系统变量。
• @@session仅用于标记会话系统变量

# 查看指定的系统变量的值
SELECT @@global.变量名;

# 查看指定的会话变量的值
SELECT @@session.变量名; # SELECT @@变量名; @@ 首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

举例看一下

SELECT @@global.max_connections;

修改系统的变量

在使用数据库的时候，有时候会修改数据库的系统变量。

• 修改MYSQL的配置文件（文件名为：my.ini），从而修改MYSQL系统变量的值，然后重启就会生效，也是长久生效。

• MYSQL服务运行期间，可以使用SET 命令重新设置系统变量的值。但是一般重启就会失效。

  # 为某个系统变量赋值
  # 方式一
  SET @@global.变量名=变量值;
  #方式二
  SET global 变量名=变量值;
  
  # 修改会话变量
  # 方式1
  SET @@session.变量名=变量值;
  # 方式2
  SET SESSION 变量名=变量值;
  

  例子修改全局变量

  SET GLOBAL max_connections=100;
  # 然后查看
  SHOW GLOBAL VARIABLES LIKE 'max_connections'; # 不过重启后就会失效，而且因为修改的全局变量所以也会影响其它会话中的最多连接数
  

  

自定义变量

用户变量分类

用户变量是用户自己定义的，在MYSQL编码规范中，用户变量是以一个@开头，但是根据作用范围不同，也分为会话用户变量和局部变量。

• 会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
• 局部变量：只有在BEGIN和END语句块中有效。局部变量只能在存储过程和存储函数中使用。

现在看一下具体如何声明：

全局会话变量

这里局明白在前面聊存储函数和存储过程中那样定义变量了。

# 方式1  通过SET 和赋值符号 =或者:= 进行赋值
SET @用户变量=用户变量值;
SET @用户变量:=用户变量值;


#  通过 SELECT 和赋值符号 := 或 INTO
SELECT @用户变量:= 表达式 [FROM 等语句]
SELECT 表达式 INTO @用户变量 [FROM 等语句] 

举例子

# 例子1
SET @a=12;
SELECT @a;
# 例子2
SELECT @b:=120   FROM DUAL;
SELECT @b;
# 例子3
SELECT 130 INTO @c   FROM DUAL;
SELECT @c;

# 例子4
SET @d:=@a+@b;
SELECT @d;

# 在看一下如何结合表
SELECT COUNT(*) INTO @user_count  FROM emp;
SELECT @user_count;

这个传值的时候一般是一个，如果是多个就会报错

SELECT sal INTO @user_sal  FROM emp;

可以看出MYSQL中的自定义变量，无法对其赋值为一个集合。

局部变量

定义局部变量的吸引使用DECLARE语句定义一个局部变量，其作用域在BEGIN…END中有效。位置只能放在BEGIN…END中，而且只能放在第一句。

BEGIN
 # 声明局部变量
 DECLARE 变量名1 数据类型 [DEFAULT 变量默认值]
     #这个地方如果不设置DEFAULT，那么初始值为NULL
 
 
 # 如果定义多个局部变量
 DECLARE 变量名2，变量名3…… 数据类型  [DEFAULT 变量默认值]
 
 #局部变量赋值
 SET 变量名1=用户变量值;# 在局部变量中一般赋简单值
   
 SELECT 值 INTO 变量名2 [FROM 等语句]  #一般用户赋表中的字段值
 
 #查询局部变量的值
 SELECT 变量名1
END

现在演示一个例子

DELIMITER $
CREATE PROCEDURE test_1()
BEGIN
DECLARE a INT DEFAULT 2;
DECLARE b INT;
DECLARE c INT;

SELECT MIN(sal)  INTO c  FROM emp; # 这个地方也是不能有集合比如不适用MIN函数就会报错

SELECT a,b,c;

END $
DELIMITER ;
# 然后调用
CALL test_1();

如果局部变量中几个变量属性都一样可以这样设置

DELIMITER $
CREATE PROCEDURE test_2()
BEGIN
DECLARE a ,b INT DEFAULT 2;
SELECT a,b;

END $
DELIMITER ;
# 然后调用
CALL test_2();

# 如果只是两个类型一样，但是一个有默认值，要给没有 
#如下写
DELIMITER $
CREATE PROCEDURE test_3()
BEGIN
DECLARE a INT,b INT DEFAULT 2; #这样会报错
SELECT a,b;

END $
DELIMITER ;

这个可以看出报错，也不再演示，如果两个类型不一样，也不能在一行。

DELIMITER $
CREATE PROCEDURE test_4()
BEGIN
DECLARE a VARCHAR(2),b INT  ; #这样也报错
SELECT a,b;
END $
DELIMITER ;

演示

## 输入一个员工的id 然后得到员工和管理者的工资差 

DELIMITER $
CREATE PROCEDURE test_5(IN e_no INT ,OUT dif_sal DECIMAL(7,2)  )
BEGIN
DECLARE emp_sal,mgr_sal DECIMAL(7,2);
DECLARE mgr_id INT ;

#SELECT mgr INTO mgr_id,sal INTO emp_sal  FROM emp WHERE empno=e_no; # 这样写会报错，也是 因为INTO 后面不能有其他表的字段，哪怕再来一个INTO , 所以自动都写在前面
#SELECT mgr ,sal INTO emp_sal  FROM emp WHERE empno=e_no; # 这样写不会报错 

#SELECT sal INTO emp_sal,mgr_sal  FROM emp WHERE empno=e_no; # 这样写本身没有意义，现在演示一下into 可以给两个变量赋值，而且不会报错
SELECT sal INTO emp_sal  FROM emp WHERE empno=e_no; 
SELECT mgr INTO mgr_id  FROM emp WHERE empno=e_no; 

SELECT sal INTO mgr_sal  FROM emp WHERE empno=mgr_id; 

SET dif_sal=mgr_sal-emp_sal;
 
END $
DELIMITER ;

# 调用

CALL test_5(7900,@dif_sal)