二十二、MySQL基础系列笔记之变量
什么MySQL变量
MySQL本质是一种编程语言，变量用来存储数据。
与所有语言一样，变量是用来存储数据的。我们平常所写的SQL语句，实际上是在SQL编程。
MySQL两种变量
MySQL变量分为系统变量与用户自定义变量。系统变量一看就明白，系统已经定义好的变量；用户自定义变量就是用户自己的定义的变量。
系统变量
系统变量是由系统内部定义的变量，对所有用户有效。
系统变量又分为全局变量和会话变量。
全局变量
作用域：全局变量针对所有会话（连接）有效，不能重启。
查看全局变量语法
SHOW GLOBAL VARIABLES [LIKE] 变量名;
案例
查看所有全局变量
SHOW GLOBAL VARIABLES;
查看部分全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
查看指定全局变量的值
SELECT @@GLOBAL.autocommit;
为指定的全局变量赋值
SET @@global.autocommit=0;
会话变量
作用域：针对当前会话（连接）有效。
查看会话变量语法
SHOW SESSION VARIABLES [LIKE] 变量名;
查看所有会话变量
SHOW SESSION VARIABLES;
查看部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
查看指定会话变量的值
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一 （推荐）</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@@session.autocommit</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@@autocommit</span>;</span>
为指定的会话变量赋值
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">session</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">autocommit</span>=<span style="box-sizing: border-box;color: rgb(17, 102, 68)">1</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@@autocommit</span>=<span style="box-sizing: border-box;color: rgb(17, 102, 68)">0</span>;</span>
用户自定义变量
用户自定义变量分为全局变量与局部变量。全局变量在任何地方可以使用；局部变量只能在定义它的begin end中有效。
用户变量
自定义变量语法
声明并初始化变量
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> @用户变量名=值;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> @用户变量名:=值;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式三</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> @用户变量名 := 值；</span>
变量赋值：SET或SELECT
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式一</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> @用户变量名=值;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> @用户变量名:=值;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式三</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> @用户变量名:=值；</span>
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 也可以通过SELECT INTO方式</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> 字段名或表达式 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">INTO</span> @变量名 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> 表名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 案例</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">select</span> id+<span style="box-sizing: border-box;color: rgb(17, 102, 68)">10</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">into</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@uuid</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">from</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">user</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">where</span> id = <span style="box-sizing: border-box;color: rgb(17, 102, 68)">8</span>;</span>
查看变量
SELECT @用户变量名;
案例
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 声明变量并赋值</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@age</span> := <span style="box-sizing: border-box;color: rgb(17, 102, 68)">19</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 更新变量值 </span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@age</span> := <span style="box-sizing: border-box;color: rgb(17, 102, 68)">20</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 查看变量值</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@age</span>;</span>
局部变量
局部变量声明语法
DECLATE 变量名 类型 [DEFAULT 值];
类型：MySQL支持的数据类型，如varchar。
局部变量赋值
<span style="box-sizing: border-box;padding-right: 0.1px">SET 变量名=值；</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">SET 变量名:=值;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">SELECT 变量名=值;</span>
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SELECT</span> 字段名或表达式 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">INTO</span> @变量名 <span style="box-sizing: border-box;color: rgb(119, 0, 136)">FROM</span> 表名;</span>
使用
SELECT 变量名;
案例
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 声明一个用户变量（实际上就是全局变量）</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">SET</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@uname</span> = <span style="box-sizing: border-box;color: rgb(34, 162, 201)">'王五'</span>;</span><br></br> <br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 创建一个存储过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">DELIMITER</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">CREATE</span> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">PROCEDURE</span> proc2()</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">BEGIN</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> <span style="box-sizing: border-box;color: rgb(119, 0, 136)">select</span> <span style="box-sizing: border-box;color: rgb(0, 85, 170)">@uname</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">end</span> $$</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 调用存储过程</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(119, 0, 136)">call</span> proc2(); <span style="box-sizing: border-box;color: rgb(170, 85, 0)"># 结果输出 “王五"</span></span>
用户变量与局部变量对比
我是小白，期待和优秀的你一起同行!
小白
2020年11月07日
请登录后再评论