一、触发器

1.触发器的定义和类型

定义
- 触发器的对象是表，当表上出现特定的事件时触发该程序的执行
触发器的类型
- UPDATE：update操作
- DELETE
  - delete 操作
  - replace 操作
    - 注意：drop，truncate等DDL操作不会触发 DELETE
- INSERT
  - insert 操作
  - load data 操作
  - replace 操作

注意：

replace 操作会触发两次，一次是 UPDATE 类型的触发器，一次是 INSERT 类型的触发器

MySQL 5.6版本同一个类型的触发器只能有一个( 针对一个表 )

MySQL 5.7允许多个同一类型的触发器

触发器只触发DML(Data Manipulation Language)操作，不会触发DDL(Data Definition Language)操作（ create,drop 等操作）

2.触发器的创建

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name -- 触发器名字
    trigger_time trigger_event -- 触发时间和事件
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

其中： trigger_time: { BEFORE | AFTER } -- 事件之前还是之后触发 trigger_event: { INSERT | UPDATE | DELETE } -- 三个类型 trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

mysql> create table test_trigger_1 (
    -> name varchar(10),
    -> score int(10),
    -> primary key (name));
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter // -- 将语句分隔符定义成 // （原来是';'）
mysql> create trigger trg_upd_score -- 定义触发器名字
    -> before update on test_trigger_1 -- 作用在test_trigger_1 更新(update)之前(before)
    -> for each row -- 每行
    -> begin -- 开始定义
    -> if new.score < 0 then -- 如果新值小于0
    -> set new.score=0; -- 则设置成0
    -> elseif new.score > 100 then -- 如果新值大于100
    -> set new.score = 100; -- 则设置成100
    -> end if; -- begin对应的 结束
    -> end;// -- 结束，使用新定义的 '//' 结尾
Query OK, 0 rows affected (0.01 sec)

mysql>  delimiter ;  -- 恢复 ';' 结束符

注意：
-- new.col : 表示更新以后的值
-- old.col : 表示更新以前的值(只读)

--插入新值
mysql> insert into test_trigger_1 values ("sure", 1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_trigger_1;
+------+-------+
| name | score |
+------+-------+
| sure |  1000 |  -- 没改成100，因为定义的是update，而执行的是insert
+------+-------+
1 row in set (0.00 sec)

--通过触发器的设置，大于100的值被修改成100
mysql> update test_trigger_1 set score=500 where name ='sure';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_trigger_1;
+------+-------+
| name | score |
+------+-------+
| sure |   100 |
+------+-------+
1 row in set (0.00 sec)

3.触发器总结

触发器对性能有损耗，应当非常慎重使用；
对于事务表，触发器执行失败则整个语句回滚
Row格式主从复制，触发器不会在从库上执行
- 因为从库复制的肯定是主库已经提交的数据，既然已经提交了说明触发器已经被触发辟了，所以从库不会执行。
- 使用触发器时应防止递归执行；
```
  delimiter //
  create trigger trg_test
  before update on 'test_trigger'
  for each row
  begin
  update test_trigger set score=20 where name = old.name; -- 又触发了update操作，循环触发了
  end;//
```

4.用触发器模拟物化视图

物化视图：

不是基于基表的虚表
根据基表实际存在的实表
预先计算并保存耗时较多的SQL操作结果（如多表迹接(join)或者group by等）

物化视图的更新方式有很多种：

从不更新（只在开始更新，只用于静态数据）
根据需要（比如每天，比如每夜）
即时（每次数据修改之后）

一般使用的更新方法：

全部更新（速度慢，完全从无到有）
延时的（速度快，使用log表）
通过在日志表中存储变更信息，通常会产生简单的“快照”或者延时状况：
- 及时更新
- 完全更新

示例：

--创建order表
mysql> create table Orders
    -> (order_id int unsigned not null auto_increment,
    ->  product_name varchar(30) not null,
    ->  price decimal(8,2) not null,
    ->  amount smallint not null,
    ->  primary key(order_id));
Query OK, 0 rows affected (0.06 sec)

--插入记录
mysql> insert into Orders values
    -> (null, 'cpu', 135.5 ,1),
    -> (null, 'memory', 48.2, 3),
    -> (null, 'cpu', 125.6, 3),
    -> (null, 'cpu', 105.3, 4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from Orders;
+----------+--------------+--------+--------+
| order_id | product_name | price  | amount |
+----------+--------------+--------+--------+
|        1 | cpu          | 135.50 |      1 |
|        2 | memory       |  48.20 |      3 |
|        3 | cpu          | 125.60 |      3 |
|        4 | cpu          | 105.30 |      4 |
+----------+--------------+--------+--------+
4 rows in set (0.00 sec)

--创建模拟的mv
mysql> create table Orders_MV
    -> ( product_name varchar(30) not null,
    -> price_sum decimal(8,2) not null,
    -> amount_sum int not null,
    -> price_avg float not null,
    -> orders_cnt int not null,
    -> unique index (product_name));
Query OK, 0 rows affected (0.02 sec)

--将mv进行初始化记录
mysql> insert into Orders_MV
    -> select product_name, sum(price),
    -> sum(amount), avg(price), count(*)
    -> from Orders group by product_name;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from Orders_MV;
+--------------+-----------+------------+-----------+------------+
| product_name | price_sum | amount_sum | price_avg | orders_cnt |
+--------------+-----------+------------+-----------+------------+
| cpu          |    366.40 |          8 |   122.133 |          3 |
| memory       |     48.20 |          3 |      48.2 |          1 |
+--------------+-----------+------------+-----------+------------+
2 rows in set (0.00 sec)

--建立触发器
mysql> delimiter //
mysql> CREATE TRIGGER tgr_Orders_insert -- 创建触发器为tgr_Orders_insert
    -> AFTER INSERT ON Orders -- 触发器是INSERT类型的，且作用于Orders表
    -> FOR EACH ROW
    -> BEGIN
    -> SET @old_price_sum := 0; -- 设置临时存放Orders_MV表(模拟物化视图)的字段的变量
    -> SET @old_amount_sum := 0;
    -> SET @old_price_avg := 0;
    -> SET @old_orders_cnt := 0;
    -> SELECT -- select ... into ... 在更新Orders_MV之前，将Orders_MV中对应某个产品的信息写入临时变量
    -> IFNULL(price_sum, 0),
    -> IFNULL(amount_sum, 0),
    -> IFNULL(price_avg, 0),
    -> IFNULL(orders_cnt, 0)
    -> FROM
    -> Orders_MV
    -> WHERE
    -> product_name = NEW.product_name INTO @old_price_sum , @old_amount_sum , @old_price_avg , @old_orders_cnt;
    -> SET @new_price_sum = @old_price_sum + NEW.price; -- 累加新的值
    -> SET @new_amount_sum = @old_amount_sum + NEW.amount;
    -> SET @new_orders_cnt = @old_orders_cnt + 1;
    -> SET @new_price_avg = @new_price_sum / @new_orders_cnt ;
    -> REPLACE INTO Orders_MV
    -> VALUES(NEW.product_name, @new_price_sum,
    -> @new_amount_sum, @new_price_avg, @new_orders_cnt );
    -> -- REPLACE 将对应的物品（唯一索引）的字段值替换new_xxx的值
    -> END;//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

--插入新的记录
mysql> insert into Orders values (null, 'ssd', 299, 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Orders values (null, 'memory', 47.9, 5);
Query OK, 1 row affected (0.01 sec)

--查询order_mv，发现相应的值也做了变化
mysql> select * from Orders_MV;
+--------------+-----------+------------+-----------+------------+
| product_name | price_sum | amount_sum | price_avg | orders_cnt |
+--------------+-----------+------------+-----------+------------+
| cpu          |    366.40 |          8 |   122.133 |          3 |
| memory       |     96.10 |          8 |     48.05 |          2 |
| ssd          |    299.00 |          3 |       299 |          1 |
+--------------+-----------+------------+-----------+------------+
3 rows in set (0.00 sec)

二、存储过程

1.存储过程介绍

存储在数据库端的一组SQL语句逸；
用户可以通过存储过程名和传参多次调用的程序模块；
存储辟程的特点：
- 使用灵活，可以使用流控语句、自定义变量等完成复杂的业务逻辑；
- 提高数据安全性，屏蔽应用程序直接对表的操作，易于进行审计；
- 减少网络传输；
- 提高代码维护的复杂度，实际使用需要结合业务评估；

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter: -- 注意，只有procedure才有in(传入),out(传出),inout(传入传出)参数，自定义函数（只有）默认就是 in。
    [ IN | OUT | INOUT ] param_name type

characteristic:
        COMMENT 'string'
    | LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

2.存储过程中的流程控制

if 语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

示例创建存储过程

mysql>  delimiter //
mysql> create procedure pcd_test_1 (in param_a int) -- 创建一个
    -> begin
    -> declare a int; -- delcare声明了该变量的作用域在该procedure中
    -> if param_a > 10 then set a:=11;
    -> elseif param_a = 10 then set a:=10;
    -> else set a:=9;
    -> end if;
    -> select a;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

调用存储过程

--传入值为1，根据存储过程的逻辑，a=9
mysql> call pcd_test_1(1);
+------+
| a    |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--传入值为10，根据存储过程的逻辑，a=10
mysql> call pcd_test_1(10);
+------+
| a    |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--传入值为20，根据存储过程的逻辑，a=11
mysql> call pcd_test_1(20);
+------+
| a    |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--a的赋值范围仅限于存储过程中，所以这个地方的值为null
mysql>  select @a;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

3.CASE WHEN 语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

-- 或者是
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

示例

mysql> delimiter //
mysql> create procedure pcd_test_2(in param_1 int)
    -> begin
    -> case param_1
    -> -- 当case后面有value时，该value会和when中的when_value进行"="判断
    -> -- 相等则执行then后面的语句，然后跳出；否则就进行下一次when的匹配
    -> when 2 then select 200;
    -> when 3 then select 300;
    -> else
    -> begin
    -> -- 当没有匹配时，且else中没有要执行的语句
    -> -- 则给一个begin/end的空语句；
    -> -- 或者不写else语句；或者写个其他的提示性语句；
    -> select "not found!";
    -> end;
    -> end case;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call pcd_test_2(1);
+------------+
| not found! |
+------------+
| not found! |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call pcd_test_2(2);
+-----+
| 200 |
+-----+
| 200 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call pcd_test_2(3);
+-----+
| 300 |
+-----+
| 300 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

4.WHILE 语句

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

示例：

mysql> delimiter //
mysql> create procedure pcd_test_3(in param_1 int)
    -> begin
    -> declare a int default 1;
    -> while param_1 > 10 do
    -> set param_1 = param_1 - 1;
    -> set a = a + 1;
    -> end while;
    -> select a;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

--15 - 10 = 5；需要5次循环
mysql> call pcd_test_3(15);
+------+
| a    |
+------+
|    6 |  --a=a+1=5+1=6
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

5.REPEAT语句

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

示例：

mysql> delimiter //
mysql> create procedure pcd_test_4(in param_1 int)
    -> begin
    -> SET @x = 0; -- 没有使用declare，所以x是会话级别的
    -> REPEAT
    -> SET @x = @x + 1;
    -> UNTIL @x > param_1 END REPEAT;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call pcd_test_4(10);
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

6.loop语句

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

-- ITERATE 和label相结合，表示继续从label处执行
-- LEAVE 和label相结合，表示从label 标记的代码段离开

示例：

mysql>  delimiter //
mysql> create procedure pcd_test_5(in param_1 int)
    -> begin
    -> test_label: loop
    -> set param_1 := param_1 + 1; -- 参数累加
    -> if param_1 < 10 then -- 如果累加的值小于10
    -> iterate test_label; -- 继续执行 标签 test_label
    -> end if;
    -> leave test_label; -- 如果>=10则离开这个test_label(loop)
    -> end loop test_label;
    -> set @x = param_1; -- 设置会话级别的变量
    -> end;//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

-- 5<10 ，累加5次后>=10为true，离开循环
mysql>  call pcd_test_5(5);
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

三、自定义函数

自定义函数和存储过程很类似，但是必需要有返回值；
与内置的函数(sum(), max()等)使用方法类似
- select fun(val);
- select * from t where col= fun(val);
自定义函数可能在遍历每条记录中使用；

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type -- 必须有返回值
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
        COMMENT 'string'
    | LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

示例：计算一个阶乘

--创建一个存放记录的表
mysql> create table test_proc_1(a int, b int); 
Query OK, 0 rows affected (0.02 sec)

--定义函数
mysql> delimiter //
mysql> create function fun_test_1(total int)
    ->  returns int
    ->  begin
    ->  declare i int;
    ->  declare res int;
    ->  set i := 1;
    ->  set res := 1;
    ->  if total <= 0 then
    ->  set total := 1;
    ->  end if;
    ->  while i <= total do
    ->  set res := res * i;
    ->  set i := i + 1;
    ->  end while;
    ->  return res;
    -> end;//
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

上面的定义报错了，原因是因为函数的声明中没有"DETERMINISTIC, NO SQL, or READS SQL DATA"等关键字，需要使用打开参数 log_bin_trust_function_creators，或者增加上述相应功能的关键字

使用 deterministic 关键字

mysql> create function fun_test_1(total int)
    -> returns int deterministic -- 这个只是告诉MySQL这个函数是否会改变数据
    -> -- 即使下面使用了insert，update等DML语句，MySQL不会检查
    -> -- 函数是否会改变数据，完全依赖创建函数的用户去指定的关键字
    -> -- 而非真的是否有修改数据。仅仅只是声明，而非约束
    -> begin
    -> declare i int;
    -> declare res int;
    -> set i := 1;
    -> set res := 1;
    -> if total <= 0 then
    -> set total := 1;
    -> end if;
    -> while i <= total do
    -> set res := res * i;
    -> insert into test_proc_1 values(i, res); -- 在自定义函数中，同样可以使用sql并且该SQL是insert，其实和deterministic违背。
    -> set i := i + 1;
    -> end while;
    -> return res;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

--测试调用
mysql> select fun_test_1(6); 
+---------------+
| fun_test_1(6) |
+---------------+
|           720 |
+---------------+
1 row in set (0.00 sec)

mysql> select * from test_proc_1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    6 |
|    4 |   24 |
|    5 |  120 |
|    6 |  720 |
+------+------+
6 rows in set (0.00 sec)

关键字简单说明：

DETERMINISTIC ：当给定相同的输入，产生确定的结果

NOT DETERMINISTIC ：默认值，认为产生的结果是不确定的

READS SQL DATA ：只是读取SQL数据

MODIFIES SQL DATA ：会修改数据

NO SQL ：没有SQL遇见

CONTAINS SQL ：包含SQL语句，但是没有读写语句，理论有select now()等

13.触发器存储过程函数