MySQL 从入门到实践，万字详解！（下）

15. 存储过程

前面介绍的大部分 SQL 语句都是对一个或者多个表的单个查询，但是实际情况下一个完整的操作可能是由多个语句组合而成的，比如考虑下面这个下单流程:

1. 为了处理订单，需要核对以保证库存中有相应的物品。
2. 如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
3. 库存中没有的物品需要订购，需要与供应商进行一些交互。
4. 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

可以说存储过程就是数据库 SQL 语言层面上的代码封装和重用，可以回传值，也可以接受参数。可以将其视为批文件，但作用不仅限于批处理。

存储过程简单、安全、高性能。不过有些数据库管理员会限制存储过程的创建权限，只允许用户使用，但不允许用户创建存储过程。

15.1 创建存储过程 create procedure

创建存储过程使用 create procedure，可以设置参数，存储过程体使用 begin ... end 分隔开，调用使用 call

# 创建一个计算平均价格的存储过程
create procedure product_pricing(vend_id int)
begin
    select avg(prod_price) as priceaverage
    from products
    where products.vend_id = vend_id;
end;
# 查看创建存储过程的语句
show create procedure product_pricing;
# 调用存储过程查询平均价格
call product_pricing(1002);
复制代码

这里的存储过程使用了参数，也可以不使用参数，和其他语言中的函数类似。

15.2 删除存储过程 drop procedure

删除使用 drop 关键字，如果不存在这个存储过程会报错，此时可以增加 if exists 关键字：

# 删除存储过程    
drop procedure product_pricing;
# 先检查再删除
drop procedure if exists product_pricing;
复制代码

15.3 使用参数

变量(variable) 内存中一个特定的位置，用来临时存储数据。

存储过程输入了 4 个参数，一个输入参数，还有三个用来存储的参数，每个参数用 in（传递给存储过程）、out（从存储过程传出）、inout（对存储过程传入和传出）指定参数。

MySQL 中的变量都必须以 @ 开始，存储过程中检索得到的值使用 into 保存到相应变量，之后可以就可以查询到变量中存储的值了。

# 存储过程输入输出参数
create procedure product_pricing(
    in vend_id int,
    out min_price decimal(8, 2),
    out max_price decimal(8, 2),
    out avg_price decimal(8, 2)
)
begin
    select min(prod_price)
    into min_price
    from products where products.vend_id = vend_id;
    select max(prod_price)
    into max_price
    from products where products.vend_id = vend_id;
    select avg(prod_price)
    into avg_price
    from products where products.vend_id = vend_id;
end;
# 调用存储过程查询产品平均价格
call product_pricing(1002, @minprice, @maxprice, @avgprice);
# 查询刚刚输出的变量
select @minprice, @maxprice, @avgprice;
复制代码

再试个例子，使用存储过程计算出指定订单号的总价，并输出到变量中：

# 计算指定订单号的总价格，并输出到变量中
create procedure order_pricing(
    in order_num int,
    out total_price decimal(8, 2)
)
begin
    select sum(quantity * item_price)
    into total_price
    from orderitems
    where orderitems.order_num = order_num;
end;
# 计算订单 20005 的总价
call order_pricing(20005, @totalprice);
# 查询总价
select @totalprice;
复制代码

15.4 使用条件语句

存储过程也可以使用 if (条件) then ... elseif (条件) then ... else 语句，比如现在要计算折扣后的商品价格，总商品数量 3 件 8 折，4 件 7 折，这里使用存储过程：

# 首先为了方便后面计算订单总金额，创建一个查询订单总金额的视图
create or replace view sum_order_price as
select sum(quantity * item_price) as total_price, order_num
from orderitems group by order_num;
# 总商品数量 3 件 8 折，4 件 7 折,计算折扣后的产品价格，
create procedure total_discount_price(
    in order_num int,
    out discount_price decimal(8, 2)
) 
begin
    # 创建一个变量保存商品总数
    declare prod_count int;
    # 计算该订单号的商品总件数
    select sum(quantity)
    into prod_count
    from orderitems where orderitems.order_num = order_num;
    # 小于 3 件无折扣
    if prod_count < 3 then
        select total_price
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    # 等于 3 件 8 折
    elseif prod_count = 3 then
        select total_price * 0.8
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    # 大于等于 3 件 7 折
    else
        select total_price * 0.7
        into discount_price
        from sum_order_price o
        where o.order_num = order_num;
    end if;
end;
# 调用存储过程查询折扣后的金额
call total_discount_price(20005, @discountprice);
select @discountprice;
复制代码

这个例子中我们使用了一个临时变量 prod_count，计算出该订单总件数之后将其赋到这个临时变量中，然后在之后的 if else 条件语句中对其进行判断，再通过视图计算出总金额，最后保存给输出变量。

16. 游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因。游标（cursor）是一个存储在 MySQL 服务器上的数据库查询，它不是一条 select 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

MySQL 中的游标只能用于存储过程或函数。

游标处理分为下面几个步骤：

1. 声明游标 declare：没有检索数据，只是定义要使用的 select 语句；
2. 打开游标 open：打开游标以供使用，用上一步定义的 select 语句把数据实际检索出来；
3. 检索游标 fetch：对于填有数据的游标，根据需要取出(检索)各行；
4. 关闭游标 close：在结束游标使用时，必须关闭游标，如果你不关闭游标，MySQL 将在到达 end 语句时关闭游标。

下面直接看例子：

drop procedure if exists process_orders;
# 使用游标将每个订单的实际价格填写到一个表中
create procedure process_orders()
begin
    # 定义本地变量
    declare o_num int;                    # 循环中的订单号变量
    declare d_price decimal(8, 2);        # 循环中的实际价格变量
    declare done boolean default false;   # 终止条件布尔值
    # 定义游标
    declare order_numbers cursor for
        select order_num from orders;
    # 终止条件，当没有更多行供循环时满足 not found，此时给 done 赋值 true
    declare continue handler for not found set done = true;
    # 没有则创建一个新的表，用来存订单的实际价格
    create table if not exists ordertotals
    (
        order_num   int,
        order_price decimal(8, 2),
        primary key (order_num)
    );
    open order_numbers;         # 打开游标
    # 在循环中依次给表 ordertotals 填充行
    repeat
        fetch order_numbers into o_num;
        call total_discount_price(o_num, d_price); 
        insert into ordertotals(order_num, order_price)
        values (o_num, d_price);
    until done end repeat;              # 满足 done 为真值则跳出循环
    close order_numbers;
end;
call process_orders();
select * from ordertotals;
复制代码

首先使用 declare 定义了几个局部变量，这几个变量用来存中间值，其中默认值为 false 的 done 是循环的终止条件，将在后面的 repeat 语句中用来作为判断是否继续循环的标志位，当 repeat 没有更多行供循环时满足 not found，此时给 done 赋值 true 终止循环。在循环体中使用上一章的存储过程给表 ordertotals 填充计算的订单实际价格。

declare 语句是有顺序的，局部变量需要在句柄之后定义，句柄必须在游标之后定义，否则会报错。

除了 repeat 循环外，MySQL 还支持 loop 循环、while 循环，基本大同小异，可以自己查询学习一下。

17. 触发器

如果你想要某些语句在事件发生时自动执行，可以考虑触发器。

1. 只可以响应 delete、insert、update 语句；
2. 只有表支持触发器，临时表和视图不支持；

17.1 创建触发器 create trigger

创建触发器使用 create trigger 关键字，格式如下：

# 语法
create trigger [触发器名]
[after/before] [insert/update/delete] on [表名]
for each row 
begin
 [sql语句]
end;
# 删除
drop trigger [if exists] [触发器名];
复制代码

for each row 表示对每个插入行执行触发器。只有表

# 创建一个触发器，在新的产品插入时给临时变量赋值
create trigger newproduct
    after insert
    on products
    for each row select 'Product added' into @newprod;
# 插入一条语句试试
insert into products(prod_id, vend_id, prod_name, prod_price, prod_desc)
values ('XP2000', 1005, 'JetPack 200', 55, 'JetPack 200, multi-use');
select @newprod;
复制代码

17.2 使用触发器

触发器要谨慎使用，由于触发器是针对每一行的，对增删改非常频繁的表上切记不要使用触发器，因为会非常消耗资源。

insert 触发器

1. insert 触发器内可以通过访问名为 new 的虚拟表访问被插入的行；
2. before insert 语句中可以通过更改 new 虚拟表中的值来修改插入行的数据；
3. 对于 auto_increment 自增列，在 before 中 new 中的值为 0，在 after 中为自动生成的自增值。

# 插入用户后获取这个新用户自动生成的的 ID 并且赋值给临时变量
create trigger newcust
    after insert
    on customers
    for each row select new.cust_id
                 into @newcust_after;
# 插入用户
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
values ('Zhang San', '001 street', 'ShangHai', 'SH', '666666', 'ZH', null, null);
# 查看新值
select @newcust_after;
复制代码

before 经常被用于数据验证。

delete 触发器

1. delete 触发器内可以通过访问名为 old 的虚拟表来访问被删除的行；
2. old 虚拟表中的字段都是只读的，不能修改。

drop trigger if exists deletecustomer;
# 创建触发器，当从顾客表中删除时将删除的数据插入到另一个存档表中
create trigger deletecustomer
    before delete on customers for each row
begin
    insert into customers2(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    values (old.cust_id, old.cust_name, old.cust_address, old.cust_city, old.cust_state, old.cust_zip, old.cust_country, old.cust_contact, old.cust_email);
end;
# 删除刚刚创建的顾客数据
delete from customers where cust_id = 10013;
# 查询一下存档表中的顾客数据是否存在
select * from customers2;
复制代码

这里使用 before 而不是 after 的原因是，如果因为某种原因顾客信息不能存档，delete 操作将会放弃，避免信息丢失。

update 触发器

1. update 触发器内可以通过访问名为 old 的虚拟表访问更新前的值，访问名为 new 的虚拟表来访问更新后的值；
2. before update 触发器中，new 中的值是可以被修改的；
3. old 虚拟表中的字段都是只读的，不能修改。

drop trigger if exists updatecustomer;
# 使用触发器，将每次更新的 cust_country 转化为大写
create trigger updatecustomer
    before update
    on customers
    for each row
begin
    set new.cust_country = upper(new.cust_country);
end;
# 更改数据
update customers set cust_country ='zh' where cust_id = 10005;
复制代码

18. 管理事务处理

事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。

举个例子，如果我们要转账给别人，首先把别人账户增加钱，再把我们账户上钱扣除，如果中间出现问题，那么麻烦就大了。

或者在当前数据库中，如果我们要添加一个订单信息，分为下面几步：

1. 检查数据库中是否存在相应的客户（从customers表查询），如果不存在则添加这个用户信息。
2. 检索顾客的 ID，cust_id。
3. 添加一行订单信息到 orders 表，把它与顾客 ID 关联。
4. 检索 orders 表中赋予的新订单 ID，order_id。
5. 对于订购的每个物品在 orderitems 表中添加一行，通过检索出来的 ID 把它与 orders 表关联，以及通过产品 ID 与 products 表关联。

如果发生了某种数据库故障（超出磁盘限制、安全限制、表锁等），阻止了一个完整的流程，会出现什么情况 。如果故障出现在 1 和 2 之间，这没什么关系，因为一个顾客没有订单信息是合法的，如果出现在 3 和  4 之间，那么就会出现一个空的订单，这个订单没有包含的产品信息，这很严重，如果出现在 5 时，添加 orderitems 过程中出现问题，那么可能出现订单信息不完整的情况，也很严重。

使用事务可以避免这个情况，如果中间发生了问题，那么则回退到某个安全的状态。

18.1 事务处理

那么使用事务如何处理这个过程呢：

1. 检查数据库中是否存在相应的顾客，如果不存在则添加这个用户信息；
2. 提交顾客信息；
3. 检索顾客的 ID；
4. 添加一行到 orders 表；
5. 如果在添加行到 orders 表时出现故障，回退；
6. 检索 orders 表中赋予的新订单 ID；
7. 对于订购的每项物品，添加新行到 orderitems 表；
8. 如果在添加新行到 orderitems 时出现故障，回退所有添加的 orderitems 行和 orders 行；
9. 提交订单信息。

这里有几个概念：

• 事务（transaction）指一组 SQL 语句；
• 回退（rollback）指撤销指定 SQL 语句的过程；
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）。

18.2 控制事务处理

使用回退 rollback

start transaction 标识事务开始，使用 rollback 可以进行回退从 start 到 rollback 中间的所有语句。

# 开始事务
start transaction; 
delete from customers where cust_id > 10005;   # 删除几个行
select * from customers;
# 使用 rollback 回滚 delete 语句
rollback;
复制代码

rollback 可以回退 insert、update、delete 语句，但不能回退 create、drop 语句，事务处理块中可以使用这两个语句，但 rollback 无效果。

使用提交 commit

MySQL 中用户的任何一个更新操作（写操作）都被视为一个事务，这就是所谓的隐含提交（implicit commit），相当于 MySQL 帮你在后台提交了。

可以针对每个连接使用 set autocommit=0 来设置 MySQL 不自动提交更改，设置之后，每个 SQL 语句或者语句块所在的事务都需要显式 commit 才能提交事务。

但在事务处理块中，提交不会隐含地进行，需要你自己来显式的调用：

# 删除订单详情表总中 20007 相关订单详情，再删除订单表中的 20007
start transaction;
delete from orderitems where order_num = 20007;
delete from orders where order_num = 20007;
commit;
复制代码

由于设计到订单和订单详情，所以使用事务来保证订单是完整的被删除，而不是部分删除。如果两个 delete 语句中发生了错误，那么 commit 将不会被执行。

在 commit 或 rollback 执行时，事务会被自动关闭。

使用保留点 savepoint

之前的 rollback、commit 只能对整个事务处理块整体提交或回滚，某些复杂场景下可能要部分回滚或者部分恢复，比如之前例子，如果订单信息增加失败，可能要回滚到添加用户信息后。

此时可以使用保留点，这样在发生问题时回滚到保留点处即可。保留点使用比较简单：

# 创建保留点
savepoint sav1;
# 回滚到保留点
rollback to sav1;
复制代码

保留点可以使用多一点，当在事务完成时，他们将会被自动释放，也可以使用 release savpoint 来手动释放。

19. 安全管理

19.1 访问控制

对数据库来说，用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

1. 多数用户只需要对表进行读和写，少数用户需要能创建和删除表；
2. 某些用户需要读表，但可能不需要更新表；
3. 你可能想允许用户添加数据，但不允许他们删除数据；
4. 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
5. 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
6. 你可能想根据用户登录的地点限制对某些功能的访问。

给不同的用户提供不同的访问权，这就是访问控制。

对于 root 登陆的使用需要十分谨慎小心，仅在绝对需要时才使用，不应在日常 MySQL 操作中使用 root 账户。

19.2 管理用户

用户信息存储在 MySQL 的 mysql 库中：

# 查看用户列表
use mysql;
select user from user;
复制代码

创建用户账号：

# 创建用户及其密码
create user zhangsan identified by '888888';
# 更改用户名
rename user zhangsan to lisi;
# 删除用户
drop user if exists lisi;
复制代码

设置权限用 grant 关键字：

# 显示用户张三的权限
show grants for zhangsan;
# 设置权限，允许张三在 mysql_demo1 库上使用 select
grant select on mysql_demo1.* to zhangsan;
# 撤销权限，撤销张三在 mysql_demo1 库上使用 select 的权限
revoke select on mysql_demo1.* from zhangsan;
复制代码

权限设置和用户设置还有很多内容，不是本文的重点，可以百度一下或者看文档。

20. 数据库维护

20.1 备份数据

数据库也是经常需要备份的，可以使用以下方法：

1. 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
2. 用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据，但并非所有数据库引擎都支持这个实用程序。
3. 可以使用 MySQL 的 backup table 或 select into outfile 转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用 restore table 来复原。

20.2 数据库维护

1. analyze table 用来检查键是否正确。
2. check table 用来针对许多问题对表进行检查。
3. 针对 MyISAM 表访问产生不正确和不一致的问题，可以使用  repair table 来修复。
4. 如果从一个表中删除大量数据，应该使用 optimize table 来收回所用空间，从而优化表的性能。

20.3 查看日志

错误日志 Error Log

记录 Mysql 运行过程中的 Error、Warning、Note 等信息，系统出错或者某条记录出问题可以查看错误日志。

通过 show variables like "log_error"; 来查看错误日志存放的位置。

日常日志 General Query Log

记录包括查询、修改、更新等的每条语句。

通过 show global variables like "%genera%"; 查看日常日志存放的地方，如果 general_log 是 off 则不能查询，可以通过 set global general_log=on; 打开查询，然后 tail -f /var/lib/mysql/VM-0-17-centos.log; 来查看。

二进制日志 Binary Log

包含一些事件，描述了数据库的改动，如建表、数据改动等，主要用于备份恢复、回滚操作等。

可以通过 show variables like "%log_bin%"; 来查看 Binlog 存在哪，会有多个文件，使用 show master logs; 可以看到查看所有 Binlog 日志列表，格式是 bingo.000008 这样，当 Binlog 日志写满或者数据库重启会产生新文件，使用 flush logs 可以手动产生新文件，Binlog 十分重要，产生问题要回滚用 Binlog 就可以了。

缓慢查询日志 Slow Query Log

记录执行缓慢的任何查询，在优化数据库时比较有用。

可以通过  show variables like "%slow%"; 来查看缓慢日志存放的地方。

21. 改善性能

性能是数据库永恒的追求，对于性能有以下 Tips：

1. 数据库对硬件是有一定要求的，在老旧主机上运行自然远不如专用服务器上。
2. MySQL 有很多配置，比如内存分配、缓存区大小等，熟练使用后通过调整配置可以获得更好的性能表现。查看配置可以使用 show variables; 和 show status; 查看配置
3. 实现同样功能的不同语句有不同的性能表现，可以找到性能更好的方法。
4. 尽量不检索不需要的数据，比如能 select param，就不要 select *。
5. 一般组合查询比在 select 中使用 or 快。
6. 一般 fulltext 比 like 快。

网上的帖子大多深浅不一，甚至有些前后矛盾，在下的文章都是学习过程中的总结，如果发现错误，欢迎留言指出，如果本文帮助到了你，别忘了点赞支持一下哦，你的点赞是我更新的最大动力！~

参考文档：

1. 《MySQL必知必会》
2. MySQL 中文文档