mysql的触发器可以调用存储过程吗

本章节主要来讲解的是MySQL中的视图、存储过程、存储函数和触发器，这些都是MySQL数据库中的存储对象,学好能助你涨薪[偷笑]。

一、视图

视图(View)是一种虚拟存在的表，视图中的数据并不在数据库中真实存在，我们所有看到视图当中的行和列的数据都是来自于我们在定义视图之后所使用的这张表，并且是在使用视图时动态生成的。通俗来讲，视图只保存了查询的SQL逻辑，不保存查询结果，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图的创建语法:

create [or ｒｅｐｌａｃｅ] view 视图名[(列名)] as ｓｅｌｅｃｔ 语句 [with[ cascaded | local ] check option ]

create or ｒｅｐｌａｃｅ view sts_v_1 as ｓｅｌｅｃｔ id,name from student where id=5; #or ｒｅｐｌａｃｅ可以不加，但是修改必须加。

这条语句的意思是创建名为sts_v_1的视图，用来封装后面ｓｅｌｅｃｔ语句查询返回的数据。

mysql> ｓｅｌｅｃｔ * from sts_v_1;+----+-----------+| id | name |+----+-----------+| 5 | 韦一笑 |+----+-----------+1 row in set (0.00 sec)

视图的查询操作:

show create view 视图名;

ｓｅｌｅｃｔ * from 视图名;

视图的修改操作:

create [or ｒｅｐｌａｃｅ] view 视图名[(列名)] as ｓｅｌｅｃｔ 语句 [with[ cascaded | local ] check option ]

mysql> create or ｒｅｐｌａｃｅ view sts_v_1 as ｓｅｌｅｃｔ id,name,s_no from student where id=5;

Query OK, 0 rows affected (0.03 sec)

alter view 视图名[(列名)] as ｓｅｌｅｃｔ 语句 [with[ cascaded | local ] check option ]

mysql> alter view sts_v_1 as ｓｅｌｅｃｔ id,name from student where id=5;

Query OK, 0 rows affected (0.01 sec)

mysql> ｓｅｌｅｃｔ * from sts_v_1;+----+-----------+| id | name |+----+-----------+| 5 | 韦一笑 |+----+-----------+1 row in set (0.00 sec)

视图的删除操作:

ｄｒｏｐ view 视图名 [,视图名称] 。。。

mysql> ｄｒｏｐ view sts_v_1;

Query OK, 0 rows affected (0.02 sec)

我们重新创建个视图，并往视图插入数据

mysql> create view sts_v_1 as ｓｅｌｅｃｔ id,name,s_no from student where id ｉｎｓｅｒｔ into sts_v_1 values(9,‘宋江‘,‘21113112‘),(10,‘刘浪‘,‘20225313‘),(11,‘章六‘,‘20215393‘);

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

我们往视图里插入的数据没有找到，是因为视图并不存储数据它只是个逻辑表，数据都存放在它的基表student中。还有我们创建的视图加了条件 id ｉｎｓｅｒｔ into sts_v_1 values(13,‘李栋‘,20202987);Query OK, 1 row affected (0.04 sec)mysql> ｉｎｓｅｒｔ into sts_v_1 values(14,‘陈.栋‘,20202087);ERROR 1369 (HY000): CHECK OPTION failed ‘test.sts_v_1‘

当通过with check option子句创建视图时，MySQL会通过视图检查正在更改的每一行，例如插入、更新、删除，以使其符合视图的定义，MySQL允许基于另一视图创建视图，它会检查依赖视图中的规则以保持数据一致性，为了确定检查的范围，mysql提供了两个选项:

cascaded和local，默认使用cascaded。

1、当我们创建视图时不加cascaded(级联)或local时,对视图的增删改mysql是不会去检查条件的

create view v_1 as ｓｅｌｅｃｔ id,name from student where id create or ｒｅｐｌａｃｅ view v_1 as ｓｅｌｅｃｔ id,name from student where id ｉｎｓｅｒｔ into v_1 values(15,‘暂定‘);Query OK, 1 row affected (0.05 sec)mysql> ｉｎｓｅｒｔ into v_1 values(25,‘什么鬼‘);Query OK, 1 row affected (0.01 sec)# 插入不满足条件的值，视图查不出来mysql> ｓｅｌｅｃｔ * from v_1;+----+-----------+| id | name |+----+-----------+| 1 | 张无忌 || 2 | 哑婆婆 || 3 | 张三丰 || 4 | 成昆 || 5 | 韦一笑 || 6 | 刘邦 || 7 | 刘策 || 8 | 浏览 || 9 | 宋江 || 10 | 刘浪 || 11 | 章六 || 12 | 李广 || 13 | 李栋 || 15 | 暂定 |+----+-----------+14 rows in set (0.01 sec)#mysql> ｓｅｌｅｃｔ * from student;+----+-----------+----------+| id | name | s_no |+----+-----------+----------+| 1 | 张无忌 | 20210112 || 2 | 哑婆婆 | 20210311 || 3 | 张三丰 | 20200112 || 4 | 成昆 | 20210223 || 5 | 韦一笑 | 20201230 || 6 | 刘邦 | 20110112 || 7 | 刘策 | 20220303 || 8 | 浏览 | 20212323 || 9 | 宋江 | 21113112 || 10 | 刘浪 | 20225313 || 11 | 章六 | 20215393 || 12 | 李广 | 20140987 || 13 | 李栋 | 20202987 || 15 | 暂定 | NULL || 25 | 什么鬼 | NULL |+----+-----------+----------+15 rows in set (0.00 sec)

2、如果创建视图v_2并关联了v_1 且加了cascaded，那么对视图的操作必须满足v_1和v_2 所定义的条件。

create view v_2 as ｓｅｌｅｃｔ id,name from v_1 where id >=20 with cascaded check option;

mysql> ｉｎｓｅｒｔ into v_2 values(19,‘落凤坡‘); # 不满足id>=20, 但满足v_1同样不能插入

ERROR 1369 (HY000): CHECK OPTION failed ‘test.v_2‘

mysql> ｉｎｓｅｒｔ into v_2 values(20,‘落凤坡‘); # 满足v_1,v_2

Query OK, 1 row affected (0.03 sec)

mysql>

CASCADED级联方式:

local同样会递归去检查依赖条件。

视图的检查选项主要指的是当我们对视图当中的数据进行增删改操作时,检查选项with cascaded/local check option会去检查我们所操作的数据是否符合视图定义时的条件。

对于视图的更新，视图中的行与基表中的行之间必须存在一对一的关系，如果视图包含以下任何一项，则该视图不可更新。比如:1、聚合函数(sum()、min()、max()、count()等)；2、distinct、group by、having、union或者union all这些关键字。

mysql> create view v_3 as ｓｅｌｅｃｔ count(*) from student;

Query OK, 0 rows affected (0.02 sec)

mysql> ｉｎｓｅｒｔ into v_3 values(16);

ERROR 1471 (HY000): The target table v_3 of the ｉｎｓｅｒｔ is not insertable-into

使用了聚合函数后视图和基表已经不是一一对应的了，所以不能插入也不能更新。

那么视图到底有什么作用呢，首先视图是不是操作很简单，它不仅可以简化用户对数据的理解，也可以简化它们的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次都指定全部条件。其次是安全，数据库是可以授权的对吧，但是不能授权到数据库特定行和特点列上，通过视图用户只能查询和修改它们所能见到的数据。最后，视图可以帮助用户屏蔽真实表结构变化带来的影响，比如给字段取别名。

实际生产中为保证数据的安全，比如对开发人员在操作king_user表时，只能看到表的基本字段，屏蔽掉邮箱地址和手机号。

create view king_v as ｓｅｌｅｃｔ id,name,profession,age,gender,status,createtime from king_user; 当开发要查询这张表的数据的时候，让他查king_v视图即可。

对于多表联查，特别适合适用视图，简化那一长串的SQL语句。

二、存储过程

存储过程就是事先经过编译并存储在数据库中的一段SQL语句的集合，通过调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的网络传输，对于提高数据处理的效率是很有帮助的。说的简单点就是SQL语句的封装和重用。

存储过程的特点有: sql语句的封装和复用，其次是可以接收参数，也可以返回数据，减少网络交互，提升效率。

存储过程的操作语法:

create procedure 存储过程名称([参数列表])

begin

。。。SQL语句

end;

调用存储过程： call 名称([参数])

命令行创建存储过程需要通过ｄｅｌｉｍｉｔｅｒ指定结束符,mysql是以分号结束的。

ｄｅｌｉｍｉｔｅｒ // # 指定什么字符结束

create procedure p()

begin

ｓｅｌｅｃｔ count(*) from king_user;

end//

mysql> ｄｅｌｉｍｉｔｅｒ @@mysql> create procedure pk()-> begin-> ｓｅｌｅｃｔ count(*) from king_user;-> end@@Query OK, 0 rows affected (0.00 sec)mysql># 改回来 ，调用存储过程 mysql> ｄｅｌｉｍｉｔｅｒ ;mysql> call pk();+----------+| count(*) |+----------+|11 |+----------+1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)

存储过程的查看和删除操作

ｓｅｌｅｃｔ * FROM information_schema.ROUTINES where ROUTINE_SCHEMA=‘test‘

show create procedure pk;

ｄｒｏｐ procedure pk();

存储过程的系统变量和用户自定义变量。

系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面的，分为全局变量global和会话变量session。语法如下:

1、查看系统变量

show [session|global] variables; # 查看索引系统变量

show [session|global] variables like ‘...‘; # 模糊匹配查找系统变量

ｓｅｌｅｃｔ @@[session|global] 系统变量名; # 查看指定变量的值

2、设置系统变量

set [session|global] 系统变量名=值;

set @@[session|global] 系统变量名=值;

提示: 如果没有指定session或者global，默认是session,mysql服务重启后所有设置的全局参 数会失效，要想不失效必须在全局设置的同时把设置内容添加到/etc/my.cnf中。

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接使用"@变量名"使用就可以，其作用域为当前的连接。

赋值:

set @var_name = expr;

set @var_name := expr; # 推荐使用:=

ｓｅｌｅｃｔ @var_name := expr;

ｓｅｌｅｃｔ 字段名 into @var_name from 表名;

使用方法: ｓｅｌｅｃｔ @var_name; 即可

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为null。

3、局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要ｄｅｃｌａｒｅ声明，可用 作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin...end块。

声明: ｄｅｃｌａｒｅ 变量名 变量类型 [default ...]; 其中变量类型就是数据库字段类型

赋值: set 变量名=值; set 变量名 := 值; ｓｅｌｅｃｔ 字段名 into 变量名 from 表名 ...;

# 在存储过程中 声明一个stu_count局部变量，然后赋值及展示create PROCEDURE pk2()BEGINDECLARE stu_count int default 0;ｓｅｌｅｃｔ count(*) into stu_count from king_user;ｓｅｌｅｃｔ stu_count;END;CALL pk2();

if 条件判断的语法

if 条件1 then。。。elseif 条件2 then。。。else。。。end if;# 判断 小明分数 例子create PROCEDURE pk3()BEGINDECLARE score int default 58;ｄｅｃｌａｒｅ result VARCHAR(10);if score >= 85 THENset result := ‘优秀‘;elseif score >= 60 THENset result := ‘及格‘;ELSEset result := ‘不及格‘;end if;ｓｅｌｅｃｔ result;end;# 结果为不及格；定义了score 默认值为58，此处代码不灵活call pk3();

存储过程的参数:

in： 该参数作为输入，也就是需要调用时传入。

out: 该类参数作为输出，也就是该参数可以作为返回值。

inout: 既可以作为输入参数,也可以作为输出参数。

用法:

create procedure 存储过程名([in/out/inout 参数名 参数类型])

begin

SQL语句

end;

# 使用传参的方式create PROCEDURE pk4(in score int,out result varchar(10))BEGIN#ｄｅｃｌａｒｅ score int default 58;#ｄｅｃｌａｒｅ result VARCHAR(10);if score >= 85 THENset result := ‘优秀‘;elseif score >= 60 THENset result := ‘及格‘;ELSEset result := ‘不及格‘;end if;ｓｅｌｅｃｔ result;end;# 传参测试CALL pk4(80, @result);ｓｅｌｅｃｔ @result;

存储过程中的case|while 语句

case   case_valuewhen when_value1  then 语句1when when_value2 then 语句2else 其它语句end case;语法2:case when search_condition1 then 语句1when search_condition2 then 语句2else 其它语句end case;

案例: 使用存储过程实现，根据传入的月份，判定月份所属的季节(case结构实现)

1、1~3 月为第一季度

2、4~6月为第二季度

3、7~9月为第三季度

4、10~12月为第四季度

create PROCEDURE pk5(in month int)BEGINDECLARE result VARCHAR(10);CASEWHEN month >=1 and month =4 and month =7 and month =10 and month 0 DOset total := total + n;set n := n-1;end WHILE;ｓｅｌｅｃｔ total;end;  call pk6(10)

三、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的具体语法如下:

create function 存储函数名([参数列表])

returns type [characteristic ...]

begin

--SQL语句

return ...;

end;

characteristic:

deterministic: 相同的输入参数总是产生相同的结果

no sql: 不包含SQL语句

reads sql data: 包含读取数据的语句，但不包含写入数据的语句。

# 通过存储函数 完成从1累加到n的值，n为传入的参数值

create FUNCTION fun1(n int)RETURNS int DETERMINISTICBEGINDECLARE total int DEFAULT 0;while n>0 DOset total := total + n;set n := n - 1;end while;RETURN total;end;ｓｅｌｅｃｔ fun1(100)

四、触发器

触发器是与表结构有关的数据库对象，指在ｉｎｓｅｒｔ/ｕｐｄａｔｅ/ｄｅｌｅｔｅ之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这些特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其它的数据库是相似的，现在触发器还只支持执行级触发，不支持语句级触发。

ｉｎｓｅｒｔ型的触发器，new表示将要或者已经新增的数据

ｕｐｄａｔｅ型的触发器，OLD表示修改之前的数据，NEW表示将要或已经修改后的数据。

ｄｅｌｅｔｅ型触发器，OLD表示将要或者已经删除的数据。

触发器语法:

# 创建create TRIGGER 触发器名before/after ｉｎｓｅｒｔ/ｕｐｄａｔｅ/DELETEON 表名 for each row ... -- 行级触发器BEGINtrigger_stmt;end;# 查看SHOW TRIGGER;# 删除ｄｒｏｐ TRIGGER [schema_name] 触发器名; -- 如果没有指定，schema_name，默认为当前数据库

创建一张日志表来监控 king_user的增删改操作，并将更新日志记录到user_logs表中。

create table user_logs(id int(11) not null auto_increment,operation varchar(20) not null comment ‘操作类型 ｉｎｓｅｒｔ/ｕｐｄａｔｅ/ｄｅｌｅｔｅ‘,operation_time datetime not null COMMENT ‘操作时间‘,operation_id int(11) not null COMMENT ‘操作的id‘,operation_params VARCHAR(500) COMMENT ‘操作参数‘,primary key(id))engine=innodb default charset=utf8;-- 插入数据时的触发器create trigger tb_king_user_insert_triggerAFTER ｉｎｓｅｒｔ on king_user for each ROWBEGINinsert into user_logs VALUES(null,‘ｉｎｓｅｒｔ‘,now(),new.id,ｃｏｎｃａｔ(‘插入的数字内容为: id=‘,new.id,‘,name=‘,new.name,‘,phone=‘,new.phone,‘,email=‘,new.email,‘,profession=,‘,new.profession));END;#插入数据测试mysql> ｉｎｓｅｒｔ into king_user values(12,‘卢俊义‘,38,‘男‘,‘13298987381‘,‘lujunyi@king.com‘,‘二当家‘,‘2‘,‘2021-03-23 12:10:23‘);Query OK, 1 row affected (0.04 sec)mysql> ｓｅｌｅｃｔ * from user_logs\G;*************************** 1. row ***************************id: 1operation: insertoperation_time: 2022-04-16 21:26:07operation_id: 12operation_params: 插入的数字内容为: id=12,name=卢俊义,phone=13298987381,email=lujunyi@king.com,profession=,二当家1 row in set (0.01 sec)ERROR: No query specifiedmysql>

-- 修改数据触发器

create trigger tb_king_user_update_triggerAFTER ｕｐｄａｔｅ on king_user for each ROWBEGINinsert into user_logs VALUES(null,‘ｕｐｄａｔｅ‘,now(),new.id,ｃｏｎｃａｔ(‘更新前的数居内容为: id=‘,old.id,‘,name=‘,old.name,‘,phone=‘,old.phone,‘,email=‘,old.email,‘,profession=,‘,old.profession)),(null,‘ｕｐｄａｔｅ‘,now(),new.id,ｃｏｎｃａｔ(‘更新后的数居内容为: id=‘,new.id,‘,name=‘,new.name,‘,phone=‘,new.phone,‘,email=‘,new.email,‘,profession=,‘,new.profession));END;

ｕｐｄａｔｅ king_user set age=32 where id=1; # 验证是否记录到日志表里

-- 删除数据触发器

create trigger tb_king_user_delete_triggerAFTER ｄｅｌｅｔｅ on king_user for each ROWBEGINinsert into user_logs VALUES(null,‘ｄｅｌｅｔｅ‘,now(),old.id,ｃｏｎｃａｔ(‘删除前的数居内容为: id=‘,old.id,‘,name=‘,old.name,‘,phone=‘,old.phone,‘,email=‘,old.email,‘,profession=,‘,old.profession));END;

mysql> ｄｅｌｅｔｅ from king_user where id=13;

Query OK, 1 row affected (0.11 sec)

你学费了吗