【MySQL】存储过程、触发器、视图

存储过程

什么是存储过程

简单来说，存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合，它可以完成特定的功能，经过编译之后存储在数据库中，在需要时直接调用，就像脚本语⾔中函数定义⼀样。

大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成，例如为了处理订单，需要核对以保证库存中有相应的物品；如果库存有物品，需要预定以便不将它们再卖给别的人，并减少可用的物品数量以反映正确的库存量；如果库存中没有相应物品，则需要与供应商进行某种交互。

执行这个处理需要针对许多表的多条MySQL语句，可能需要执行的具体语句及其次序也不是固定的。那么怎样编写此代码？这时就可以创建存储过程，在每次需要这个处理时（以及每个需要它的应用中）都自动完成全部工作。

优点

1. 可以把一些复杂的SQL进行封装，简化复杂操作
2. 保证了数据的完整性，防止错误
3. 简单的变动只需要更改存储过程的代码即可
4. 提⾼性能，因为使用存储过程比使用单独的SQL语句要快（预先编译）

注意业务逻辑不要封装在存储过程中，应该由应用程序（JAVA、Python、PHP）处理

语法

定义存储过程

\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @i=@i+1;
end while;
end;
//
\d ;

说明：

• \d // 作用是将MySQL默认的语句结尾符 ; 改为 //
• create procedure 创建语句
• begin和end语句⽤来限定存储过程体
• \d ; 将语句结尾符改回;

执行存储过程

call p1();

查看存储过程

show create procedure p1\G;

删除存储过程

drop procedure p1;

触发器

什么是触发器

触发器是MySQL响应写操作（增、删、改）而自动执行的一条或一组定义在begin和end之间的MySQL语句；或可理解为：提前定义好一个或一组操作，在指定的SQL操作前或操作后来触发指定的SQL自动执行

例如每当增加⼀个员工到数据库表时，都检查其电话号码格式是否正确；每当订购一个产品时，都从库存数量中减去订购的数量；无论何时删除一行，都在某个存档表中保留一个副本…

语法

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_statement;

说明：

• trigger_name：触发器名称
• trigger_time：触发时间，可取值：BEFORE或AFTER
• trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE
• table_name：指定在哪个表上
• trigger_statement：触发时要执行的SQL语句

示例：
创建⼀个删除操作的触发器，在users表中删除数据之前，将该数据添加到del_users表中
注意在创建删除触发器时,只能在删除之前才能获取到old(之前的)数据

\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id, old.name, old.age, old.account);
end;
//
\d ;

查看所有的 触发器

show triggers\G;

删除触发器

drop trigger trigger_name;

注意：如果触发器中SQL有语法错误，那么整个操作都会报错

视图

什么是视图

视图是虚拟的表，与包含数据的表不⼀样，视图只包含使用时动态检索数据的查询

视图仅仅是用来查看存储在别处的数据的一种方法，视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索

如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害

视图的作用

1. 重用SQL语句
2. 简化复杂的SQL操作：在编写查询后，可以方便地重用它而不必知道它的查询细节
3. 使用表的组成部分而不是整个表
4. 保护数据：可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
5. 更改数据格式和表示：视图可返回与底层表的表示和格式不同的数据。

注意：视图不能索引，也不能有关联的触发器或默认值。

语法

创建视图：

create view v_users as select id, name, age from users where age >= 25 and age
<= 35;

查看当前库中所有的视图

show tables; #可以查看到所有的表和视图

show table status where comment='view'; #只查看当前库中的所有视图

删除视图

drop view v_t1;