MySql中的变量

摩森特沃 2021年12月07日 809次浏览

声明：本文多处直接引用了文末的参考链接中的内容，如有侵权，请联系删除

MySQL中的变量

在mysql文档中，mysql变量可分为两大类，即系统变量和用户变量。

但根据实际应用又被细化为四种类型，即局部变量、用户变量、会话变量和全局变量。其中会话变量和全局变量都属于系统变量。

一、局部变量

mysql局部变量，只能用在begin/end语句块中，比如存储过程中的begin/end语句块。其作用域仅限于该语句块。

-- declare语句专门用于定义局部变量，可以使用default来说明默认值
declare age int default 0;
-- 如果声明的变量为字符串类型，需要明确指定字符串的长度
declare name varchar(32) default 0;

-- 局部变量的赋值方式一
set age=18;

-- 局部变量的赋值方式二
select StuAge 
into age
from demo.student 
where StuNo='A001';

二、用户变量

mysql用户变量，mysql中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。其作用域为当前连接。

-- 第一种用法，使用set时可以用“=”或“:=”两种赋值符号赋值
set @age=19;

set @age:=20;

-- 第二种用法，使用select时必须用“:=”赋值符号赋值
select @age:=22;

select @age:=StuAge 
from demo.student 
where StuNo='A001';

三、会话变量

mysql会话变量，服务器为每个连接的客户端维护一系列会话变量。会话变量的作用域与用户变量一样，仅限于当前连接，即每个连接中的会话变量是独立的。当当前连接断开后，其设置的所有会话变量均失效

-- 显示所有的会话变量
show session variables;

-- 设置会话变量的值的三种方式
set session auto_increment_increment=1;
set @@session.auto_increment_increment=2;
set auto_increment_increment=3;        -- 当省略session关键字时，默认缺省为session，即设置会话变量的值

-- 查询会话变量的值的三种方式
select @@auto_increment_increment;
select @@session.auto_increment_increment;
show session variables like '%auto_increment_increment%';        -- session关键字可省略

-- 关键字session也可用关键字local替代
set @@local.auto_increment_increment=1;
select @@local.auto_increment_increment;

四、全局变量

mysql全局变量，全局变量影响服务器整体操作，当服务启动时，它将所有全局变量初始化为默认值。要想更改全局变量，必须具有super权限。其作用域为server的整个生命周期。

-- 显示所有的全局变量
show global variables;

-- 设置全局变量的值的两种方式
set global sql_warnings=ON;        -- global不能省略
set @@global.sql_warnings=OFF;

-- 查询全局变量的值的两种方式
select @@global.sql_warnings;
show global variables like '%sql_warnings%';

五、会话变量与全局变量的特别说明

  • 会话变量与全局变量都属于系统变量，系统变量在变量名前面有两个@。
  • 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
  • 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
  • 如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那么会话变量和全局变量的值都是一样的
  • 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）

使用SHOW SESSION VARIABLES;命令可以将所有的会话变量输出，该命令可以简写为show variables;，没有指定是输出全局变量还是会话变量的话，默认就输出会话变量。

使用SHOW GLOBAL VARIABLES;命令可以将所有的全局变量输出。

有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的。对于那些可以更改的系统变量，我们可以利用set语句进行更改

系统变量的修改和查询

如果想要更改会话变量的值，利用语句：

-- 语法：
set session varname = value;
或者
set @@session.varname = value;

-- 示例：
set session sort_buffer_size = 40000;
-- 用select @@sort_buffer_size;输出看更改后的值是什么。
-- 如果想要更改全局变量的值，将session改成global：
set global sort_buffer_size = 40000;
-- 或者也可以使用以下语句
set @@global.sort_buffer_size = 40000;

如果要更改全局变量的值，需要拥有SUPER权限。

注意，ROOT只是一个内置的账号，而不是一种权限，这个账号拥有了MYSQL数据库里的所有权限。任何账号只要它拥有了名为SUPER的这个权限，就可以更改全局变量的值，正如任何用户只要拥有FILE权限就可以调用load_file或者into outfile, into dumpfile, load data infile一样。

利用select语句我们可以查询单个会话变量或者全局变量的值：

select @@session.sort_buffer_size

select @@global.sort_buffer_size

select @@global.tmpdir

凡是上面提到的session，都可以用local这个关键字来代替，local 是 session的近义词。比如：　　

select @@local.sort_buffer_size

local 是 session的近义词。

无论是在设置系统变量还是查询系统变量值的时候，只要没有指定到底是全局变量还是会话变量。都当做会话变量来处理。比如：

set @@sort_buffer_size = 50000;
select @@sort_buffer_size;

上面都没有指定是GLOBAL还是SESSION，所以全部当做SESSION处理。

变量的赋值

局部变量的赋值

  • 使用declare在声明变量时赋值(用在存储过程或函数中)
语法：DECLARE var_name [, var_name]... data_type [ DEFAULT value ];
示例：DECLARE c int DEFAULT 0;
  • 使用set的方式赋值(变量需要先使用declare进行声明)
语法：SET var_name=expr [, var_name=expr]...;
示例：
DECLARE c int DEFAULT 0;
SET c=a+b;
SELECT c AS C;
  • 使用select方式赋值(变量需要先使用declare进行声明)
语法：select into 语句句式：SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];
示例：
DECLARE v_employee_name VARCHAR(100);
DECLARE v_employee_salary DECIMAL(8,4);

SELECT employee_name, employee_salary
INTO v_employee_name, v_employee_salary
FROM employees
WHERE employee_id=1;

用户变量的赋值

  • 使用set的方式赋值
语法：set @num=1; 或set @num:=1;
示例：
set @a = 1;
  • 使用select方式赋值
语法：select @num:=1; 或 select @num:=字段名 from 表名 where ……;
注意：使用select赋值时必须使用':='的写法
示例：
select @name:=password from user limit 0,1;
# 从数据表中获取一条记录password字段的值给@name变量
# 如果这个查询返回多个值的话，那@name变量的值就是最后一条记录的值
# 例如：select @name:=password from user中@name将被赋值为password字段的最后一个记录的值

变量使用

在存储过程中使用

MySQL存储过程中，定义变量有两种方式：

  1. 使用set或select直接赋值，变量名以 @ 开头。例如:set @var=1;，可以在一个会话的任何地方声明，作用域是整个会话，这种使用方式是用户变量的使用方式。
  2. 以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：DECLARE var1 INT DEFAULT 0;，这种方式主要用在存储过程中，或者是给存储传参数中。

两者的区别

  • 在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量

引用参考

上一篇： MySQL中的特别用法