MySQL学习笔记（六）

触发器与存储过程

触发器：

一种特殊的事务，监听增删改操作，并触发增删改操作，主要是用来处理一些比较复杂的业务逻辑以保证数据的联动性

• 四要素：

   ○ 监视地点（table）：同一个表不能拥有两个具有相同触发时刻和事件的触发器
   ○ 监视事件（insert/update/delete）
   ○ 触发时间（after/before）
   ○ 触发事件（insert/update/delete）
  

• 创建触发器

create trigger 触发器名称 触发时间 触发事件 on 表名 for each row begin 触发程序
end
（触发程序：可以使用OLD与NEW关键字，insert语句只有new合法，delete语句只有old合法，update语句可以与new或old同时使用）

• 当向表中插入新纪录时，触发程序中可以使用new关键字表示新纪录，当需要访问新纪录的某个字段值时，使用new.字段名访问
• 当从表中删除某条旧记录时，触发程序中可使用old关键字表示旧记录，当需要访问旧记录的某个字段值时，可使用old.字段名访问
• 当修改标的某条记录时，触发程序中可使用old关键字表示修改签的旧记录、使用new关键字表示修改后的新纪录；当需要访问旧记录的某个字段值时，可使用old.字段名访问；当需要访问新纪录的某个字段值时，使用new.字段名访问
• old记录是只读的，可以引用但不能更改。在before触发程序中，可使用“set new.column = value”更改new记录的值

查看触发器信息

1. show triggers;
2. select trigger_name,event_manipulation from information_schema.triggers where trigger_name like ‘tri_%_user’;
3. show create trigger 触发器名称；
4. 数据库目录下trn以及trg触发器文件以记事本方式打开查看信息 删除触发器 drop trigger 触发器名称;

使用trigger的注意事项

1. 触发程序中如果包含select语句，该select语句不能返回结果集
2. 同一个表不能创建两个相同触发时间、触发事件的触发程序
3. 触发程序中不能使用以显式或隐式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等
4. MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致更新操作性能降低
5. 在MyISAM存储引擎中，触发器不能保证原子性。InnoDB存储引擎支持事务，使用存储引擎虽然不支持外键约束关系，但可以使用触发器实现级联修改和级联删除，进而维护“外键”数据，模拟实现外键约束关系
6. InnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据；MyISAM存储引擎虽然不支持外键约束关系，但可以使用触发器实现级联修改和级联删除，进而维护“外键数据”，模拟实现外键约束关系
7. 使用触发器维护InnoDB外键约束的级联选项时，应该首先维护子表的数据，然后再维护父表的数据，否则可能会出现错误
8. MySQL的触发程序不能对本表进行更新语句（如update）。触发程序中的更新操作可以直接使用set命令替代，否则可能会出现错误信息，甚至陷入死循环
9. 在before触发程序中，auto_increment字段的new值为0，不是实际插入新记录时自动生成的自增型字段值
10. 添加触发器后，建议对其进行详细的测试，测试通过后再决定是否使用该触发器

临时表

• 按存储位置分为 内存临时表（in-memory）和外存临时表（on-disk）
• 按创建时机分为 自动创建的和手动创建的临时表

创建临时表（手动）
在create table 后加上temporary关键字
查看临时表定义
show create table 临时表名;
删除临时表
drop temporary table 临时表名;
（断开MySQL服务器连接，临时表结构定义文件和表记录将被清除）
使用临时表注意事项

1. 临时表若与基表重名，则基表会被隐藏，除非删除临时表，基表才能被访问
2. Memory、MyISAM、Merge或InnoDB存储引擎的表都支持临时表
3. 临时博爱不支持聚簇索引、触发器
4. show tables命令不会显示临时表信息
5. 不能用rename来重命名临时表，可使用alter table重命名
6. 在同一条select语句中，临时表只能引用一次

派生表（derived table）
遵守规则（1.必须有自己的别名2.所有字段必须有名称，字段名唯一）

……from（select语句）派生表名…

存储过程

优点：
在服务器端运行，执行速度快；执行一次后，其执行规划就驻留在高速缓冲存储器，后续操作只需从高速缓冲存储器中调用已编译好的二进制代码执行即可，提高了系统性能；确保了数据库的安全

创建存储过程

create procedure 过程名（[参数列表]） begin 过程体 end （参数列表形式 [in | out |
inout] 参数名 type）

• in代表输入参数，表示该参数的值必须由调用程序指定
• out代表输出参数，表示该参数的值经存储过程计算后将out参数的计算结果返回给调用程序
• inout代表即是输出参数，又是输入参数，表示该参数的值既可以由调用程序指定，也可以将inout参数的计算结果返回给调用程序

调用存储过程
call 过程名（）；
查看存储过程的定义
• show procedure status；
• select name from mysql.proc
• where db='' and type='procedure';
• show create procedure 存储过程名;
• 存储过程的信息都保存在information_schema数据库的routines表中，可使用select语句查询相关信息
修改存储过程和函数（只能修改权限）

alter {procedure | function} sp_name [characteristic…]
characteristic:{contains sql | no sql | reads sql data | modifies sql
data } | sql security{definer | invoker} | comment ‘string’

流程控制语句

1. if语句    

if 条件 then 语句块 [elseif 条件 then 语句块]… [else 语句块] end if

2. case语句  

○ case case_value
when when_value then 语句块
[when when_value then 语句块]…
[else 语句块]
end case
○ case
when when_value then 语句块
[when when_value then 语句块]…
[else 语句块]
end case

3. while语句

while 条件 do 语句块 end while

错误触发条件和错误处理
1. 自定义错误处理程序

delete 错误处理类型（continue|exit） handle for 错误触发条件 自定义错误处理程序

错误触发条件：MySQL错误代码、ANSI标准错误代码、自定义错误触发条件 自定义错误处理程序：错误发生后，MySQL会立即执行自定义错误处理程序中的MySQL语句

2. 自定义错误触发条件

delete 错误触发条件 condition for MySQL错误代码或ANSI标准错误代码