玩转Mysql系列 - 第16篇:变量详解

Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。

这是Mysql系列第16篇。

环境:mysql5.7.25,cmd命令中进行演示。

代码中被[]包含的表示可选,|符号分开的表示可选其一。

我们在使用mysql的过程中,变量也会经常用到,比如查询系统的配置,可以通过查看系统变量来了解,当我们需要修改系统的一些配置的时候,也可以通过修改系统变量的值来进行。

我们需要做一些批处理脚本的时候,可以使用自定义变量,来做到数据的复用。所以变量这块也挺重要,希望大家能够掌握。

本文内容

  • 详解系统变量的使用

  • 详解自定义变量的使用

变量分类

  • 系统变量

  • 自定义变量

系统变量

概念

系统变量由系统定义的,不是用户定义的,属于mysql服务器层面的。

系统变量分类

  • 全局变量

  • 会话变量

使用步骤

查看系统变量
  1. //1.查看系统所有变量
  2. show [global | session] variables;
  3. //查看全局变量
  4. show global variables;
  5. //查看会话变量
  6. show session variables;
  7. show variables;

上面使用了show关键字

查看满足条件的系统变量

通过like模糊匹配指定的变量

  1. //查看满足条件的系统变量(like模糊匹配)
  2. show [global|session] like '%变量名%';

上面使用了show和like关键字。

查看指定的系统变量
  1. //查看指定的系统变量的值
  2. select @@[global.|session.]系统变量名称;

注意select@@关键字,global和session后面有个.符号。

赋值
  1. //方式1
  2. set [global|session] 系统变量名=值;
  3. //方式2
  4. set @@[global.|session.]系统变量名=值;

注意:

上面使用中介绍的,全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认为session级别。

全局变量的使用中用到了@@关键字,后面会介绍自定义变量,自定义变量中使用了一个@符号,这点需要和全局变量区分一下。

全局变量

作用域

mysql服务器每次启动都会为所有的系统变量设置初始值。

我们为系统变量赋值,针对所有会话(连接)有效,可以跨连接,但不能跨重启,重启之后,mysql服务器会再次为所有系统变量赋初始值。

示例

查看所有全局变量
  1. /*查看所有全局变量*/
  2. show global variables;
查看包含'tx'字符的变量
  1. /*查看包含`tx`字符的变量*/
  2. mysql> show global variables like '%tx%';
  3. +---------------+-----------------+
  4. Variable_name | Value           |
  5. +---------------+-----------------+
  6. | tx_isolation  | REPEATABLE-READ |
  7. | tx_read_only  | OFF             |
  8. +---------------+-----------------+
  9. 2 rows in set, 1 warning (0.00 sec)
  10. /*查看指定名称的系统变量的值,如查看事务默认自动提交设置*/
  11. mysql> select @@global.autocommit;
  12. +---------------------+
  13. | @@global.autocommit |
  14. +---------------------+
  15. |                   0 |
  16. +---------------------+
  17. 1 row in set (0.00 sec)
为某个变量赋值
  1. /*为某个系统变量赋值*/
  2. set global autocommit=0;
  3. set @@global.autocommit=1;
  1. mysql> set global autocommit=0;
  2. Query OK0 rows affected (0.00 sec)
  3. mysql> select @@global.autocommit;
  4. +---------------------+
  5. | @@global.autocommit |
  6. +---------------------+
  7. |                   0 |
  8. +---------------------+
  9. 1 row in set (0.00 sec)
  10. mysql> set @@global.autocommit=1;
  11. Query OK0 rows affected (0.00 sec)
  12. mysql> select @@global.autocommit;
  13. +---------------------+
  14. | @@global.autocommit |
  15. +---------------------+
  16. |                   1 |
  17. +---------------------+
  18. 1 row in set (0.00 sec)

会话变量

作用域

针对当前会话(连接)有效,不能跨连接。

会话变量是在连接创建时由mysql自动给当前会话设置的变量。

示例

查看所有会话变量
  1. /*①查看所有会话变量*/
  2. show session variables;
查看满足条件的会话变量
  1. /*②查看满足条件的步伐会话变量*/
  2. /*查看包含`char`字符变量名的会话变量*/
  3. show session variables like '%char%';
查看指定的会话变量的值
  1. /*③查看指定的会话变量的值*/
  2. /*查看事务默认自动提交的设置*/
  3. select @@autocommit;
  4. select @@session.autocommit;
  5. /*查看事务隔离级别*/
  6. select @@tx_isolation;
  7. select @@session.tx_isolation;
为某个会话变量赋值
  1. /*④为某个会话变量赋值*/
  2. set @@session.tx_isolation='read-uncommitted';
  3. set @@tx_isolation='read-committed';
  4. set session tx_isolation='read-committed';
  5. set tx_isolation='read-committed';

效果:

  1. mysql> select @@tx_isolation;
  2. +----------------+
  3. | @@tx_isolation |
  4. +----------------+
  5. READ-COMMITTED |
  6. +----------------+
  7. 1 row in set, 1 warning (0.00 sec)
  8. mysql> set tx_isolation='read-committed';
  9. Query OK0 rows affected, 1 warning (0.00 sec)
  10. mysql> select @@tx_isolation;
  11. +----------------+
  12. | @@tx_isolation |
  13. +----------------+
  14. READ-COMMITTED |
  15. +----------------+
  16. 1 row in set, 1 warning (0.00 sec)

自定义变量

概念

变量由用户自定义的,而不是系统提供的。

使用

  1. 使用步骤:
  2. 1. 声明
  3. 2. 赋值
  4. 3. 使用(查看、比较、运算)

分类

  • 用户变量

  • 局部变量

用户变量

作用域

针对当前会话(连接)有效,作用域同会话变量。

用户变量可以在任何地方使用也就是既可以在begin end里面使用,也可以在他外面使用。

使用

声明并初始化(要求声明时必须初始化)
  1. /*方式1*/
  2. set @变量名=值;
  3. /*方式2*/
  4. set @变量名:=值;
  5. /*方式3*/
  6. select @变量名:=值;

注意:

上面使用了@符合,而上面介绍全局变量使用了2个@符号,这点注意区分一下。

set中=号前面冒号是可选的,select方式=前面必须有冒号

赋值(更新变量的值)
  1. /*方式1:这块和变量的声明一样*/
  2. set @变量名=值;
  3. set @变量名:=值;
  4. select @变量名:=值;
  5. /*方式2*/
  6. select 字段 into @变量名 from 表;

注意上面select的两种方式。

使用
select @变量名;

综合示例

  1. /*set方式创建变量并初始化*/
  2. set @username='路人甲java';
  3. /*select into方式创建变量*/
  4. select 'javacode2018' into @gzh;
  5. select count(*) into @empcount from employees;
  6. /*select :=方式创建变量*/
  7. select @first_name:='路人甲Java',@email:='javacode2018@163.com';
  8. /*使用变量*/
  9. insert into employees (first_name,email) values (@first_name,@email);

局部变量

作用域

declare用于定义局部变量变量,在存储过程和函数中通过declare定义变量在begin…end中,且在语句之前。并且可以通过重复定义多个变量

declare变量的作用范围同编程里面类似,在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了,不能使用了。这个同编程一样。

使用

声明
  1. declare 变量名 变量类型;
  2. declare 变量名 变量类型 [default 默认值];
赋值
  1. /*方式1*/
  2. set 局部变量名=值;
  3. set 局部变量名:=值;
  4. select 局部变量名:=值;
  5. /*方式2*/
  6. select 字段 into 局部变量名 from 表;

注意:局部变量前面没有@符号

使用(查看变量的值)
select 局部变量名;

示例

  1. /*创建表test1*/
  2. drop table IF EXISTS test1;
  3. create table test1(a int PRIMARY KEY,b int);
  4. /*声明脚本的结束符为$$*/
  5. DELIMITER $$
  6. DROP PROCEDURE IF EXISTS proc1;
  7. CREATE PROCEDURE proc1()
  8. BEGIN
  9.   /*声明了一个局部变量*/
  10.   DECLARE v_a int;
  11.   select ifnull(max(a),0)+1 into v_a from test1;
  12.   select @v_b:=v_a*2;
  13.   insert into test1(a,b) select v_a,@v_b;
  14. end $$
  15. /*声明脚本的结束符为;*/
  16. DELIMITER ;
  17. /*调用存储过程*/
  18. call proc1();
  19. /*查看结果*/
  20. select * from test1;

代码中使用到了存储过程,关于存储过程的详解下章节介绍。

delimiter关键字

我们写sql的时候,mysql怎么判断sql是否已经结束了,可以去执行了?

需要一个结束符,当mysql看到这个结束符的时候,表示可以执行前面的语句了,mysql默认以分号为结束符。

当我们创建存储过程或者自定义函数的时候,写了很大一片sql,里面包含了很多分号,整个创建语句是一个整体,需要一起执行,此时我们就不可用用分号作为结束符了。

那么我们可以通过delimiter关键字来自定义结束符。

用法:

delimiter 分隔符
上面示例的效果
  1. mysql> /*创建表test1*/
  2. mysql> drop table IF EXISTS test1;
  3. Query OK0 rows affected (0.01 sec)
  4. mysql> create table test1(a int PRIMARY KEY,b int);
  5. Query OK0 rows affected (0.01 sec)
  6. mysql>
  7. mysql> /*声明脚本的结束符为$$*/
  8. mysql> DELIMITER $$
  9. mysql> DROP PROCEDURE IF EXISTS proc1;
  10.     -> CREATE PROCEDURE proc1()
  11.     -> BEGIN
  12.     ->   /*声明了一个局部变量*/
  13.     ->   DECLARE v_a int;
  14.     ->
  15.     ->   select ifnull(max(a),0)+1 into v_a from test1;
  16.     ->   select @v_b:=v_a*2;
  17.     ->   insert into test1(a,b) select v_a,@v_b;
  18.     -> end $$
  19. Query OK0 rows affected (0.00 sec)
  20. Query OK0 rows affected (0.00 sec)
  21. mysql>
  22. mysql> /*声明脚本的结束符为;*/
  23. mysql> DELIMITER ;
  24. mysql>
  25. mysql> /*调用存储过程*/
  26. mysql> call proc1();
  27. +-------------+
  28. | @v_b:=v_a*2 |
  29. +-------------+
  30. |           2 |
  31. +-------------+
  32. 1 row in set (0.00 sec)
  33. Query OK1 row affected (0.01 sec)
  34. mysql> /*查看结果*/
  35. mysql> select * from test1;
  36. +---+------+
  37. | a | b    |
  38. +---+------+
  39. 1 |    2 |
  40. +---+------+
  41. 1 row in set (0.00 sec)

用户变量和局部变量对比


作用域定义位置语法
用户变量当前会话会话的任何地方@符号,不用指定类型
局部变量定义他的begin end之间begin end中的第一句话不加@符号,要指定类型

总结

  • 本文对系统变量和自定义变量的使用做了详细的说明,知识点比较细,可以多看几遍,加深理解

  • 系统变量可以设置系统的一些配置信息,数据库重启之后会被还原

  • 会话变量可以设置当前会话的一些配置信息,对当前会话起效

  • declare创建的局部变量常用于存储过程和函数的创建中

  • 作用域:全局变量对整个系统有效、会话变量作用于当前会话、用户变量作用于当前会话、局部变量作用于begin end之间

  • 注意全局变量中用到了`@@`,用户变量变量用到了`@`,而局部变量没有这个符号

  • `delimiter`关键字用来声明脚本的结束符

Mysql系列目录

  1. 第1篇:mysql基础知识

  2. 第2篇:详解mysql数据类型(重点)

  3. 第3篇:管理员必备技能(必须掌握)

  4. 第4篇:DDL常见操作

  5. 第5篇:DML操作汇总(insert,update,delete)

  6. 第6篇:select查询基础篇

  7. 第7篇:玩转select条件查询,避免采坑

  8. 第8篇:详解排序和分页(order by & limit)

  9. 第9篇:分组查询详解(group by & having)

  10. 第10篇:常用的几十个函数详解

  11. 第11篇:深入了解连接查询及原理

  12. 第12篇:子查询

  13. 第13篇:细说NULL导致的神坑,让人防不胜防

  14. 第14篇:详解事务

  15. 第15篇:详解视图

mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!

路人甲java

640?wx_fmt=png

▲长按图片识别二维码关注

路人甲java:工作10年的前阿里P7分享Java、算法、数据库方面的技术干货!坚信用技术改变命运,让家人过上更体面的生活。

来源:https://itsoku.blog.csdn.net/article/details/101878181
posted @ 2022-04-23 22:20  程序员小明1024  阅读(235)  评论(0编辑  收藏  举报