视图

MySQL 从 5.0.1 版本开始提供视图功能，本章对视图 MySQL 中的视图进行介绍。

什么是视图

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本是透明的。视图并不在数据库中实际存在，行和列来自于自定义视图中使用的表，并且是在使用视图时动态生成的。

视图相对于普通表的优势主要包括以下几点：

• 简单： 使用视图的用户完全不用关心后面对应的表结构、关联条件和筛选条件，对于用户来说已经是关联好的复合结果的结果集。
• 安全： 使用视图的用户只能访问他们被允许查询的结果集，对于表的权限管理并不能限制到某个行某个列，但通过视图就可以简单的实现。
• 数据独立： 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加字段对视图没有影响；源字段名修改，则可以通过修改视图来解决，不会对访问者造成影响。

视图操作

视图的操作包括创建或修改视图、删除视图、或查看视图定义。

创建或修改视图

创建视图需要有 CREATE VIEW 权限，并且对于查询所涉及到的列有 SELECT 权限。如果使用 CREATE OR REPLACE 或者 ALTER 修改视图，那么还需要该视图的DROP权限。

创建视图的语法如下：

CREATE [OR REPLACE] [ALGORTHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图的语法如下：

ALTER [[ALGORTHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

例如要创建视图 staff_list_view，可以使用以下命令：

mysql> CREATE OR REPLACE VIEW staff_list_view AS
    -> SELECT p.id,p.name,c.name cname
    -> FROM parent AS p,child AS c 
    -> WHERE p.type = c.id;
Query OK, 0 rows affected (0.00 sec)

MySQL 视图的定义有一些限制，例如：在 5.7.7 版本之前，FROM 关键字后面不能包含子查询，这和其他数据库是不同的，如果视图是从其他数据库迁移过来的，那么可能仅此需要做一些改动。

视图的可能性和视图中查询的定义有关系，以下类型的视图是不可更新的。

（这个更新是指 UPDATE 对视图结果集的更新，而不是更新 视图的SQL语句）

• 包含以下关键字的SQL语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或 UNION ALL。
• 常量视图。
• SELECT 中包含子查询。
• JOIN。
• FROM 一个不可更新的视图。
• WHERE 子句的子查询引用了 FROM 子句中的表。

WITH [CASCADED | LOCAL] CHECK OPTOIN 决定了是否允许更新数据而不再满足视图的条件。这个选项和 Oracle 数据库的选项是类似的：

• LOCAL 只要满足了本视图的条件就可以更新；
• CASCADED 必须满足所有针对该视图的所有视图条件才可以更新。

如果没有指定，默认是 CASCADED。

例如，对 payment 表创建两层视图，并进行更新操作：

mysql> CREATE OR REPLACE VIEW payment_view AS
    -> SELECT country_id,amount FROM payment
    -> WHERE amount < 10 WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE OR REPLACE VIEW payment_view1 AS
    -> SELECT country_id,amount FROM payment_view
    -> WHERE amount > 5 WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE OR REPLACE VIEW payment_view2 AS
    -> SELECT country_id,amount FROM payment_view
    -> WHERE amount > 5 WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM payment_view1 limit 1;
+------------+--------+
| country_id | amount |
+------------+--------+
|          4 |   6.43 |
+------------+--------+
1 row in set (0.00 sec)

mysql> UPDATE payment_view1 SET amount = 10 WHERE country_id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM payment_view2 limit 1;
+------------+--------+
| country_id | amount |
+------------+--------+
|          5 |   5.35 |
+------------+--------+
1 row in set (0.00 sec)

mysql> UPDATE payment_view2 SET amount = 10 WHERE country_id = 5;
ERROR 1369 (HY000): CHECK OPTION failed 'test.payment_view2'

从结果中可以看到， payment_view1 是 WITH LOCAL CHECK OPTION 的，只要满足当前视图的条件就可以更新；而 payment_view2 是 WITH CASCADED CHECK OPTION 的，需要满足所有视图的条件才可以更新，因为更新后的纪录不能满足 payment_view 视图的条件，所以更新时报错了。

注意： 书中的 payment_view 视图的 CHECK_OPTION 是NONE， 但创建的时候默认值是 CASCADED，导致即使是 payment_view1 也是无法更新的，所以如果要嵌套视图更新，注意要把外层改成 NONE。

删除视图

可以一次删除一个或多个视图，但必须有视图的 DROP 条件

 DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCDE]

例如： 删除 staff_list_view 视图：

mysql> DROP VIEW staff_list_view;
Query OK, 0 rows affected (0.00 sec)

查看视图

从MySQL5.1 开始， 使用 SHOW TABLE STATUS 命令的时候，不仅可以显示表的信息，也可以显示视图的信息：

下面演示查看 payment_view 视图的操作：

mysql> SHOW TABLE STATUS LIKE 'payment_view' \G;
*************************** 1. row ***************************
           Name: payment_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

如果需要查询视图的定义，可以使用 SHOW CREATE VIEW 命令：

mysql> SHOW CREATE VIEW payment_view \G
*************************** 1. row ***************************
                View: payment_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `payment_view` AS select `payment`.`country_id` AS `country_id`,`payment`.`amount` AS `amount` from `payment` where (`payment`.`amount` < 10) WITH CASCADED CHECK OPTION
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
1 row in set (0.00 sec)

最后通过查看系统表 informaction_schema.views 也可以查看视图的相关数据：

mysql> SELECT * FROM information_schema.views WHERE table_name = 'payment_view' \G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: payment_view
     VIEW_DEFINITION: select `test`.`payment`.`country_id` AS `country_id`,`test`.`payment`.`amount` AS `amount` from `test`.`payment` where (`test`.`payment`.`amount` < 10)
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_unicode_ci
1 row in set (0.00 sec)

存储过程

MySQL 从 5.0 版本开始支持存储过程和函数。

什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的合集，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用之间的传输，对于提高数据处理效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用 IN、OUT、INOUT 类型，而函数的参数只能是 IN 类型的。如果有函数是从其他类型的数据库迁移至 MySQL ，那么可能因此需要将函数改造成存储过程。

存储过程和函数的相关操作

对存储过程进行相应操作时，应确认用户是否拥有相应的权限。 创建存储过程和函数需要 CREATE ROUTINE 权限，修改或删除存储过程和函数需要 ALTER ROUTINE 权限，执行存储过程或函数需要 EXECUTE 权限。

创建、修改存储过程或函数

语法如下：

CREATE
    [DEFINER = {USER | CURRENT_USER}]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
CREATE
    [DEFINER = {USER | CURRENT_USER}]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
func_parameter:
    param_name type:
    
type:
    Any valid MySQL data type
    
characteristic:
    COMMENT 'string'
    | LENGUAGE SQL
    | [NOT] DETERMINUISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    
routine_body:
     valid SQL routine statement

参数说明：

• IN 参数名 参数类型 输入参数
• OUT 参数名 参数类型 输出参数 可以将结果赋值给这个变量 向外传递
• INOUT 参数名 参数类型 输入输出参数，有点像PHP的传引用，既能传入数据 也能获取修改后的内容

调用存储过程语法如下：

CALL sp_name([parameter[,...]])

MySQL 的存储过程或函数中允许包含 DDL 语句，也允许在存储过程中执行提交或回滚，但存储过程或函数中不允许执行 LOAD DATA INFILE 语句。此外存储过程或函数中可以调用其他的过程或者函数。

下面创建了一个新的过程 payment_get ：

mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE payment_get(IN amount INT,OUT count INT)
    -> READS SQL DATA
    -> BEGIN
    ->  SELECT country_id
    ->  FROM payment
    ->  WHERE payment.amount > amount;
    ->
    ->  SELECT FOUND_ROWS() INTO count;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>

上面创建了一个过程，该过程用于查找 金额大于给定金额的数据，并返回满足要求的记录数。

通常我们在创建过程和函数之前，都会使用 DELIMITER 命令将语句的结束符从 “;” 改为 “$$” ，目的是为了防止MySQL 将过程或函数中的结束符解释成语句的结束符，而提示错误。存储过程或函数创建完毕后，使用 DELIMITER 命令将结束符改回 “;”。

我们先用SQL语句 实现上面存储过程的处理内容：

mysql> SELECT country_id FROM payment WHERE amount > 5;
+------------+
| country_id |
+------------+
|          4 |
|          5 |
|          6 |
+------------+
3 rows in set (0.00 sec)

如果将这个查询封装在过程中，效果如下：

mysql> CALL payment_get(5,@a);
+------------+
| country_id |
+------------+
|          4 |
|          5 |
|          6 |
+------------+
3 rows in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

可以看到存储过程与直接执行SQL的结果是相同的，但存储过程的好处在于逻辑都封装在服务端，调用者不需要了解中间的处理逻辑，一旦逻辑发生变化，只需修改存储过程即可，面对调用者的程序完全没有影响。

和视图的创建语法稍有不同，存储过程和函数的 CREATE 语法并不支持使用 CREATE OR REPLACE 对存储过程和函数进行修改，如果需要对已有的过程和函数进行修改，需要执行 ALTER 语法。

下面对 characteristic 特征值的部分进行简单的说明：

• LANGUAGE SQL ：说明下面过程的的 BODY 是使用 SQL 编写，系统默认，为今后 MySQL 支持除 SQL 外的其他语言支持的存储过程而准备。
• [NOT] DETERMINISTIC ：DETERMINISTIC 确定的，即每次输入一样，输出也一样的程序。 NOT DETERMINISTIC 是非确定的，默认是 非确定的，当前没有被优化程序使用。

• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据特征值来约束过程实际使用数据的情况。

  • CONTAINS SQL 表示子程序不包含读或写的语句
  • NO SQL 表示子程序不包含SQL语句
  • READS SQL DATA 表示子程序包含读数据的语句，但不包含写数据的语句。
  • MODIFIES SQL DATA 表示子程序包含写数据的语句。

  如果创建过程时，没有明确指定，默认使用 CONTAINS SQL

• SQL SECURITY { DEFINER | INVOKER } ：可以用来指定子程序使用创建者权限来执行，还是调用者权限来执行， 默认是 DEFINER。
• COMMENT 'string'：存储过程或函数的注释信息。

删除存储过程和函数

一次只能删除一个存储过程或函数，删除存储过程或函数需要 ALTER ROUTINE 权限，语法如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

例如 删除 payment_get 过程：

mysql> DROP PROCEDURE payment_get;
Query OK, 0 rows affected (0.01 sec)

查看存储过程或函数

存储过程或函数被创建后，用户可能需要查看存储过程、函数的状态、定义等信息，便于了解存储过程或函数的基本情况。

查看存储过程或函数的状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

查看 payment_get 的信息：

mysql> SHOW PROCEDURE STATUS LIKE 'payment_get' \G;
*************************** 1. row ***************************
                  Db: test
                Name: payment_get
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-01-24 22:02:39
             Created: 2021-01-24 22:02:39
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

查看存储过程或函数的定义

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

查看 payment_get 的定义：

mysql> SHOW CREATE PROCEDURE payment_get \G;
*************************** 1. row ***************************
           Procedure: payment_get
            sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `payment_get`(IN amount INT,OUT count INT)
    READS SQL DATA
BEGIN
 SELECT country_id
 FROM payment
 WHERE payment.amount > amount;

 SELECT FOUND_ROWS() INTO count;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

通过系统表查看存储过程或函数的信息

除了以上两种方法，还支持通过系统表来了解存储过程或函数的相关信息，通过查看 information_schema.Routines 就可以获得存储过程或函数的名称、类型、语法、创建人等信息。

例如，查看过程 payment_get 的定义：

mysql> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME = 'payment_get' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: payment_get
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: payment_get
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
 SELECT country_id
 FROM payment
 WHERE payment.amount > amount;

 SELECT FOUND_ROWS() INTO count;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: READS SQL DATA
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2021-01-24 22:02:39
            LAST_ALTERED: 2021-01-24 22:02:39
                SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_unicode_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

变量的使用

定义存储过程或函数可以使用变量（也只能在过程或函数中使用），而且在 MySQL5.1 版本中，变量是不区分大小写的。

变量的定义

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN ... END 块中，可以用在嵌套的块中。变量的定义必须在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个同类型的变量。如果需要，可以使用 DEFAULT 赋默认值。

定义一个变量的语法如下：

DECLARE var_name[,...] type [DEFAULT value]

例如定义一个 DATE 类型的变量，名称是 last_month_start：

DECLARE last_month_start DATE;

变量的赋值

变量可以直接复制，或者通过查询赋值。直接赋值使用 SET，可以赋常量或通过表达式，具体语法如下：

SET var_name = expr [, var_name = expr] ...

例如给刚才定义的变量赋值：

SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)

也可以通过查询将结果赋值给变量，这要求返回的数据必须只有一行，具体语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr

通过查询将变量赋值给 v_total

mysql> DELIMITER $$
mysql>
mysql> CREATE FUNCTION get_payment_sum(end_date DATE)
    -> RETURNS DECIMAL(8,2)
    -> DETERMINISTIC
    -> READS SQL DATA
    -> BEGIN
    ->  DECLARE v_total DECIMAL(8,2); # SUM total
    ->
    ->  SELECT IFNULL(SUM(payment.amount),0) INTO v_total
    ->  FROM payment
    ->  WHERE payment.payment_date <= end_date;
    -> RETURN v_total;
    ->
    -> END $$
mysql> DELIMITER ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT get_payment_sum('2010-01-01');
+-------------------------------+
| get_payment_sum('2010-01-01') |
+-------------------------------+
|                         30.00 |
+-------------------------------+
1 row in set (0.00 sec)

定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题是将如何进行相应的处理。

条件的定义

DECLARE cndition_name CONDITION FOR condition_value

condition_value: 
    mysql_error_code | SQLSTATE [VALUE] sqlstate_value

条件的处理

DECLARE handler_type HANDLER 
 FOR condition_value[,...] 
 statement 
 
 handler_type:
         CONTINUE
     | EXIT
     | UNDO
     
 condition_value: 
     mysql_error_code 
  | SQLSTATE [VALUE] sqlstate_value 
  | condition_name 
  | SQLWARNING 
  | NOT FOUND
  | SQLEXCEPTIONe

通过下面两个例子来说明：如果没有进行 条件的处理，那么在主键冲突的时候会抛出异常并退出；如果对条件进行了处理，则不会抛出异常。

没有进行条件处理时：

mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE parent_insert ()
    -> BEGIN
    ->  SET @x = 1;
    ->  INSERT INTO parent(id,name,type) VALUES(4,'初一',1);
    ->  SET @x = 2;
    ->  INSERT INTO parent(id,name,type) VALUES(1,'初二',1);
    ->  SET @x = 3;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call parent_insert();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> SELECT @x;
+------+
| @x   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

可以看到 当插入 id = 1 的数据时，会组件冲突并退出，没有执行到下面的其他语句。

当对主键冲突的异常进行处理时：

mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE parent_insert ()
    -> BEGIN
    ->  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @err_msg = '主键冲突';
    -> SET @x = 1;
    ->  INSERT INTO parent(id,name,type) VALUES(4,'初一',1);
    -> SET @x = 2;
    -> INSERT INTO parent(id,name,type) VALUES(1,'初二',1);
    -> SET @x = 3;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call parent_insert();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x,@err_msg;
+------+--------------+
| @x   | @err_msg     |
+------+--------------+
|    3 | 主键冲突     |
+------+--------------+
1 row in set (0.00 sec)

调用条件处理的过程，再遇到主键冲突的问题，会按照定义的方式进行处理，由于例子中定义的是 CONTINUE 所以会继续执行下面的语句。

handler_type 目前只支持 CONTINUE 和 EXIT 两种，CONTINUE 表示继续执行下面的语句，EXIT 表示终止执行，UNDO 目前还不支持

condition_value 的值既可以是通过 DECLARE 定义的 condition_name ，也可以是 SQLSTATE 的值或者 mysql-error-code 的值或者 SQLWARNING、NOT FOUND、SQLEXCEPTION，这 3 个值是定义好的错误类别，分别代表不同的含义：

• SQLWARNING 是对所有以 01 开头的 SQLSTATE 代码的速记。
• NOT FOUND 是对所有以 02 开头的 SQLSTATE 代码的速记。
• SQLEXCEPTION 是对所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记。

因此上面的例子还可以写成下面几种方式：

-- 捕获 mysql-error-code
DECLARE CONTINUE HANDLER FOR 1062 SET @err_msg = '主键冲突';

-- 事先定义 condition_name
DECLARE Duplicatekey CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR Duplicatekey SET @err_msg = '主键冲突';

-- 捕获 SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @err_msg = '主键冲突';

光标的使用

在存储过程和函数中，可以使用光标对结果集进行循环处理。光标的使用包含光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下：

声明光标

DECLARE cursor_name CURSOR FOR select_statement

OPEN 光标

OPEN cursor_name

FETCH 光标

FETCH [[NEXT] FROM] cursor_name INTO var_name [,var_name...]

CLOSE 光标

CLOSE cursor_name

下面的例子是一个简单的使用光标的过程，对payment表进行循环处理，累加 id 大于指定参数的 amount 的值，判断循环结束的条件是捕获 NOT FOUND 的条件，当 FETCH 找不到下一条记录的时候，就会关闭光标退出过程。

（因为是使用工具测的，所以没有完整的命令行命令）

CREATE PROCEDURE pay_sum(IN `param_id` int)
BEGIN
    -- 用于存储光标循环的当前值
    DECLARE i_country_id int;
    DECLARE d_amount DECIMAL(15,2);
    -- 光标赋值
    DECLARE cur_payment cursor for select country_id,amount from payment;
    
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    
    -- 设置一个
    set @total_sum = 0.00;
    
    -- 打开光标 
    OPEN cur_payment;
    REPEAT 
    -- 遍历光标 并将当前循环的值赋给变量 i_country_id,d_amount
        FETCH cur_payment INTO i_country_id,d_amount;
            IF i_country_id = param_id THEN
                SET @total_sum = @total_sum + d_amount;
            END IF;
        UNTIL 0 END REPEAT;
-- 关闭光标
CLOSE cur_payment;
END

变量、条件、处理程序、光标都是通过 DECLARE 定义的，它们之间是有先后顺序来要求的。

变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明。

流程控制

下面说明如何使用 IF 、CASE、LOOP、LEAVE、ITERATE、REPEAT 及 WHITE 语句来控制流程。

IF 条件语句

IF 实现条件判断，满足不同的条件执行不同的语句列表，具体语法如下：

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

-- 例子
IF i_country_id = param_id THEN
    SET @total_sum = @total_sum + d_amount;
END IF;

CASE 条件语句

CASE 实现比 IF 更复杂一些的条件构造（就是switch），具体语法如下:

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

上面 IF 的例子，使用 CASE 来完成：

case
    when i_country_id = param_id then
     SET @total_sum = @total_sum + d_amount;
    else
     -- else 逻辑
end case;

-- 或者

case i_country_id 
    when param_id then
     SET @total_sum = @total_sum + d_amount;
    else
     -- else 逻辑
end case;

LOOP 循环语句

LOOP 实现简单的循环，退出循环的条件需要使用他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

如果不在 statement_list 中增加退出循环的语句，那么LOOP 语句可以用来实现一个简单的死循环。

LEAVE 退出循环语句

LEAVE 用来从标注的流程构造中退出，通常和 BEGIN...END 或者 循环一起使用。

下面是使用 LEAVE 和 LOOP 的简单例子，循环 100 次向表中插入数据，当插入 100 条数据之后退出。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE actor_insert ()
    -> BEGIN
    ->  set @x = 0;
    ->  ins: LOOP
    ->    set @x = @x + 1;
    ->    IF @x = 100 then
    ->          leave ins;
    ->    END IF;
    ->    INSERT INTO actor(first_name,last_name) VALUES('Test',@x);
    ->  END LOOP ins;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> select count(*) FROM actor;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> call actor_insert();
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) FROM actor;
+----------+
| count(*) |
+----------+
|       99 |
+----------+
1 row in set (0.00 sec)

mysql>

ITERATE 跳过循环语句

ITERATE 语句必须在循环内使用，作用是跳过当前循环，进行下次循环。

下面的例子 当 @x 变量是偶数时，不再执行循环中剩下的语句，而是进行下一次循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `actor_insert`()
BEGIN 
    set @x = 0;
    ins: LOOP 
      set @x = @x + 1;
      IF @x > 10 then 
            leave ins;
        ELSEIF MOD(@x,2) = 0 then
            ITERATE ins;
      END IF;
      INSERT INTO actor(actor_id,first_name,last_name) VALUES(@x + 200,'Test',@x);
    END LOOP ins;
END

执行结果

mysql> call actor_insert();
Query OK, 0 rows affected (0.00 sec)

mysql> select * FROM actor;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|      201 | Test       | 1         |
|      203 | Test       | 3         |
|      205 | Test       | 5         |
|      207 | Test       | 7         |
|      209 | Test       | 9         |
+----------+------------+-----------+
5 rows in set (0.00 sec)

REPEAT 循环语句

有条件的控制循环语句，当满足条件的时候退出循环，具体语法如下：

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

使用方法，完整SQL 见 光标的使用：

REPEAT 
    -- 遍历光标 并将当前循环的值赋给变量 i_country_id,d_amount
        FETCH cur_payment INTO i_country_id,d_amount;
            IF i_country_id = param_id THEN
                SET @total_sum = @total_sum + d_amount;
            END IF;
        UNTIL 0 END REPEAT;

WHILE 循环语句

WHILE 循环和 REPEAT 循环的区别在于：WHILE 是满足条件才执行循环，REPEAT 是满足条件才退出循环。WHILE 在首次执行前就判断条件，所以循环最少执行 0 次，REPEAT 是在首次循环后才判断条件，所以循环最少执行1次。

以下例子用来对比 REPEAT 和 WHILE 语句的功能：

mysql> delimiter $$
mysql> CREATE PROCEDURE loop_demo ()
    -> BEGIN
    ->  SET @x = 1,@x1 = 1;
    ->  REPEAT
    ->  SET @x = @x + 1;
    ->  UNTIL @x > 0 END REPEAT;
    ->
    ->  WHILE @x1 < 0 DO
    ->  SET @x1 = @x1 + 1;
    ->  END WHILE;
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call loop_demo();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x,@x1;
+------+------+
| @x   | @x1  |
+------+------+
|    2 |    1 |
+------+------+
1 row in set (0.00 sec)

从判断条件上来看，初始值都是满足退出条件的，但 REPEAT 仍执行了一次循环，而 WHILE 一次循环都没有执行。

事件调度器

事件调度器是MySQL5.1 之后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似于 Linux 的任务调度器 crontab。

下面是一个最简单的事件调度器：

CREATE EVENT myevent
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
        UPDATE myschema.mytable SET mycol = mycol + 1;

其中：

• 事件名称在 CREATE EVENT 关键字后面指定；
• 通过 ON SCHEDULE 子句指定时间在何时执行以及执行频次；
• 通过 DO 子句指定要执行的具体操作或事件。

上面创建的调度事件，首先创建了 myevent 调度事件，然后执行更新操作，起始执行时间为调度器创建时间，后续在起始时间的基础上每隔 1 小时触发一次。

使用

下面通过完整的示例来展示调度器的使用

查看调度器状态

若事件调度器状态为 off，则需要开启，否则事件不会触发。

-- 查看事件调度器状态
mysql> SHOW variables LIKE '%scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set, 1 warning (0.01 sec)

-- 开启事件调度器
mysql> SET GLOBAL event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW variables LIKE '%scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

-- 查看进程发现一个用户为 event_scheduler 的进程
mysql> SHOW processlist \G;
 ... 
*************************** 2. row ***************************
     Id: 25
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 122
  State: Waiting on empty queue
   Info: NULL
2 rows in set (0.00 sec)

创建测试表 test

mysql> create table test_event(id varchar(10),create_time datetime);
Query OK, 0 rows affected (0.01 sec)

创建事件调度器

创建事件调度器 test_event_1，每隔 5s 插入一条记录：

mysql> CREATE EVENT test_event_1
    -> ON SCHEDULE
    -> EVERY 5 SECOND
    -> DO
    -> INSERT INTO test.test(id,create_time) VALUES('test',now());
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_event;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
| test | 2021-03-28 18:37:47 |
| test | 2021-03-28 18:37:52 |
| test | 2021-03-28 18:37:57 |
+------+---------------------+
3 rows in set (0.00 sec)

使用调度器定时清空表

为了防止表变得很大，创建一个新的调度器，每隔 1min 清空一次 test_event 表。

mysql> CREATE EVENT trunc_event
    -> ON SCHEDULE EVERY 1 MINUTE
    -> DO TRUNCATE TABLE test_event;
Query OK, 0 rows affected (0.00 sec)

这类触发器非常适合用来定时清空临时表或日志表。

删除或禁用调度器

如果事件调度器不再使用，可以禁用（disable）或删除（drop）掉：

-- 禁用
mysql> ALTER EVENT test_event_1 disable;
Query OK, 0 rows affected (0.00 sec)

-- 删除
mysql> DROP EVENT test_event_1;
Query OK, 0 rows affected (0.00 sec)

事件调度器还有很多选项，比如指定开始时间和结束时间，或者指定某个时间执行一次而不是循环执行，具体可以参考官方文档，这里不做赘述。

优势、场景、注意事项

最后总结一下事件调度器的优势、适用场景以及使用中的注意事项：

优势

MySQL 事件调度器部署在数据库内部由DBA 或专人统一管理，避免将数据库相关的定时任务部署在操作系统层，减少操作系统管理员产生误操作的风险，对于后续的管理和维护也非常有益。

例如，后续进行数据库迁移时无需再迁移操作系统层的定时任务，数据库迁移本身已经包含了调度事件的迁移。

适用场景

• 定时收集统计信息
• 定期清理历史数据
• 定期数据库检查（例如，自动监控和恢复 Slave 失败进程）

注意事项

• 在繁忙且要求性能的数据库服务器上要慎重部署和启用调度器
• 过于复杂的处理更适合用程序实现
• 开启或关闭 调度器需要具有超级用户权限

触发器

MySQL 从 5.0.2 版本开始支持触发器功能。触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句合集。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

创建触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time tirgger_event 
    ON tbl_name FOR EACH ROW [trigger_order] trigger_body

触发器只能建立在永久表上，不能对临时表创建触发器。

其中 trigger_time 是触发器的触发时间，可以是 BEFORE （检查约束前触发）或者 AFTER （检查约束后触发）。

而 trigger_event 是触发器的触发事件，可以是 INSERT、UPDATE、DELETE。

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

在测试数据库中，为 film 表建立 AFTER INSERT 触发器，具体如下：

mysql> DELIMITER $$
mysql> CREATE TRIGGER ins_film
    -> AFTER INSERT ON film FOR EACH ROW BEGIN
    ->  INSERT INTO film_text(id,title,description)
    ->  VALUES(new.id,new.title,CONCAT(NOW(),' - ',new.description));
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;

向 film 表插入数据，会向 film_text 表插入同步的数据

mysql> INSERT INTO film VALUES(101,'zhangsan','张三是一个可爱的男孩子');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM film;
+------+----------+--------------------------+
| id   | title    | description              |
+------+----------+--------------------------+
|  101 | zhangsan | 张三是一个可爱的男孩子         |
+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM film_text;
+------+----------+------------------------------------------------+
| id   | title    | description                                    |
+------+----------+------------------------------------------------+
|  101 | zhangsan | 2021-04-06 22:27:52 - 张三是一个可爱的男孩子         |
+------+----------+------------------------------------------------+
1 row in set (0.00 sec)

对于 INSERT INTO...ON DUPLICATE KEY UPDATE 语句来说，触发触发器的顺序是可能造成疑惑的。

下面对 film 表分别创建 BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 触发器，然后插入记录，观察触发器的触发情况。

mysql> create table tri_demo(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER ins_film_bef
    -> BEFORE INSERT ON film FOR EACH ROW BEGIN
    ->  INSERT INTO tri_demo(note) VALUES('before insert');
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER ins_film_aft
    -> AFTER INSERT ON film FOR EACH ROW BEGIN
    ->  INSERT INTO tri_demo(note) VALUES('after insert');
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER upd_film_bef
    ->  BEFORE UPDATE ON film FOR EACH ROW BEGIN
    ->  INSERT INTO tri_demo(note) VALUES('before update');
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER upd_film_aft
    ->  AFTER UPDATE ON film FOR EACH ROW BEGIN
    ->  INSERT INTO tri_demo(note) VALUES('after update');
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

插入已经存在的数据：

mysql> INSERT INTO film VALUES(101,'zhangsan','hi zhangsan')
    -> ON DUPLICATE KEY
    -> UPDATE title = 'update record';
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM tri_demo;
+----+---------------+
| id | note          |
+----+---------------+
|  1 | before insert |
|  2 | before update |
|  3 | after update  |
+----+---------------+
3 rows in set (0.00 sec)

插入不存在的数据：

mysql> INSERT INTO film VALUES(102,'zhangsan','hi zhangsan')
    -> ON DUPLICATE KEY
    -> UPDATE title = 'update record';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tri_demo;
+----+---------------+
| id | note          |
+----+---------------+
|  4 | before insert |
|  5 | after insert  |
+----+---------------+
2 rows in set (0.00 sec)

对于有重复记录、需要 update 操作的 INSERT，触发器的触发顺序是 BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE；

对于没有重复记录的 INSERT，触发器的执行顺序是 BEFORE INSERT、AFTER INSERT；

对于实际执行 UPDATE 操作的记录，仍会执行 BEFORE INSERT 触发器的内容；

在设计触发器的时候一定要考虑这种情况，避免错误的触发触发器。

删除触发器

一个可以删除一个触发器，如果没有指定 schema_name 则默认当前数据库，具体语法如下：

DROP TRIGGER [schema_name.]trigger_name

例如删除 film表上的 ins_film 触发器，使用以下命令：

mysql> DROP trigger ins_film;
Query OK, 0 rows affected (0.01 sec)

查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息，但是因为不能拆线呢指定的触发器，所以每次查询都返回所有的触发器的信息，使用起来不是很方便，具体语法如下：

mysql> SHOW triggers \G;
*************************** 1. row ***************************
             Trigger: ins_film_bef
               Event: INSERT
               Table: film
           Statement: BEGIN
        INSERT INTO tri_demo(note) VALUES('before insert');
END
              Timing: BEFORE
             Created: 2021-04-06 23:15:47.49
            sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
...

另一个查看方式是查询系统表的 information_schema.triggers 表，这个方式可以查询指定触发器的指定信息，操作起来更方便：

mysql> DESC information_schema.triggers;
+----------------------------+---------------+------+-----+---------+-------+
| Field                      | Type          | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------+-------+
| TRIGGER_CATALOG            | varchar(512)  | NO   |     |         |       |
| TRIGGER_SCHEMA             | varchar(64)   | NO   |     |         |       |
| TRIGGER_NAME               | varchar(64)   | NO   |     |         |       |
| EVENT_MANIPULATION         | varchar(6)    | NO   |     |         |       |
| EVENT_OBJECT_CATALOG       | varchar(512)  | NO   |     |         |       |
| EVENT_OBJECT_SCHEMA        | varchar(64)   | NO   |     |         |       |
| EVENT_OBJECT_TABLE         | varchar(64)   | NO   |     |         |       |
| ACTION_ORDER               | bigint(4)     | NO   |     | 0       |       |
| ACTION_CONDITION           | longtext      | YES  |     | NULL    |       |
| ACTION_STATEMENT           | longtext      | NO   |     | NULL    |       |
| ACTION_ORIENTATION         | varchar(9)    | NO   |     |         |       |
| ACTION_TIMING              | varchar(6)    | NO   |     |         |       |
| ACTION_REFERENCE_OLD_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_NEW_TABLE | varchar(64)   | YES  |     | NULL    |       |
| ACTION_REFERENCE_OLD_ROW   | varchar(3)    | NO   |     |         |       |
| ACTION_REFERENCE_NEW_ROW   | varchar(3)    | NO   |     |         |       |
| CREATED                    | datetime(2)   | YES  |     | NULL    |       |
| SQL_MODE                   | varchar(8192) | NO   |     |         |       |
| DEFINER                    | varchar(93)   | NO   |     |         |       |
| CHARACTER_SET_CLIENT       | varchar(32)   | NO   |     |         |       |
| COLLATION_CONNECTION       | varchar(32)   | NO   |     |         |       |
| DATABASE_COLLATION         | varchar(32)   | NO   |     |         |       |
+----------------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.triggers WHERE trigger_name = 'ins_film_bef' \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_film_bef
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: film
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
        INSERT INTO tri_demo(note) VALUES('before insert');
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2021-04-06 23:15:47.49
                  SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_unicode_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.01 sec)

触发器的使用

触发器执行的语句有以下两个限制：

• 触发程序即不能调用将数据返回给客户端的存储程序，也不能使用采用 CALL语句的动态SQL，但是允许存储程序通过参数将数据返回触发程序。也就是允许存储过程或函数通过 OUT 或 INOUT 类型的参数将数据返回给触发器是可以的，但不能调用直接返回数据的过程或函数。
• 不能在触发器中使用以显式或隐式的方式开始或结束事务的语句，如 START、TRANS-ACTION、COMMIT、或 ROLLCACK。

MySQL 触发器是按照 BERORE触发器、行操作、AFTER 触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行操作，那么会整个作为一个事务被回滚（Rollback），但如果是对非事务表进行操作，那么已经执行的操作将无法回滚，这也是设计触发器时需要注意的。

小结

关于触发器这部分，需要特别注意的是触发器是行触发，每次增加、修改或删除表机都会进行触发处理，编写过于复杂的触发器或增加过多的触发器对记录的插入、更新、删除操作肯定会有比较严重的影响，因此在设计数据库时，不要将应用的处理逻辑过多的依赖触发器来处理。