深入浅出MySQL 开发常用数据库对象

开发常用数据库对象

视图

​ MySQL 从6.0.1版本开始提供视图功能，本节将对MySQL中的视图进行介绍。

什么是视图

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

​ 视图相对于普通的表的优势主要包括以下几项。

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

视图操作

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

创建或者修改视图

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

​ 创建视图的语法如下：

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

​ 修改视图的语法如下：

ALTER [ALGORTIHM = {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 s.staff_id,s.first_name,s.last_name,a.address from staff as s,address as a where s.address_id = a.address_id;
Query OK, 0 rows affected (0.01 sec)

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

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

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

例如，以下的视图都是不可更新的：

-- 包含聚合函数
create or replace view payment_sum as select staff_id, sum(amount) from payment group by staff_id;

-- 常量函数
create or replace view pi as select 3.1415926 as pi;

-- select 中包含子查询
create view city_view as select (select city from city where city_id = 1);

​ WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。这个选项与 Oracle 数据库中的选项是类似的，其中：

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

如果没有明确是 LOCAL 还是 CASCADED ，则默认是 CASCADED。

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

mysql> create or replace view payment_view as
    -> select payment_id,amount from payment
    -> where amount < 10 with check option;
Query OK, 0 rows affected (0.01 sec)

mysql> create or replace view payment_view1 as
    -> select payment_id,amount from payment_view
    -> where amount > 4 with local check option;
Query OK, 0 rows affected (0.01 sec)
mysql> create or replace view payment_view2 as
    -> select payment_id, amount from payment_view
    -> where amount > 4 with cascaded check option;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from payment_view1 limit 1;
+------------+--------+
| payment_id | amount |
+------------+--------+
|          1 |   5.00 |
+------------+--------+
1 row in set (0.00 sec)

mysql> update payment_view1 set amount=10 where payment_id=1;
ERROR 1369 (HY000): CHECK OPTION failed 'test.payment_view1'
mysql> update payment_view2 set amount=10 where payment_id=1;
ERROR 1369 (HY000): CHECK OPTION failed 'test.payment_view2'

经过测试，发现payment_view1 和 payment_view2都不能进行更新（不满足创建视图的约束条件）

删除视图

​ 用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP权限。

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

​ 例如删除staff_list视图：

drop view staff_list;

查看视图

​ 从mysql5.1版本开始，使用 show tables 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示试图的 show views 命令

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| payment        |
| payment_view   |
| payment_view1  |
| payment_view2  |
+----------------+
4 rows in set (0.00 sec)

​ 同样，在使用 SHOW TABLE STATUS命令的时候不但可以显示表的信息，同样也可以显示视图的信息。所以，可以通过下面的命令显示视图的信息：

SHOW TABLE STATUS [FROM db_name] [LIKE 'PATTERN']

​ 下面演示是查看视图信息的操作：

mysql> show table status like 'payment_view' \G;
*************************** 1. row ***************************
           Name: payment_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-09-26 14:04:41
    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`.`payment_id` AS `payment_id`,`payment`.`amount` AS `amount` from `payment` where (`payment`.`amount` < 10) WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

​ 最后，通过查看系统表 information_schema.views 也可以查看视图的相关信息。

mysql> select * from views where table_name = 'payment_view' \G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: payment_view
     VIEW_DEFINITION: select `test`.`payment`.`payment_id` AS `payment_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: utf8
COLLATION_CONNECTION: utf8_general_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_parmeter[,...]])
	[characteristic ...] routine_body
CREATE
	[DEFINER = { user | CURRENT_USER }]
	FUNCTION sp_name ([func_parameter[, ...]])
	RETURNS type
	[characteristic ...] routine_body

proc_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

​ 调用过程的语法如下：

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

​ mysql的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交（commit，即确认之前的修改）或者回滚，但是存储过程和函数中不允许执行 LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的过程或者函数。

​ 下面创建了一个新的过程 film_in_stock;

mysql> DELIMITER $$
mysql> CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
    -> READS SQL DATA
    -> BEGIN
    -> 		select inventory_id
    -> 		from inventory
    -> 		where film_id=p_film_id and store_id=p_store_id
    -> 		and inventory_in_stock(inventory_id);
    ->
    -> 		select found_rows() into p_film_count;
    -> END $$
mysql> DELIMITER ;

​ 上面是在使用的样例数据库中创建的一个过程，该过程用来检查 film_id 和 store_id 对应的inventory 是否满足要求，并且返回满足要求的inventory_id 以及满足要求的记录数。

​ 通常我们在执行创建过程和函数之前，都会通过 “DELIMITER $$”命令将语句的结束符从“;”修改成其他符号，这里使用的是“$$” ,这样在过程和函数中的“;” 就不会被MySQL解释成语句的结束而提示错误。存储过程或者函数创建完毕，通过 “DELIMITER ;” 命令将结束符修改回成“;”。

​ 可以看到在这个存储过程中调用了函数 inventory_in_stock() 并且这个过程有两个输入参数和一个输出参数。下面可以通过调用这个过程来看看返回结果。

​ 如果需要检查 film_id=2 store_id=2 对应的 inventory的情况，则首先手工执行过程中的SQL语句，以查看执行的效果：

mysql> select * from inventory;
+--------------+---------+----------+
| inventory_id | film_id | store_id |
+--------------+---------+----------+
|            1 |       2 |        2 |
|            2 |       2 |        2 |
+--------------+---------+----------+
2 rows in set (0.00 sec)

​ 满足条件的记录应该是两条，inventory_id分别是1和2.如果将这个查询封装在存储过程中调用，那么调用过程的执行情况如下：

mysql> call film_in_stock(2,2,@a);
+--------------+
| inventory_id |
+--------------+
|            1 |
|            2 |
+--------------+
2 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    2 |
+------+
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’ ： 存储过程或者函数的注释信息。

​ 下面的例子对比了 SQL SECURITY 特征值得不同，使用root用户创建了两个相似的存储过程，分别指定使用创建者的权限执行和调用者的权限执行，然后使用一个普通用户调用两个存储过程，对比执行效果。

​ 首先，使用root用户创建以下两个存储过程 film_in_stock_definer 和 film_in_stock_invoker:

mysql> DELIMITER $$
mysql> CREATE procedure film_in_stock_definer(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
    -> sql SECURITY DEFINER
    -> BEGIN
    -> SELECT inventory_id from inventory where film_id=p_film_id and store_id = p_store_id;
    -> select found_rows() into p_film_count;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE film_in_stock_invoker(IN p_flim_id INT, IN p_store_id INT, OUT p_film_count INT)
    -> SQL SECURITY INVOKER
    -> BEGIN
    -> SELECT inventory_id from inventory where film_id=p_film_id and store_id = p_store_id;
    -> select found_rows() into p_film_count;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

​ 给普通用户赋予可以执行存储过程的权限，但是不能查询inventory 表 ：

mysql> create user 'lisa'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant execute on test.* TO 'lisa'@'%';
Query OK, 0 rows affected (0.01 sec)

​ 使用lisa登录后，直接查询表inventory会提示被拒绝

mysql> select count(*) from inventory;
ERROR 1142 (42000): SELECT command denied to user 'lisa'@'localhost' for table 'inventory'

​ lisa 用户分别调用 film_in_stock_definer 和 film_in_stock_invoker:

mysql> call film_in_stock_definer(2,2,@a);
+--------------+
| inventory_id |
+--------------+
|            1 |
|            2 |
+--------------+
2 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> call film_in_stock_invoker(2,2,@a);
ERROR 1142 (42000): SELECT command denied to user 'lisa'@'localhost' for table 'inventory'

​ 从上面的例子可以看出，film_in_stock_definer 是以创建者的权限去执行的，因为是root创建的，所以可以访问 inventory 表，film_in_stock_invoker是以调用者的权限执行的，因为lisa没有访问 inventory表的权限，所以提示权限不足。

删除存储过程或者函数

​ 一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的 ALTER ROUTINE 权限，具体语法如下：

DROP { PROCEDURE | FUNCTION } [IF EXISTS] sp_name

​ 例如，使用drop语句删除存储过程：

mysql> drop procedure film_in_stock_invoker;
Query OK, 0 rows affected (0.01 sec)

查看存储过程或者函数

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

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

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

​ 下面演示的是查看过程 film_in_stock的信息：

mysql> show procedure status like 'film_in_stock' \G;
*************************** 1. row ***************************
                  Db: test
                Name: film_in_stock
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-09-26 21:15:53
             Created: 2020-09-26 21:15:53
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

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

SHOW CREATE { PROCEDURE | FUNCTION } sp_name

​ 下面演示的是查看过程film_in_stock 的定义

mysql> show create procedure film_in_stock \G;
*************************** 1. row ***************************
           Procedure: film_in_stock
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(IN p_film_id int, IN p_store_id INT, OUT p_film_count INT)
    READS SQL DATA
begin
select inventory_id
from inventory
where film_id=p_film_id and store_id=p_store_id;
select found_rows() INTO p_film_count;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

3. 通过查看 information_schema.Routines了解存储过程和函数的信息

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

​ 例如，通过查看 information_schema.Routines 查看过程 film_in_stock 的定义。

mysql> select * from information_schema.Routines where routine_name='film_in_stock' \G;
*************************** 1. row ***************************
           SPECIFIC_NAME: film_in_stock
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: film_in_stock
            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 inventory_id
from inventory
where film_id=p_film_id and store_id=p_store_id;
select found_rows() INTO p_film_count;
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: READS SQL DATA
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-09-26 21:15:53
            LAST_ALTERED: 2020-09-26 21:15:53
                SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
1 row in set (0.01 sec)

变量的使用

​ 存储过程和函数中可以使用变量，而且在MySQL5.1版本中变量是不区分大小写的。

1. 变量的定义

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

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

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

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

DECLARE last_month_start DATE;

2. 变量的赋值

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

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

​ 给刚才定义的变量 last_month_start赋值，具体语法如下：

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

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

SELECT column[,...] into var_name[,...] table_expr

​ 将查询结果赋值给变量v_payments:

CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date datetime)
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
	...
	DECLARE v_payments DECIMAL(5,2);# SUM OF PAYMENTS MADE PREVIOUSLY
	...
	SELECT IFNULL(SUM(payment.amount), 0) INTO v_payments
	FROM payment
	where payment.payment_date <= p_effective_date
	AND payment.customer_id = p_customer_id;
	...
	RETURN v_rentfees + v_overfees - v_payments;
END $$	

定义条件和处理

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

1. 条件的定义

DECLARE condition_name CONDITION FOR condition_value

condition_value:
	mysql_error_code | SQLSTATE [VALUE] sqlstate_value

2. 条件的处理

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 | SQLEXCEPTION 

​ 下面将通过两个例子来说明：在向actor表中插入记录时，如果没有进行条件的处理，那么在主键冲突的时候会抛出异常并退出；如果对条件进行了处理，那么就不会再抛出异常。

​ （1）当没有进行条件处理时，执行结果如下：

mysql>DELIMITER $$
mysql> create procedure actor_insert()
    -> begin
    -> set @x=1;insert into actor value(2);
    -> set @x=2;insert into actor value(1);
    -> set @x=3;END $$
Query OK, 0 rows affected (0.01 sec)

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

​ 从上面的例子可以看出，执行到插入 id =1时，会主键冲突并退出，没有执行下面的其它语句。

​ （2）当对主键冲突异常进行处理时，执行结果如下：

mysql> drop procedure actor_insert$$
Query OK, 0 rows affected (0.01 sec)
mysql> create procedure actor_insert()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x=1;
    -> insert into actor value(3);
    -> set @x=2; insert into actor value(1);
    -> set @x=3;
    -> end $$
    
mysql> select @x, @x2$$
+------+------+
| @x   | @x2  |
+------+------+
|    3 |    1 |
+------+------+
1 row in set (0.00 sec)
    

​ 调用条件处理的过程，在遇到主键冲突的错误时，会按照当以的处理方式，由于例子中定义的是CONTINUE,所以会继续执行下面的语句。

​ handler_type 现在还只支持CONTINUE 和 EXIT两种，CONTINUE 表示继续执行下面的语句，EXIT则表示执行终止，UNDO现在还不支持。

​ continue_value 的值既可以是通过DECLARE 定义的 condition_name，也可以是SQLSTATE的值或者 mysql_error_code 的值或者 SQLWARNING,NOT FOUND,SQLEXCEPTION,这3个值是3种定义好的错误类别，分别代表不同含义：

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

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

-- 捕获 mysql-error-code
DECLARE CONTINUE HANDLER FOR 1062 SET @x2=1;
-- 事先定义 condition_name:
DECLARE Duplicatekey CONDITION FOR SQLSTATE '23000'
DECLARE CONTINUE HANDLER FOR duplicatekey set @x2=1;
-- 捕获 SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2=1;

光标的使用

​ 在存储过程和函数中，可以使用光标对结果集进行循环处理。光标的使用包括光标的声明、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 表 按照行进行循环的处理， 按照 staff_id 值的不同累加 amount 的值，判断循环结束的条件是捕获 NOT FOUND 的条件，当 FETCH 光标找不到下一条记录的时候，就会关闭光标然后退出过程。

  delimiter $$
  create procedure payment_stat ()
  BEGIN
  	declare i_staff_id int;
  	declare d_amount decimal(5,2);
  	declare cur_payment cursor for select staff_id, amount from payment;
  	declare exit handler for not found close cur_payment;
  	
  	set @x1=0;
  	set @x2=0;
  	
  	open cur_payment;
  	
  	REPEAT
  		FETCH cur_payment into i_staff_id, d_amount;
  			if i_staff_id = 2 then
  				set @x1 = @x1 + d_amount;
  			else
              	set @x2 = @x2 + d_amount;
              end if;
      UNTIL 0 END REPEAT;
      
      CLOSE cur_payment;
  END $$    
  
  delimiter ;
  call payment_stat();
  select @x1,@x2;
  

流程控制

​ 下面将逐一进行说明如何使用IF、CASE、LOOP、LEAVE、ITERATE、REPEAT及WHILE 语句来控制流程。

1. IF 语句

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

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

2. CASE 语句

​ CASE 实现比 IF更复杂一些的条件构造，具体语法如下：

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	

3. LOOP 语句

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

[begin_label:] LOOP
	statement_list
END LOOP [end_label]	

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

4. LEAVE 语句

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

​ 下面是一个使用LOOP 和 LEAVE 的简单例子，循环 100 次向 actor 表中插入记录，当插入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(id) values(null);
    -> 	END LOOP ins;
    -> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call actor_insert();
Query OK, 0 rows affected (0.67 sec)

mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)

5. ITERATE 语句

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

​ 下面的例子使用了ITERATE语句，当@x变量是偶数的时候，不再执行循环中剩下的语句，而直接进入下一轮循环：

mysql> delimiter $$
mysql> create 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 (id) values (@x+200);
    -> end loop ins;
    -> end;
    -> $$
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from actor;
+-----+
| id  |
+-----+
| 201 |
| 203 |
| 205 |
| 207 |
| 209 |
+-----+
5 rows in set (0.00 sec)

6. REPEAT 语句

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

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

例如：

REPEAT
		FETCH cur_payment into i_staff_id, d_amount;
			if i_staff_id = 2 then
				set @x1 = @x1 + d_amount;
			else
            	set @x2 = @x2 + d_amount;
            end if;
    UNTIL 0 END REPEAT;

7. WHILE 语句

​ WHILE 语句实现的也是有条件的循环控制语句，即当满足条件时执行循环的内容，具体语法如下：

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

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

事件调度器

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

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

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

​ 其中：

1. 事件名称在 create event 关键字后指定；
2. 通过 ON SCHEDULE 子句指定事件在何时执行及执行频次；
3. 通过DO子句指定要执行的具体操作或事件。

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

下面通过一个完整的实例来熟悉事件调度器的使用。

（1）创建测试表 test：

mysql> create table test(id1 varchar(10), create_time datetime);
Query OK, 0 rows affected (0.05 sec)

（2）创建事件调度器 test_event_1 ，每隔5s向test表插入一条记录：

mysql> create event test_event_1
    -> on schedule
    -> every 5 second
    -> do
    -> insert into test.test(id1,create_time) values ('test', now());
Query OK, 0 rows affected (0.01 sec)

（3）查看调度器状态：

mysql> show events \G;
*************************** 1. row ***************************
                  Db: test
                Name: test_event_1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 5
      Interval field: SECOND
              Starts: 2020-09-27 22:01:01
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

（4）每隔几秒后，查看test表，发现有数据插入。

mysql> select * from test;
+------+---------------------+
| id1  | create_time         |
+------+---------------------+
| test | 2020-09-27 22:01:01 |
| test | 2020-09-27 22:01:06 |
| test | 2020-09-27 22:01:11 |
| test | 2020-09-27 22:01:16 |
| test | 2020-09-27 22:01:21 |
| test | 2020-09-27 22:01:26 |
| test | 2020-09-27 22:01:31 |
| test | 2020-09-27 22:01:36 |
| test | 2020-09-27 22:01:41 |
| test | 2020-09-27 22:01:46 |
| test | 2020-09-27 22:01:51 |
| test | 2020-09-27 22:01:56 |
| test | 2020-09-27 22:02:01 |
| test | 2020-09-27 22:02:06 |
| test | 2020-09-27 22:02:11 |
| test | 2020-09-27 22:02:16 |
| test | 2020-09-27 22:02:21 |
+------+---------------------+
17 rows in set (0.00 sec)

注意：表中没有数据，表示事件调度器状态默认是关闭的

mysql> show variables like ‘%scheduler%’;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| event_scheduler | OFF |
±----------------±------+
1 row in set, 1 warning (0.01 sec)

打开调度器

SET GLOBAL event_scheduler = 1;

（5） 查看后台进程

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 0
  State: Waiting for next activation
   Info: NULL

（6）为了防止表变得很大，创建一个新的调度器，每隔1min 清空一次 test 表：

mysql> create event trunc_test
    -> on schedule every 1 minute
    -> do truncate table test;
Query OK, 0 rows affected (0.01 sec)

隔了一段时间后，可以发现，test表中数据会定时清空，这类触发器非常适合去定期清空临时表或日志表。

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

-- 禁用
mysql> alter event test_event_1 disable;
Query OK, 0 rows affected (0.01 sec)

-- 删除
mysql> drop event test_event_1;
Query OK, 0 rows affected (0.01 sec)

​ 对于事件调度器，还有很多选项，比如指定事件开始时间和结束时间，或者指定某个时间执行一次而不是循环执行，详细信息可以参考事件调度器的相关帮助，这里不再详述。

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

事件调度器	说明
优势	MySQL 事件调度器部署在数据库内部由DBA或专人统一维护和管理，避免将一些数据库相关的定时任务部署在操作系统层，减少操作系统管理员产生误操作的风险，对后续的管理和维护也非常有益。例如，后续进行数据库迁移时无需再迁移操作系统层的定时任务，数据库迁移本身已经包含了调度事件的迁移。
适用场景	事件调度器适用于定期收集统计信息、定期清理历史数据、定期数据检查（例如，自动监控和恢复slave 失败进程）
注意事项	在繁忙且要求性能的数据库服务器上要慎重部署和和启用调度器，过于复杂的处理更适合用程序实现，开启和关闭事件调度器需要具有超级用户权限

触发器

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

创建触发器

​ 创建触发器的语句如下：

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

注意：触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table） 创建触发器。

​ 其中trigger_time 是触发器的触发时间，可以是BEFORE 或者 AFTER ，BEFORE 的含义指在检查约束前触发，而AFTER 是在检查约束后触发。

​ 而tigger_event 就是触发器的触发事件，可以是 INSERT,UPDATE 或者DELETE.

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

​ 在样例数据库中，为 film 表创建了 AFTER INSERT 的触发器，具体如下：

delimiter $$
create trigger ins_film
after insert on film for each row begin
	insert into film_text(film_id, title, description) values (new.film_id,new.title,new.description);
end;
$$
delimiter ;

​ 插入 film表记录的时候，会向film_text表中也插入相应的记录。

mysql> insert into film values(1001, 'title', 'description');
Query OK, 1 row affected (0.01 sec)

mysql> select * from film_text where film_id=1001 \G;
*************************** 1. row ***************************
    film_id: 1001
      title: title
description: description
1 row in set (0.00 sec)

​ 对于 INSERT INTO … ON DUPLICATE KEY UPDATE … 语句来说，触发触发器的顺序可能会造成疑惑。下面对 film表分别创建 BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 的触发器，然后插入记录，观察触发器的触发情况：

-- 创建BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 的触发器
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> select * from film;
+---------+-------+-------------+
| film_id | title | description |
+---------+-------+-------------+
|    1001 | title | description |
+---------+-------+-------------+

mysql> insert into film values (1002, 'title', 'description')
    -> on duplicate key
    -> update title='update title';
Query OK, 1 row affected (0.01 sec)

mysql> select * from tri_demo;
+---------------+
| note          |
+---------------+
| before insert |
| after insert  |
+---------------+

删除触发器

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

DROP TRIGGER [schema_name.]trigger_name

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

mysql> drop trigger test.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: 2020-09-28 21:05:04.64
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
....

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

mysql> desc information_schema.triggers;
....

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: 2020-09-28 21:05:04.64
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

触发器的使用

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

• 触发程序即不能调用将数据返回客户端的存储程序，也不使用采用 CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回给触发程序。也就是存储过程或者函数通过 OUT或者INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。

• 不能再触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANSACTION,COMMIT,ROLLBACK.

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

小结

​ 本章主要介绍了mysql 提供的视图、存储过程、函数、触发器的创建、维护等相关语法，也介绍了他们分别适用的场合。

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