MySQL 视图，触发器，存储过程

1. 视图

1.1 概念

1. 视图是 MySQL 在 5.0.1 版本中加入的功能。它可以理解为一个虚表。
2. 之所以被称为虚表，是因为它只是存储了一个结构，并不存储真实的数据。它的数据是在查询过程中动态生成的。
3. 视图并不是真的优化

1.2 创建、查看

-- 创建视图
create view `user_view` as select `id`, `name`, `age`, `sex` from `user`;
-- 查看视图结构
desc `user_view`;
-- 查看创建的视图语法
show create view `user_view`;
-- 查询视图内容
select * from `user_view`;

注意：视图查询的数据实则来自与源数据中的内容，而它本质是一个存储了一个结构，并不是存储真实的数据。

1.3 优点

1. 简化操作, 我们不需要关心视图中的操作, 只需要得到的结果集, 视图相当于是一个中间层。
2. 安全性更高, 我们可以让用户有权去访问某个视图, 而不是去访问原表, 这样可以对原表中的数据起到一个保护的作用
3. 降低耦合, 假如我们要修改原表的数据结构, 那我们可以通过修改对应的视图的定义, 而不需改动原表结构, 一般来说, 这样付出的代价更小。

1.4 视图IUD

表是可以更新数据的，这里的更新，指的是”增删改”,但是对于视图来说 不一定。

-- 修改视图数据
update `user_view` set `email` = 00 where `id` = 12;
-- 查询视图数据
select * from `user_view`;
-- 删除视图
drop view `user_view`;

以下是视图不可更新的情况:

1. 包含聚合函数、distinct、group by、having、union、union all。
2. 常量视图。
3. select 包含子查询。
4. 包含连接操作。
5. from 一个不能更新的视图。
6. where 子句的子查询引用了 from 子句中的表。

只允许修改满足视图条件的数据:

1. 在创建视图时加入with check option,

create view `user_view` as select `id`, `name`, `age`, `sex` from `user` with check option;

1.5 应用&好处

1. 提高了重用性，就像一个函数, 只需通过通过查询视图即可, 而不需要使用复杂的语句进行查询
2. 对数据库重构, 却不影响程序的运行, 同过修改视图无需调整程序, 程序只需查询视图。

2. 触发器

2.1 创建触发器

create trigger [触发器名称] [触发执行的时间点] [触发动作] on [触发操作的表名] for each row [函数 或 动作]

触发器的执行时间 :before, after
触发动作:insert, update, delete
函数:begin end;
或者动作:update ,insert

注意: 触发器类似于框架事件, 因此实际开发中我们可以使用框架功能来代触发器, 方便后期维护~。

3. 存储过程

3.1 理解

存储过程类似于编程语言中的函数方法, 可以理解为一段SQL语句的集合, 存储过程中进行流程控制, 因此我们可以在存储过程中编写多条SQL业务逻辑进行保存。

3.2 创建

-- 参数类型: in(只用来输入)、out(只用来输出)、inout(可输入也可输出)
create procedure 存储过程名(参数列表)
begin
    存储过程体
end

call 存储过程名(参数列表)

例子:

-- 创建存储过程
delimiter $$
create procedure user_procedure_out(in user_id int, out user_name varchar(10))
BEGIN
 select `name` into user_name from `user` where `id` = user_id;
END
$$

-- 调用存储过程
call user_procedure_out(1, @user_name);
select @user_name

3.3 优点

1. 效率高, 处理速度快, 存储过程中的代码是预编译好的, 不需要通过优化器的进行优化编译即可运行。
2. 可以将编写业务逻辑保存在存储过程中。
3. 可以减少网络IO消耗, 传存储过程调用比传输大量SQL语句开销小得多。
4. 安全性较高, 不会暴露表字段, 业务过程的细节, 可以使用权限控 制，而且参数化的存储过程可以有效地防止 SQL 注入攻击。保证了其安全性。

3.4 缺点

1. 移植性差, 存储过程和数据库是绑定的, 当更换数据库时会有很多地方需要修改。
2. 优势不明显和赘余功能, 对于小型 web 应用来说， 如果我们使用语句缓存，发现编译 SQL 的开销并不大，但是使用存 储过程却需要检查权限一类的开销，这些赘余功能也会在一定程度 上拖累性能。
3. 修改调试不方便。我们难以对存储过程进行调试，对它bug发现可能会晚些，增加了应 用的危险性。