MySQL 5+ 特性

禁止码迷，布布扣，豌豆代理，码农教程，爱码网等第三方爬虫网站爬取！

目录

• 视图
  • 虚拟的表
  • 视图使用规则
  • 视图基操
    • 视图创建
    • 创建语句的查看
    • 删除视图
  • 复杂联结的视图
  • 视图格式化检索数据
  • 视图的过滤
  • 视图封装计算字段
• 存储过程
  • 组织复杂的功能
  • 存储过程基操
    • 创建
    • 执行
    • 删除
  • 使用参数
  • 传入参数
  • 实例
    • IF 语句
    • 结合选择结构
  • 检查存储过程
• 游标
  • 简单回顾
  • 使用游标
  • 游标操作
    • 创建游标
    • 打开游标
    • 关闭游标
    • 使用游标数据
    • 重复操作
    • 条件处理
  • 实例
• 触发器
  • 创建触发器
  • INSERT 触发器
  • DELETE 触发器
  • UPDATE 触发器
• 参考资料

视图

虚拟的表

假设我需要从 2 张表中提取信息，一种做法就是使用联结，并结合 WHERE 子句来过滤。不过如果这样的联结表我需要反复调用，并且我可能需要根据不同的条件进行过滤，这个时候代码量就会比较大，移植性比较差。可以直接将联结表制作为一张新的表，但是就需要为这张表的生存期进行考虑。总而言之，上述方法可能都不是很好的方法。
MySQL 5 后的版本支持视图，所谓视图就是一个虚拟的表，这个虚拟的表是针对动态检索数据时使用的。或者说，视图中不包含表中的任何列和数据，本质上是一个 SQL 查询，视图返回的数据就是基于这个查询得到的数据。对于表中数据的修改，由于本质是查询，返回的也会是修改后的数据。视图用法有什么优点呢？使用视图可以简化复杂的 SQL 语句的编写，对于语句的重用提供了方便。视图将使用表的一部分，而不是整张表，因此对于数据的保护和权限的赋予也较为方便，使用视图是也可以修改数据呈现的格式。

视图使用规则

1. 视图名是唯一的，不能和其他视图或表共用表名；
2. 视图的创建没有数量限制，视图的创建需要有一定的权限；
3. 视图可以嵌套使用，在这个时候就会产生更多的开销，特别是在使用了嵌套和复杂的过滤条件；
4. 视图创建时可以使用 ORDER BY，使其支持排序；
5. 视图不能支持索引、触发器、默认值；

视图基操

视图创建

CREATE VIEW viewname AS

创建语句的查看

SHOW CREATE VIEW viewname;

删除视图

DROP VIEW viewname；

• 视图允许各种更新操作，但是**分组、联结、子查询、合并、聚集函数和 DISTINCT 这些操作的视图不能更新。不过视图的主要功能是查询，更新操作较少使用。

复杂联结的视图

视图可以将联结了多个表的查询，封装成为一个动态数据查询虚拟表。

视图格式化检索数据

对于检索数据的格式化输出，可以封装成为一个动态数据查询虚拟表。

视图的过滤

可以将过滤条件封装到视图当中。

视图封装计算字段

存储过程

组织复杂的功能

实际操作中会出现，对于一些操作需要多条语句的配合才能完成。这里可以用编程中的顺序结构来类比，当我每次需要完成这个操作都需要把完整的 SQL 语句写一遍，这么写不仅麻烦，还容易出错。我们在编程中是怎么优化这个问题的？就是将固定操作的代码块封装成一个函数，然后通过参数的传递实现函数调用。所谓存储过程就与编程中的函数有类似之处，是一条或多条 MySQL 语句的集合，也可以当作是批处理文件。存储过程的语句编写会稍微复杂一些，同时也会有权限问题，但是存储过程的优势还是很明显的，在于简捷、安全、性能好 3 个方面：

1. 封装一段代码，使得操作变得简捷；
2. 规避了反复建立某种操作容易出错的问题，保证了数据的完整性；
3. 操作的健壮性、移植性增强，修改功能可以通过修改存储过程实现；
4. 存储过程的执行速度比 SQL 语句更快，使得效率提高；
5. 存储过程可以实现的功能更为灵活。

存储过程基操

创建

定义存储过程要使用 CREATE PROCEDURE 定义，在语句后面要给出存储过程名，若可以接受参数就需要在括号内罗列出来。语法框架为：

CREATE PROCEDURE name(variable1，variable2……)
BEGIN
    --MySQL 语句
END；

创建存储过程时需要指定哪些变量需要输入和输出，用 OUT 关键字指定，输入用 IN 关键字指定，若同时需要输入和输出，用 INOUT 关键字指定。

• 为了便于在命令提示符中调试，可以用以下代码修改语句的结束符。

DELIMITER //

执行

执行存储过程的语句为 CALL name()，如果需要传参就要在括号之间填充参数。

删除

存储过程将在服务器上供调用，我们可以把建立的存储过程删掉，使用 DROP PROCEDURE 删除语句实现。

若指定的存储过程不存在会报错，可以使用下列语句实现异常处理。

DROP PROCEDURE IF EXISTS

使用参数

在这里给出 3 个参数，这些参数是此存储过程所接受的，每个参数必须指定数据类型，例如下面就使用了十进制数字。OUT 就说明这些参数是需要返回给调用者的，操作完值之后就使用 INTO 将值注入变量之中。而对于存储过程的运行的代码就封装在 BEGIN 和 END 语句之内。

此时用 CALL 语句调用存储过程后，返回的变量将会被调出来。MySQL 中的变量都是以 @开始的，由于存储过程有 3 个参数，因此也必须传 3 个参数。

这个时候就使用 SELECT 语句依次查看变量。

• 存储过程中的参数的数据类型和表中对应数据的数据类型应当是相同的。同时数据集是不被认可的数据类型，因此不能够使用一个参数集成多个行和列。

传入参数

创建时需要用关键字 IN 指定传入的参数。

传参就要按照参数表填写变量，这个顺序不能有错。

实例

IF 语句

在存储过程中引入分支处理的代码框架为：

IF expression1 THEN 
   --MySQL 语句段
ELSEIF expression2 THEN
   --MySQL 语句段
ELSE
   --MySQL 语句段
END IF;

结合选择结构

这个实例实现的功能就更强了，那么这里解释一下选择结构的功能。IF 语句检查变量是都是真，真的情况下用一个 SELECT 语句将值注入变量中，若为假则用另一个 SELECT 语句将值注入变量。

• COMMENT关键字用于给存储过程包含一个值，这个值会在检查语句时被回显。

现在传入 2 组参数进去试试看。

检查存储过程

使用以下语句可以检查创建存储过程的语句。

SHOW CREATE PROCUDURE name;

以下语句可以查看存储过程的创建信息。

SHOW PROCUDURE STATUS LIKE name;

游标

简单回顾

我们来回顾一下之间接触的内容：一般检索的就是直接按照一定的条件，将所有符合的数据组合成一个结果集返回；计算字段可以对检索的结果集进行整合，或者是进行运算；聚集函数可以对数据集，进行整体的整合分析；子查询以一次检索的数据为基准，对这些结果进行再一次的搜索；联结可以将不同表中有关联的数据，整合到一个结果集中；组合查询可以将多个检索的结果，组合到一个结果集中；全文本搜索是一种智能的搜索，可以有效地对文本数据进行检索。
以上就是我们之前看过的知识了，不过我们发现这些操作都是对于一个结果集合而言的。但是在现实操作中我们可能需要对数据进行逐行处理，这个要求在上述的操作中都难以实现，即使可以强行实现也会有严重的性能问题。

使用游标

对于逐行操作的需求，可以使用游标 (cursor)来实现。游标是一个存储在 MySQL 服务器上的数据库查询，与 SELECT 不同的是右边本身就是通过检索构建的一个新的可调用的检索集合。游标可以理解为 C/C++ 中的指针，对于某一个结构可以用指针去访问值，游标的原理就与之类似。对于游标的使用，有以下的操作顺序：

1. 定义游标，并且封装建立结果集所使用的 SELECT 语句；
2. 打开游标；
3. 使用封装的 SELECT 检索数据；
4. 在游标填充数据的情况下，按需对行进行操作；
5. 关闭游标；

游标操作

创建游标

在 MySQL 中，游标只能应用于存储过程和函数，因此对于游标的创建，首先还是得先拥有一个存储过程。拥有存储过程之后就使用 DECLARE 语句定义游标，这些语句就放到存储过程的 BEGIN 和 END 之间。代码框架如下：

DECLARE cursor_name CURSOR
FOR
SELECT 语句;

打开游标

使用游标时必须要先打开，打开之后就可以对数据进行浏览了，打开语句如下：

OPEN cursor_name;

关闭游标

游标使用完毕之后要把其关掉，关闭时占用的内存和资源会被自动释放。游标被关闭之后就不能再使用，但是可以再次被打开。语句为：

CLOSE cursor_name;

使用游标数据

使用游标数据时要用到 FETCH 语句来逐行访问，需要给出访问所需的列，这样便于游标去定向访问，同时这个语句还会促使游标向下一行移动。语句为：

FETCH row_name INTO variable;

这个语句可以用来检索指定的行，并且注入到一个变量当中。

重复操作

FETCH 语句可以将游标推进到下一行，但是它只进行一次操作，并不能实现逐行处理。这里可以使用重复语句 REPEAT：

REPEAT
    MySQL 代码
UNTIL 条件语句 END REPEAT;

这个语句可以重复执行中间的代码，直到条件语句中的条件为真之后结束。

条件处理

DECLARE CONTINE HANDLER 表达式 1 SET 表达式 2：

这段代码的作用是定义一个 CONTINE HANDLER，这个的作用是当表达式 1 的条件出现时，将执行表达式 2 的语句。用这个语句可以实现条件的变更。

实例

下面使用上述的所有操作，给出相对完整的右边应用实例。

启用存储过程，并对其内容进行查看。

同时也可以去查看数据库，会发现多了一张新建的表。

触发器

创建触发器

所谓触发器，就是在某一个操作发生时，我希望有某些语句会随之自动执行，就可以使用这个功能。触发器将会对 DELETE、INSERT、UPDATE 语句做出响应，执行另一个操作。当创建触发器时，要保证每个数据库中的触发器名是唯一的，虽然不同的表允许拥有同名的触发器，但是这种习惯还是要养成。创建时需要给出以下信息：

1. 触发器名；
2. 触发器关联的表；
3. 触发器响应的语句；
4. 触发器执行的时机。

要创建触发器，就使用 CREATE TRIGGER 语句来光剑，如果要删除就使用 DROP TRIGGER 语句。由于每个表每个事件每次只允许一个触发器，因此每个表只能支持 DELETE、INSERT、UPDATE 3 种语句的 BEFORE 和 AFTER 2 种一共 6 个触发器。一般来说 BEFORE 型用于数据验证和净化，保证插入的是所需的数据。
不过 MySQL 5+ 的触发器总体功能较弱，不支持 CALL 语句，即不能够封装入存储过程。同时触发器的创建需要安全权限，不过使用却需要，因为是自动进行的，这也就说明了这个操作是透明的，且与客户机无关。触发器的一个应用就是进行审计跟踪，能够知晓数据的变化。

INSERT 触发器

INSERT 触发器会在 INSERT 语句执行前或执行后被启动，在触发器的代码内可以引用一个 NEW 虚拟表，访问被插入的行。对于设置为自动增量的列，虚拟表可以在操作之前包含一个 0，然后操作时自动增量也会被自动执行。

在这里，每当 INSERT 语句被执行，就会将一个字符串注入到一个变量中。

所以在没做 INSERT 操作时，这个变量为 NULL。

执行一次任意 INSERT 操作，变量的值被修改。

DELETE 触发器

DELETE 触发器内可以引用一个 OLD 虚拟表，访问被删除的行，表中的值都是只读的。这个触发器就可以吧 OLD 保存的被删除数据添加到一个新的表中。

UPDATE 触发器

UPDATE 触发器内可以引用一个 OLD 虚拟表，访问被修改前的行，引用一个 NEW 虚拟表访问被更新的行。在 BEFORE UPDATE 触发器中 NEW 的值可能被更新。

参考资料

《MySQL Crash Course》[英] Ben Forta 著，刘晓霞 钟鸣 译，人民邮电出版社
mysql游标的用法及作用
学习MySQL出现问题Not allowed to return a result set from a trigger