第62讲：MySQL存储过程中变量的定义及应用

文章目录

在MySQL数据库种分为三种类型的变量，分别是系统变量、用户自定义的变量、局部变量。

1.系统变量

系统变量是由MySQL数据库提供的系统变量，属于服务层面的系统变量，系统变量在使用时的级别又分为两种：

• 全局变量（global）：全局变量针对所有的会话生效，但是当重启MySQL后，修改的系统变量值将会失效，若想永久修改变量的值，可以在MySQL的配置文件中声明。
• 局部变量（session）：会话变量只针对与单个会话生效，到另一个会话窗口将失效。

系统变量的两种使用级别可以在使用变量时指定使用哪一个级别，如果不明确指定系统变量的级别，默认是session。

1.1.查看系统变量的语法格式

1）查看所有的系统变量

SHOW[SESSION|GLOBAL] VARIABLES 

2）查找某一个系统变量

SHOW[SESSION|GLOBAL] VARIABLES LIKE'变量名'

3）查看指定变量的值

SELECT @@[SESSION|GLOBAL].变量名

1.2.设置系统变量值的语法格式

两种方式可以设置变量的值。

SET[SESSION|GLOBAL] 系统变量名 = 值 

SET @@[SESSION|GLOBAL].系统变量名 = 值 

1.3.系统变量使用案例

1）查看所有的系统变量

mysql>show variables;

2）查看名称包含auto的系统变量

左侧是变量名，右侧是变量值。

mysql>show variables like'%auto%';+----------------------------------------------+-------+| Variable_name                                |Value|+----------------------------------------------+-------+| auto_generate_certs                          |ON|| auto_increment_increment                     |1|| auto_increment_offset                        |1|| autocommit                                   |ON|| automatic_sp_privileges                      |ON|| caching_sha2_password_auto_generate_rsa_keys |ON|| innodb_autoextend_increment                  |64|| innodb_autoinc_lock_mode                     |2|| innodb_stats_auto_recalc                     |ON|| sha256_password_auto_generate_rsa_keys       |ON|| sql_auto_is_null                             |OFF|+----------------------------------------------+-------+11rowsinset(0.00 sec)

3）查看指定变量的变量值

mysql>select @@session.autocommit;+----------------------+| @@session.autocommit|+----------------------+|1|+----------------------+1rowinset(0.00 sec)

4）修改系统变量的值

mysql>set @@session.autocommit=0;
Query OK,0rows affected (0.02 sec)

或者

mysql>setsession autocommit =0;
Query OK,0rows affected (0.00 sec)

2.用户自定义的变量

在MySQL数据库中，还支持用户根据自己的需要，自定义的环境变量，用户自定义的变量不需要提前声明，直接赋值使用即可，用户自定义的变量仅应用于当前的会话连接。

用户自定义的变量是不需要声明或者初始化的，如果没有赋值直接使用自定义的变量名，变量值则为NULL。

2.1.用户自定义变量的语法格式

1）定义变量并赋值

方式一，使用SET直接定义变量并赋值。

SET @变量名1= 变量值1SET @变量名1= 变量值1, @变量名2= 变量值2SET @变量名1 := 变量值1SET @变量名1 := 变量值1, @变量名2 := 变量值2 

建议使用:=的方式为变量赋值，因为在变量运算时=符号也是运算符号。

方式二，由于用户自定义的变量不需要声明，可以使用SELECT直接定义变量和赋值。

SELECT @变量名1 := 变量值1, @变量名2 := 变量值2

方式三，将SQL的查询结果赋值给变量。

SELECT 字段名 INTO @变量名 FROM 表名

将某个表的某个字段的结果赋值给变量。

2）使用变量

SELECT @变量名1

2.2.用户自定义变量的案例

1）使用set定义变量并赋值

1.一次性定义一个变量
mysql>set@name='jiangxl';
或者
mysql>set@username :='wang';2.一次性定义多个变量
mysql>set@username='wang',@userid='1';
或者
mysql>set@age :='19',@book :='linux';

2）使用select定义变量并赋值

使用select定义完变量赋完值之后就可以看到变量的值。

1.一次性定义一个变量
mysql>select@myzhiwu :='devops_linux';+----------------------------+|@myzhiwu :='devops_linux'|+----------------------------+| devops_linux               |+----------------------------+2.一次性定义多个变量
mysql>select@myzhiwu :='devops_linux',@myage :='25';+----------------------------+----------------+|@myzhiwu :='devops_linux'|@myage :='25'|+----------------------------+----------------+| devops_linux               |25|+----------------------------+----------------+

3）将ryxxb的总数赋值给变量

mysql>selectcount(*)into@mycountfrom ryxxb;

4）查看变量的值

查询各个定义的变量所对应的值。

mysql>select@name,@username,@userid,@age,@book,@myzhiwu,@myage;+---------+-----------+---------+------+-------+--------------+--------+|@name|@username|@userid|@age|@book|@myzhiwu|@myage|+---------+-----------+---------+------+-------+--------------+--------+| jiangxl | wang      |1|19| linux | devops_linux |25|+---------+-----------+---------+------+-------+--------------+--------+

3.可以在存储过程中定义的局部变量

局部变量是在存储过程中声明的，仅应用于存储过程的BEGIN…END这个范围中，局部变量需要通过DECLARE关键字声明，声明完成后再通过SET或者SELECT进行赋值，局部变量和用户自定义变量的赋值方式是一模一样的。

3.1.局部变量的语法格式

1）声明变量

DECLARE 变量名 变量值的类型 [DEFAULT 默认值]

变量值的类型就是数据库字段的类型，例如：INTBIGINTCHARVARCHARDATETIME等等。

2）变量赋值

SET 变量名 = 变量值
SET 变量名 := 变量值
SELECT 字段名 INTO 变量名 FROM 表名;

3.2.局部变量的应用案例

创建一个存储过程，在里面定义两个局部变量，分别用SET和SELECT赋值，最后查看变量的内容。

1.创建视图
createprocedure proc_2()begindeclare ryxxb_count intdefault0;#声明ryxxb表总数的变量，类型为int，默认值为0declare username varchar(10);#声明username变量，类型为varcharset username :='jiangxl';#赋值username变量selectcount(*)into ryxxb_count from ryxxb;#将ryxxb表的总数据量赋值为ryxxb_count变量select ryxxb_count,username;#查询变量的值end;2.调用视图
mysql>call proc_2();+-------------+----------+| ryxxb_count | username |+-------------+----------+|22| jiangxl  |+-------------+----------+1rowinset(0.00 sec)