MySQL - 数据库中的系统变量和用户变量详解

原创

程序员小默 2022-06-11 00:56:21 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者程序员小默的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL中变量分为两种：系统变量和用户变量。

【1】系统变量

系统定义好的变量，大部分时候用户根本不需要使用系统变量。系统变量是用来控制服务器表现的。如：autocommit，auto_increment_increment等。

① 查看系统变量

--查看所有变量
show variables;

--查看范围变量
show variables like '%变量名%';

示例：

--查看mysql数据目录
show VARIABLES like 'datadir'

MySQL - 数据库中的系统变量和用户变量详解_系统变量

② 查看变量值

任何一个有数据返回的内容都是由select查看。

查看系统变量值

select @@变量名;

MySQL - 数据库中的系统变量和用户变量详解_mysql_02

同时查看多个变量值；

MySQL - 数据库中的系统变量和用户变量详解_赋值_03

③ 修改系统变量

修改系统变量有两种方式：会话级别和全局级别。

会话级别

临时修改，当前客户端当次连接有效，关闭将会恢复默认值。

有说法称为会话变量，属于系统变量子集，区别与用户变量！

set 变量名 = 变量值;

or

set @@变量名 = 变量值;

示例如下：

set autocommit = 0;

set @@ autocommit = 0;

查看会话变量如下：

MySQL - 数据库中的系统变量和用户变量详解_mysql_04

全局级别

一次修改，永久生效(对所有客户端都生效)。

set global 变量名 = 值;

示例如下：

set GLOBAL autocommit = 1;

当前客户端修改后需要关闭连接重新打开，即可看到生效。

如果对方(其他)客户端当前已经连接上服务器，那么当次修改无效，要退出重新登录才会生效。

④ 变量单位

不同的变量的单位不同，比如table_cache是指缓存的表的个数，而key_buffer_size则是以字节为单位，另外还有以页或者百分比为单位的变量。

许多变量可以通过后缀制订单位，比如1M表示一百万字节，在配置文件中或者在命令行下有效。但是在使用set命令的时候，这些单位就无效，必须使用数字，单位为字节。
比如：

set @@session.sort_buffer_size = 1024*1024
或者
set @@session.sort_buffer_size = 1048576

但是配置文件中设置的时候就不能使用表达式。

【2】自定义变量

用户在使用过程中自定义的变量，即用户变量。
系统为了区分系统变量，规定用户自定义变量必须使用一个@符号。
所有自定义变量都是会话级别。

语法格式如下 :

set @变量名;

默认值为null，可以赋值：

set @name = 'Jane';

查看自定义变量值

select @变量名;

如：
--select @name;

这里需要说明的是，在MySQL中，"="很多时候会被当做比较符号处理。为了区分比较和赋值的概念，MySQL指定了特殊的赋值符号：“:=”。

示例如下：

--赋值
set @name := 'Janu';

--查看
select @name;

除了上面直接赋值方式，MySQL还允许从数据表中获取数据，赋值给变量，有两种方式。

① 边赋值，边查看结果

select @变量名 := 字段名 from table where...;

示例如下 :

SELECT @name1 := name from p_user;

结果集有多个值，相当于多次赋值，后者覆盖前者。

MySQL - 数据库中的系统变量和用户变量详解_缓存_05

查看变量值：

SELECT @name1;

MySQL - 数据库中的系统变量和用户变量详解_赋值_06

② 先赋值，后查看

-- 赋值如下

select 字段名(或统计函数) into @变量名 from  table where[条件字句1..] ;

or

select 字段名(或统计函数) from  table where[条件字句1..] into @变量名;

-- 查看如下

select @变量名;

需要注意的是，这里结果集最多只能有一个数据，才能成功为变量赋值。

错误示例图如下：

MySQL - 数据库中的系统变量和用户变量详解_赋值_07

正确示例如下：

SELECT name into @name1 from p_user where id =1   ;

SELECT name  from p_user where id =2  into @name2 ;

SELECT @name1,@name2;

MySQL - 数据库中的系统变量和用户变量详解_mysql_08

或者如下：

SELECT name,age  into @name1 ,@age1 from p_user where id =1   ;

select @name1,@age1;

MySQL - 数据库中的系统变量和用户变量详解_赋值_09

综上，给用户变量赋值有两中方式：

① 方式一

set @变量 = 变量值[表达式];

or

set @变量 := 变量值[表达式];

② 经过查询或计算赋予值

select [column][function] into @变量名 from table_name where [..][..];

or

select [column][function]  from table_name where [..][..] into @变量名;

示例如下：

set @name1 [:]= (select name from p_user where id = 2);
select @name1;

MySQL - 数据库中的系统变量和用户变量详解_mysql_10

用户变量多种赋值方式：

CREATE TRIGGER `tr_puser` AFTER INSERT ON `p_user` FOR EACH ROW BEGIN

DECLARE p_v int(11) DEFAULT 0;
DECLARE p_v1 int(11) DEFAULT 0;

  select sum(age) into p_v from p_user ;--第一种
  select sum(age)  from p_user into p_v1;--第二种
  set @user_count = p_v;--第三种
  set @user_count1 := p_v1;--第四种
  set @user_count2 :=(select sum(age) from p_user);--第五种
  
  select new.age into @user_count3;
  
--其中第五种是一个变形。
end;