MySQL高级教程笔记：编码，变量，存储过程，流程控制

这里记述一些零散，但是有效的知识点

# 在命令行的后面加上 \G 可以修改数据在命令行中的展现形式，比如
select * from table；
# 数据会以表格的形式返回
select * from tables \G;
# 数据则会以行的形式返回，适合当列很多，需要频繁换行才能展示时查看数据用
# 创建临时变量
set @$paramName=$value;
# 在 select 时，值为 null 时设置另一个值，比如当 $param为null时，则返回后面的 $value 否则返回原始值
select ifnull($param,$value);
# 在 select 时，将一些函数计算值赋值给一个变量
set @param=1;
select count(*) into @param from $tableName;

MySQL编码：charset

查看系统的字符相关信息

show variables like 'char%';

可以看到系统的一些默认设置值。这里建议统一设置成UTF-8的，因为对中文的支持情况好，如果系统在命令行中显示正常，但是到客户端，或者别人连接你的MySQL服务，查询出来的数据到他们服务器上显示异常，那么就很有可能是编码的问题，用上面的方式查看一下吧。

想要修改默认的值，可以通过修改配置文件的方式，具体的情况就按照具体的要求去百度吧。

会话变量&全局变量

查看一个会话变量

show session variables;
# 如果要查看单一变量，也可以直接用 like

更改会话变量

set $variableName = $newValue;

set @@session.$variableName = $newValue;

查看全局变量

show global variables;
# 跟上面一样，想查看单一值，可以使用 like

设置全局变量

set global $variableName = $newValue;

set @@global.$variableName = $newValue;

使用select操作符查看以上单一值

# 查看会话变量
select @@session.$variableName;
# 查看全局变量
select @@global.$variableName;

存储过程

存储过程优点

• 可以在其中编写复杂的查询，效率相比直接编写sql高
• 一次编写，多次调用
• 如果是复杂的操作，由于存储过程经过预编译，效率比起单次查询更高
• 存储过程能减少网络流量，因为在线上传输的数据更少
• 可以在其中设置权限相关操作，这里的权限的含义是并不需要将对应数据库的权限开放给特定的用户，而是将存储过程的执行权限开放给对应用户，这样实现权限上的限制

创建存储过程

# 首先选择一个数据库
use $dataBase;
# 改变分隔符，如下面设置成 $$
delimiter $$;
# 开始编写存储过程
create procedure $procedureName()
begin
# 定义局部变量 declare 局部变量名 数据类型，int char varchar等 如果要设置默认值 可以使用 default
declare $paramName $dataType (default $value);
# 修改局部变量的值
set $paramName = $value;
$sql1;
$sql2;
end
$$;
# 将分隔符修改回来
delimater ;
# 调用存储过程
call $procedureName;

存储过程中的参数

• in

在调用存储过程之前指定，而且在调用存储过程后不会修改其原始值

• out

在调用存储过程时传入，其原始值不会生效，在存储过程中修改其值，在存储过程结束后，该修改仍旧生效。

• inout

拥有以上两者特性，在传入时，该值的原始值生效，在存储过程中修改其值后，在结束存储过程后仍旧生效。

# 这里主要展示创建存储过程时设置参数的形式 参数类型(in out inout) 参数名 数据类型
create procedure $precedureName(in $param1 $dataType,out $param2 $dataType,inout $param3 $dataType)
begin
# 修改参数的方式与修改存储过程中的临时变量不同
set $param1=$value;
end;
# 展示调用存储过程时传入参数
set @param1=1;
set @param2=2;
set @param3=3;
call $precedure(@param1,@param2,@param3);
# 这里 @param2 @param3 在存储过程中的修改都将生效，而 @param1 则不会
select @param1,@param2,@param3;

定义条件和处理

简单来说很像编程中的异常处理，就是在存储过程中设置异常处理机制。因为在创建存储过程的时候，毕竟是预编译，真正执行的时候，可能会出现异常，为了出现异常时，另一部分代码也能正常执行，则需要抓捕该异常，告诉mysql你需要执行剩余sql。

语法规则

declare continue handler for sqlstate '$mysqlErrorCode' set $param=$value;

这里最后面的 set $param=$value可能跟你的业务要求没关系，但是这里就是这样的语法规则，而且在该存储过程执行完后，该变量仍旧生效。下面举个例子：

# 下面在存储过程中查询一张不存在的表 users 和一张存在的表 test
delimiter $$;
create procedure findData()
begin
# 设置下面的查询中出现 42S02 类型的错误（表不存在），不抛出异常，继续执行下面的 sql
declare continue handler for sqlstate '42S02' set @errorInfo='表数据源异常';
# 支持设置多个异常处理机制 
declare continue handler for sqlstate '23000' set @errorInfo='插入数据错误';
# 下面这条 insert 命令是错误的
insert into test values(1,3);
select * from users;
select * from test;
end $$;
delimiter ;
call findData();
# 这里查询一下其中设置的变量
select @errorInfo as '错误信息';

存储过程管理

查看一个数据库下所有的存储过程

show procedure status where db='$dataBaseName' \G;

数据库的所有信息都保存在mysql数据库中，包括存储过程，所以也可以通过下面的形式：

select * from mysql.proc \G;

也可以通过show ceate procdure $procedureName;查看一个存储过程的创建语句。

删除存储构成

drop procedure (if exists) $procedureName;

修改存储过程

直接说可以修改哪些内容吧，具体修改可以到时候直接百度。

• 存储过程中执行的内容，可以全部删掉，或者修改
• 该存储过程的执行权限，有两种，只有创建者可以执行，或者拥有调用存储过程的用户可以执行
• 备注内容

流程控制

if else 语句

语法规则

if $condition then $sql
elseif $condition then $sql
else $sql
end if;

其中 else和elseif都不是一定要写的，可以只有if。

在$condition中可以使用一些操作符，比如&&，||等实现多个条件，而且不需要用括号括起来。

这里拿存储过程来做例子：

# 一个简单的 demo
# 创建存储过程
delimiter $$;
create procedure showAge(int age int)
begin
# 在存储过程中设置判断条件
if age<18 then
select '未成年';
elseif age>=18 && age<60 then
select '成年人';
else
select '老年人';
end if;
end
$$;
delimiter ;
# 设置变量
set @age=18;
# 调用存储过程
call showAge(@age);

case分支语句

语法规则

case $param
when $value then $sql
when $value then $sql
else $sql
end case

同样可以在存储过程中使用，这里在这里记述是因为想要记录另一种用法，在select中使用

# 注意，前面都一样，只是这里不是 end case，而是 end
select (case gender when 'M' then '男人' when 'F' then '女' else '其他' end) as '性别' from users;

循环语句

while循环

语法结构

while $condition do
$sql
end while;

这个就很简单了吧，下面拿个小demo演示一下。

create procedure countDown() 
begin 
declare count int default 1; 
# 设置结束条件
while count<10 do select count as '计时器';
set count=count+1;
end while;
end $$;

repeat循环

语法规则

repeat
$sql
# 退出循环的条件，不是满足该条件时继续执行该循环，这一点上别弄混了
until $condition
end repeat;

照例来个demo

delimiter $$;
create procedure downCount()
begin
declare count int default 10;
# 开始循环
repeat
select count as '倒计时';
set count=count-1;
# 满足以下条件时，退出循环
until count=0
end repeat;
end
$$;
delimiter ;
call downCount;

loop循环

语法规则

$loopName:loop 
$sql
# 这里虽然是使用 if 的条件判断，但是也不一定，重点就是一定要设置一个离开条件
if $condition then
# 使用下面的形式结束对应的循环
leave $loopName;
end if;
end loop;

照例一个简单的demo

delimiter $$;
create procedure numCount()
begin
declare num int default 10;
findSix:loop
select num as '本次数据';
if num=6 then
leave findSix;
end if;
set num=num-1;
end loop;
end $$;
delimiter ;
call numCount();