MySQL 学习笔记 进阶（存储过程 下，存储函数，触发器，锁 上）

存储过程

存储过程-if判断

• 语法

IF 条件1 THEN
        ......
ELSEIF 条件2 THEN
        ......
ELSE
        ......
END IF;

存储过程-参数

•  用法

CREATE PROCEDURE 存储过程名称([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
    -- SQL语句
END;

存储过程-case

• 语法一

CASE case_value
    WHEN when_value1 THEN statement_list1
    [ WHEN when_value2 THEN statement_list2 ] ...
    [ ELSE statement_list ]
END CASE;

• 语法二

CASE 
    WHEN search_condition1 THEN statement_list1
    [ WHEN search_condition2 THEN statement_list2 ] ...
    [ ELSE statement_list ]
END CASE;

存储过程-循环

• while

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
    SQL逻辑
END WHILE;

• repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

#先执行一次逻辑，然后判断逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环。
REPEAT 
    SQL逻辑...
    UNTIL 条件
END REPEAT;

• loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环。loop可以配合以下两个语句使用：

LEAVE：配合循环使用，退出循环。

ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
     SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

存储过程-游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

• 声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

• 打开游标

OPEN 游标名称;

• 获取游标记录

FETCH 游标记录 INTO 变量 [, 变量 ];

存储过程-条件处理程序

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition value] ... statement;

handler_action
    CONTINUE:继续执行当前程序
    EXIT: 终止执行当前程序
condition_value
    SQLSTATE sqlstate_value: 状态码，如02000
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    NOT FOUNG: 所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

存储函数

存储函数

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

CREATE FUNCTION 存储函数名称([ 参数列表 ])
RETUREN type [characteristic ...]
BEGIN
    -- SQL语句
    RETURN ...;
END;

characteristic说明：
DETERMINISTIC: 相同的输入参数总是产生相同的结果
NOT SQL: 不包含SQL语句
READS SQL DATA: 包含读取数据的语句，但不包含写入数据的语句。

触发器

触发器-介绍

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并进行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

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

•  创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
    trigger_stmt;
END;

• 查看

SHOW TRIGGERS;

• 删除

DROP TRIGGER [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库。

锁

锁-概述

• 介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU，RAM，I/O）的征用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

• 分类

MySQL中的锁，按照锁的粒度分，分为以下三类：

　　　　· 全局锁：锁定数据库中的所有表。

　　　　· 表级锁：每次操作锁住整张表。

　　　　· 行级锁：每次操作锁住对应的行数据。

锁-全局锁-介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

数据库中加全局锁，是一个比较重的操作，存在以下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql