封面来源：本文封面来源于 MySQL 官网，如有侵权，请联系删除。

本文参考：MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！

1. 视图的概述

1.1 常见的数据库对象

对象 描述
表 表是存储数据的逻辑单元，以行和列的形式存在，列是字段，行是记录
数据字典 即系统表，存放数据库相关信息的表。系统表中的数据通常由数据库系统维护，程序员不应该修改，只可查看
约束 执行数据校验的规则，用于保证数据完整性的规则
视图 一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引 用于提高查询性能，相当于书的目录
存储过程 用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数 用于完成一次特定的计算，具有一个返回值
触发器 相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

1.2 为什么要使用视图

视图既可以帮我们只使用表的一部分而不是所有的表，又可以针对不同的用户制定不同的查询视图。

比如，针对一个公司的销售人员，只想给他看部分数据，而不允许查看某些特殊的数据，比如采购的价格。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中不应该提供这个字段。

1.3 视图的理解

视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，是 SQL 中一个重要概念。

视图建立在已有表的基础上，视图赖以建立的这些表称为 基表 。

用户、虚拟表和数据表之间的关系

视图的创建和删除只影响视图本身，不影响对应的基表，但对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。

向视图提供数据内容的语句是 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句。

在数据库中，视图不会保存数据，数据被真正保存在数据表中。

视图，是向用户展示基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但在大型项目或数据表比较复杂的情况下，视图的价值就凸显出来了，它可以把经常查询的结果集放到虚拟表中，提高使用效率。

2. 视图的创建

2.1 创建的语法

简单来说就是在 CREATE VIEW 语句中嵌入子查询：

1
2
3
4
5
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

2.2 创建单表视图

创建视图：

1
2
3
4
5
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

查询视图：

1
SELECT * FROM empvu80;

部分查询结果如下：

empvu80视图查询结果

视图可以理解为在 SQL 查询语句的基础上进行的封装，基于 SQL 语句的结果集形成的一张虚拟表。

在创建视图时，如果没有在视图后面指定字段列表，那么视图中字段列表默认和 SELECT 语句中的字段列表一致。如果 SELECT 语句中给字段取了别名，那么视图中的字段名和别名相同。

2.3 创建多表联合视图

比如：

1
2
3
4
5
CREATE VIEW empview 
AS
SELECT employee_id emp_id, last_name NAME, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

再比如：

1
2
3
4
5
6
CREATE VIEW dept_sum_vu(name, minsal, maxsal, avgsal) 
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

利用视图可以对数据进行格式化，比如想要同时输出员工姓名和对应的部门名时，可以使用视图来完成数据的格式化：

1
2
3
4
5
CREATE VIEW emp_depart 
AS
SELECT CONCAT(last_name, '(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id;

2.4 基于视图创建视图

视图并不是只能基于 SQL 查询语句进行创建，还可以在已有视图的基础上创建新的视图。比如：

1
2
3
4
5
# 基于视图 empview 创建新视图 emp_depart_view
CREATE VIEW emp_depart_view
AS
SELECT CONCAT(NAME, '(',department_name,')') AS emp_dept
FROM empview;

3. 查看视图信息

查看数据库的表对象、视图对象

1
SHOW TABLES;

查看视图的结构

1
2
3
DESC 视图名称;
# 或者
DESCRIBE 视图名称;

查看视图的属性信息

1
2
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

比如查看 emp_depart_view 的属性信息：

视图emp_depart_view的属性信息

注意： \G 在 Navicat 中是不支持的，在 Navicat 使用命令时，老实用 ; 结尾。

根据上图信息显示，Comment 的值是 VIEW，表示这个表是视图，其他信息基本都是 NULL，说明这是一个虚拟表。

查看视图的详细定义信息

1
SHOW CREATE VIEW 视图名称;

4. 视图的其他操作

4.1 更新视图的数据

当对视图中的数据进行插入、更新、删除操作时，数据表中的数据也会发生变化。

视图中数据的更新

先创建一个视图：

1
2
3
4
5
CREATE VIEW emp_phone
AS
SELECT employee_id, phone_number
FROM employees
WHERE job_id = 'IT_PROG';

查询 emp_phone 视图中的数据：

emp_phone视图中的数据

对 employee_id 为 103 记录进行更新：

1
UPDATE emp_phone SET phone_number = '590.423.4566' WHERE employee_id = 103;

查询数据表中的数据：

视图修改后数据表中的数据

可以看到 employee_id 为 103 的记录的 phone_number 已被修改为 590.423.4566。

视图中数据的删除

在视图中删除 employee_id 为 104 的数据：

1
DELETE FROM emp_phone WHERE employee_id = 104;

在数据表中查询 employee_id 为 104 的数据，发现并不存在这条数据：

1
SELECT * FROM employees WHERE employee_id = 104;

4.2 不可更新的视图

并不是所有的视图都可以进行更新，要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。

当视图定义出现如下情况时，视图不支持更新操作：

  • 在定义视图的时候指定了 ALGORITHM = TEMPTABLE，视图将不支持 INSERT 和 DELETE 操作；
  • 视图中不包含基表中 所有 被定义为非空且未指定默认值的列，视图将不支持 INSERT 操作；
  • 在定义视图的 SELECT 语句中使用了 JOIN 联合查询，视图将不支持 INSERT 和 DELETE 操作；
  • 在定义视图的 SELECT 语句后的字段列表中使用了数学表达式或子查询，视图将不支持 INSERT，也不支持 UPDATE 使用了数学表达式、子查询的字段值；
  • 在定义视图的 SELECT 语句后的字段列表中使用 DISTINCT 、聚合函数、GROUP BY 、HAVING 、UNION 等关键词或函数，视图将不支持 INSERT、UPDATE、DELETE；
  • 在定义视图的 SELECT 语句中包含了子查询，而子查询中引用了 FROM 后面的表，视图将不支持 INSERT、UPDATE、DELETE；
  • 视图定义基于一个不可更新视图；
  • 常量视图。

虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据。对视图数据的更改都应该通过对实际数据表里数据的操作来完成。

4.3 修改视图

方式一：使用 CREATE REPLACE VIEW 子句修改视图

1
2
3
4
5
CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
AS 
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

CREATE VIEW 子句中各列的别名应和子查询中各列相对应。

方式二：使用 ALTER 关键字

语法如下：

1
2
3
ALTER VIEW 视图名称 
AS
查询语句;

4.4 删除视图

删除视图只是删除视图的定义，不会影响到基表的数据。其语法是：

1
DROP VIEW IF EXISTS 视图名称;

也可以一次性删除多个视图：

1
DROP VIEW IF EXISTS 视图名称1, 视图名称2, 视图名称3, ...;

比如：

1
DROP VIEW empvu80;

由于视图可以基于其他已有视图进行创建，基于视图 A、B 创建的视图 C，当视图 A 或视图 B 被删除后，会导致视图 C 的查询失败。此时需要手动删除视图 C，因为它已经无法被使用了。

5. 视图的优点与不足

5.1 视图的优点

优点一：操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需简单地操作视图即可，极大简化了开发人员对数据库的操作。

优点二：减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

优点三：数据安全

MySQL 将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表，这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。

MySQL 可以根据权限将用户对数据的访问限制在某些视图上，用户无需查询数据表，就可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

优点四：适应灵活多变的需求

当业务系统的需求发生变化后，如果需要改动数据表的结构，工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

优点五：能够分解复杂的查询逻辑

数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

5.2 视图的不足

当实际数据表的结构发生变更时，需要及时对相关的视图进行相应的维护。如果是嵌套的视图（在视图的基础上创建视图），维护会变得更加复杂，容易成为系统的潜在隐患。除此之外，创建视图的 SQL 查询可能会对字段进行重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

实际项目中，如果视图过多，会增加数据库的维护成本。

所以，在创建视图的时候，需要结合实际项目需求，综合考虑视图的优点和不足，才能正确并合理地使用视图。

6. 存储过程概述

6.1 存储过程的理解

存储过程，Stored Procedure，一组经过预先编译的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处

1、简化操作，提高了 SQL 语句的重用性，减少了开发程序员的压力；

2、减少操作过程中的失误，提高效率；

3、减少网络传输量（即客户端不需要把所有的 SQL 语句通过网络发给服务器） ；

4、减少了 SQL 语句暴露在网上的风险，间接提高了数据查询的安全性。

和视图、函数相比

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。存储过程和视图也存在不同，视图是虚拟表，通常不会对底层数据表直接进行操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

存储过程一旦被创建出来，使用它就像使用函数一样简单，直接调用存储过程名即可。相较于函数，存储过程是没有返回值的。

6.2 存储过程的分类

存储过程的参数类型可以是 IN、OUT 和 INOUT。因此，存储过程可以分为：

1、没有参数（无参数无返回）；

2、仅仅存在 IN 类型（有参数无返回）；

3、仅仅存在 OUT 类型（无参数有返回）；

4、既存在 IN 又存在 OUT（有参数有返回）；

5、仅仅存在 INOUT（有参数有返回）。

注意： IN、OUT 和 INOUT 可以在一个存储过程中存在多个。

7. 存储过程的使用

7.1 创建存储过程的语法

1
2
3
4
5
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型, ...)
[characteristics ...]
BEGIN 
	存储过程体
END

类似 Java 中的方法：

1
2
3
修饰符 返回类型 方法名(参数类型 参数名, ...){
    方法体;
}

参数名前 IN、OUT、INOUT 符号的含义

IN：表示当前参数为输入参数，即入参。存储过程只读取这个参数的值，如果没有显示定义 IN、OUT 或者 INOUT，默认为 IN；

OUT：当前参数为输出参数，即出参。存储过程执行完成后，调用这个存储过程的客户端或者应用程序可以读取到这个参数的值；

INOUT：当前参数既可以作为入参，也可以作为出参。

注意： 形参类型可以是 MySQL 数据库中的任意类型。

语法中 characteristics 的含义

characteristics 表示创建存储过程时指定的对存储过程的约束条件。其取值信息如下：

1
2
3
4
5
6
7
8
9
10
# 取值一
LANGUAGE SQL
# 取值二
[NOT] DETERMINISTIC
# 取值三
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
# 取值四
SQL SECURITY { DEFINER | INVOKER }
# 取值五
COMMENT 'string'

1、LANGUAGE SQL：存储过程执行体由 SQL 语句组成的，当前系统支持的语言为 SQL。

2、[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的，即每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果不是确定的，这种情况下，相同的输入可能会得到不同的输出。默认为 NOT DETERMINISTIC。

3、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用 SQL 语句的限制。

  • CONTAINS SQL（默认值）：当前存储过程的子程序包含 SQL 语句，但是并不包含读写数据的 SQL 语句；
  • NO SQL：表示当前存储过程的子程序中不包含任何 SQL 语句；
  • READS SQL DATA：表示当前存储过程的子程序中包含读数据的 SQL 语句；
  • MODIFIES SQL DATA：表示当前存储过程的子程序中包含写数据的 SQL 语句。

4、SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

  • DEFINER（默认值）：只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  • INVOKER：拥有当前存储过程的访问权限的用户能够执行当前存储过程。

5、COMMENT 'string'：注释信息，可以用来描述存储过程。

存储过程体中使用到的关键词

从给出的语法可以看到，存储过程体是放在 BEGIN...END 之间的，存储过程体中可以有多条 SQL 语句，如果仅仅只有一条 SQL 语句，可以忽略 BEGIN 和 END。

1、BEGIN...END：中间包含了多个语句，每个语句以 ; 为结束符。

2、DECLARE：用来声明变量，在 BEGIN...END 中间使用，而且需要在其他语句使用之前进行变量的声明。

3、SET：赋值语句，用于对变量进行赋值。

4、SELECT...INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

设置新的结束标记

语法：

1
DELIMITER 新的结束标记

由于 MySQL 默认的语句结束符号为分号 ;，为了避免与存储过程中存储过程体里的 SQL 语句结束符相冲突，需要使用 DELIMITER 改变存储过程的结束符。

比如：DELIMITER // 语句的作用是将 MySQL 的结束符设置为 //，并以 END // 结束存储过程。存储过程定义完毕后再使用 DELIMITER ; 恢复 MySQL 的默认结束符。

当使用 DELIMITER 命令时，应该避免使用反斜杠 \ 作为结束字符，因为它是 MySQL 的转义字符。

比如：

1
2
3
4
5
6
7
8
DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型, ...)
[characteristics ...]
BEGIN
    SQL语句1;
    SQL语句2;
END $

7.2 创建存储过程示例

示例一：创建存储过程 select_all_data()，查看 emps 表的所有数据。

1
2
3
4
5
6
7
8
DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN 
	SELECT * FROM emps;  
END $

DELIMITER ;

示例二：创建存储过程 avg_employee_salary()，返回所有员工的平均工资。

1
2
3
4
5
6
7
8
DELIMITER //

CREATE PROCEDURE avg_employee_salary()
BEGIN 
	SELECT AVG(salary) AS avg_salary FROM emps;
END //

DELIMITER ;

示例三：创建存储过程 show_max_salary()，用来查看 emps 表的最高薪资值。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE show_max_salary()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '查看最高薪资'
    
    BEGIN
    	SELECT MAX(salary) FROM emps;
    END //

DELIMITER ;

示例四：创建存储过程 show_min_salary()，查看 emps 表的最低薪资值，并将最低薪资通过 OUT 参数 ms 输出。

1
2
3
4
5
6
7
8
DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
    BEGIN
    	SELECT MIN(salary) INTO ms FROM emps;
    END //
    
DELIMITER ;

示例五：创建存储过程 show_someone_salary()，查看 emps 表的某个员工的薪资，并用 IN 参数 empname 输入员工姓名。

1
2
3
4
5
6
7
8
DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
    BEGIN 
    	SELECT salary FROM emps WHERE ename = empname;
    END //

DELIMITER ;

示例六：创建存储过程 show_someone_salary2()，查看 emps 表的某个员工的薪资，并用 IN 参数 empname 输入员工姓名，用 OUT 参数 empsalary 输出员工薪资。

1
2
3
4
5
6
7
8
DELIMITER //

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DOUBLE)
    BEGIN 
    	SELECT salary INTO empsalary FROM emps WHERE ename = empname;
    END //

DELIMITER ;

示例七：创建存储过程 show_mgr_name()，查询某个员工领导的姓名，并用 INOUT 数 empname 输入员工姓名，输出领导的姓名。

1
2
3
4
5
6
7
8
9
DELIMITER //

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
    BEGIN
        SELECT ename INTO empname FROM emps
        	WHERE eid = (SELECT MID FROM emps WHERE ename = empname);
    END //

DELIMITER ;

7.3 存储过程的调用格式

存储过程有多种调用方法。存储过程必须使用 CALL 语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，比如 CALL dbname.procname。

1
CALL 存储过程名(实参列表)

比如：

  • 调用 IN 模式的参数：
1
CALL sp1('值')；
  • 调用 OUT 模式的参数：
1
2
3
SET @name;
CALL sp1(@name);
SELECT @name;
  • 调用 INOUT 模式的参数：
1
2
3
SET @name = 值;
CALL sp1(@name);
SELECT @name;

7.4 调用存储过程示例

示例一

创建存储过程：

1
2
3
4
5
6
7
8
9
DELIMITER //

CREATE PROCEDURE CountProc(IN sid INT, OUT num INT)
    BEGIN
        SELECT COUNT(*) INTO num FROM fruits 
        WHERE s_id = sid;
    END //

DELIMITER ;

调用存储过程：

1
CALL CountProc(101, @num);

查看返回结果：

1
SELECT @num;

该存储过程返回了指定 s_id = 101 的水果商提供的水果种类，返回值存储在 num 变量中，使用 SELECT 查看。

示例二

创建存储过程，实现 1 + 2 + … + n 的累加运算。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
    DECLARE i INT;
    DECLARE sum INT;
    
    SET i = 1;
    SET sum = 0;
    WHILE i <= n DO
        SET sum = sum + i;
        SET i = i +1;
    END WHILE;
    SELECT sum;
END //
DELIMITER ;

调用存储过程：

1
CALL add_num(50);

调用存储过程add_num

如果使用的是 Navicat，那么在编写存储过程时，Navicat 会自动设置 DELIMITER 为其他符号，不需要再进行 DELIMITER 的操作。

7.5 存储过程的调试

在 MySQL 中，存储过程不像普通的编程语言（比如 C++、Java 等）那样有专门的集成开发环境。

因此要调试存储过程时，只能通过 SELECT 语句，把程序执行的中间结果查询出来，验证某一个 SQL 语句的正确性。逐步推进，依次验证完所有 SQL 语句的正确性，就可以完成对存储过程中所有操作的调试了

8. 存储函数的使用

8.1 存储函数的语法

MySQL 支持自定义函数，对自定义函数的调用与 MySQL 预定义的系统函数一样。

自定义函数的语法格式：

1
2
3
4
5
6
CREATE FUNCTION 函数名(参数名 参数类型, ...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体   # 函数体中肯定有 RETURN 语句
END

语法说明

1、参数列表：在存储过程中可以使用 IN、OUT 或 INOUT，而在存储函数中，总是默认为 IN。

2、RETURNS 关键字后紧跟存储函数的返回值类型，这是必须的，因此函数体中必须包含一个 RETURN value 语句。

3、 characteristics 表示创建存储函数时对其的约束，取值与创建存储过程时一样，不在赘述。

4、函数体也可以用 BEGIN...END 来表示 SQL 代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN...END。

8.2 定义与调用

在 MySQL 中，存储函数的使用方法与 MySQL 内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与 MySQL 内部函数是一个性质的。只不过存储函数是用户自己定义的，而内部函数是 MySQL 的开发者定义的。

因此它们的调用方式都是：

1
SELECT 函数名(实参列表);

示例一

创建存储函数，名称为 email_by_name()，参数定义为空，该函数返回 Abel 的 email 信息，数据类型为字符串型。

1
2
3
4
5
6
7
8
9
10
11
DELIMITER //

CREATE FUNCTION email_by_name() 
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN 
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;

调用：

1
SELECT email_by_name();

存储函数email_by_name的调用结果

示例二

创建存储函数，名称为 email_by_id()，参数传入 emp_id，该函数返回指定 emp_id 对应员工的 email 信息，数据类型为字符串型。

1
2
3
4
5
6
7
8
9
10
11
DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT) 
RETURNS VARCHAR(25) 
DETERMINISTIC 
CONTAINS SQL 
BEGIN 
	RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END // 

DELIMITER ;

调用：

1
2
SET @emp_id = 102;
SELECT email_by_id(102);

存储函数email_by_id的调用结果

示例三

创建存储函数 count_by_id()，参数传入 dept_id，该函数返回 dept_id 对应部门的员工人数，数据类型为整型。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
    LANGUAGE SQL
    NOT DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT '查询某一部门的人数'
BEGIN
	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //

DELIMITER ;

调用：

1
2
SET @dept_id = 50;
SELECT count_by_id(@dept_id);

存储函数count_by_id的调用结果

创建存储函数时的出错

若在创建存储函数时出现以下错误信息：

you might want to use the less safe log_bin_trust_function_creators variable

有两种解决方法：

1、加上必要的函数特性 [NOT] DETERMINISTIC 和 {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}；

2、执行以下语句：

1
SET GLOBAL log_bin_trust_function_creators = 1;

8.3 与存储过程的对比

关键字 调用语法 返回值 应用场景
存储过程 PROCEDURE CALL 存储过程() 可以理解为 0 个或多个 常用于更新
存储函数 FUNCTION SELECT 存储函数() 只能是一个 常用于查询结果是一个值并需要返回时

此外，存储函数可以在查询语句中使用，存储过程不行。 但存储过程的功能更加强大，能够执行对表的操作（比如创建表，删除表等）和事务操作，存储函数并不具备这些功能。

9. 查看、修改与删除

9.1 查看

创建完存储过程和存储函数后，怎么知晓已经创建成功呢？

MySQL 存储了存储过程和存储函数的状态信息，用户可以使用 SHOW STATUS 语句或 SHOW CREATE 语句来查看，也可直接从系统的 information_schema 数据库中查询。

使用 SHOW CREATE 语句查看

基本语法：

1
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或存储函数名;

比如：

1
2
3
4
# 在命令行中使用
SHOW CREATE FUNCTION email_by_name \G
# 在 Navicat 中使用
SHOW CREATE FUNCTION email_by_name;

使用SHOW-CREATE查看存储函数信息

使用 SHOW STATUS 语句查看

基本语法：

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

这将返回子程序的特征，比如数据库、名字、类型、创建者、创建时间和修改时间。

[LIKE 'pattern']：匹配存储过程或存储函数的名称，可以省略。当省略不写时，会列出 MySQL 数据库中存在的所有存储过程或存储函数的信息。

比如：

1
2
3
4
# 在命令行中使用
SHOW FUNCTION STATUS LIKE 'email_by_%' \G
# 在 Navicat 中使用
SHOW FUNCTION STATUS LIKE 'email_by_%';

上述语句将查出两个存储函数的信息，分别是 email_by_id 和 email_by_id。

从 information_schema.Routines 表中查看

MySQL 中的存储过程和存储函数的信息存储在 information_schema 数据库下的 Routines 表中，可以通过查询该表的记录来查询存储过程和函数的信息。语法如下：

1
2
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

如果在 MySQL 数据库中存在存储过程和存储函数名称相同的情况，最好指定 ROUTINE_TYPE 查询条件来指明查询的是存储过程还是存储函数。

比如：

1
2
3
4
5
6
# 在命令行中使用
SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME = 'count_by_id' AND ROUTINE_TYPE = 'FUNCTION' \G
# 在 Navicat 中使用
SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME = 'count_by_id' AND ROUTINE_TYPE = 'FUNCTION';

9.2 修改

修改存储过程或存储函数，不影响存储过程或函数功能，只是修改相关特性，可以使用 ALTER 语句实现。语法如下：

1
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic 指定存储过程或存储函数的特性，其取值信息与创建存储过程、存储函数时的取值信息略有不同。

1
2
3
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
  • CONTAINS SQL，表示子程序包含 SQL 语句，但不包含读或写数据的语句。
  • NO SQL，表示子程序中不包含 SQL 语句。
  • READS SQL DATA，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行；INVOKER 表示调用者可以执行。
  • COMMENT 'string'，表示注释信息。

示例一

修改存储过程 count_by_id 的定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行：

1
2
3
ALTER FUNCTION count_by_id 
MODIFIES SQL DATA 
SQL SECURITY INVOKER;

查看修改后的信息：

1
2
3
SELECT specific_name, sql_data_access, security_type 
FROM information_schema.`ROUTINES` 
WHERE routine_name = 'count_by_id' AND routine_type = 'FUNCTION';

修改count_by_id后的信息

从查询的结果可以看出，访问数据的权限已经变成 MODIFIES SQL DATA，安全类型已经变成 INVOKER。

示例二

修改存储函数 count_by_id 的定义，将读写权限改为 READS SQL DATA，并加上注释信息 Number of people in a department：

1
2
3
ALTER FUNCTION count_by_id 
READS SQL DATA
COMMENT 'Number of people in a department';

再次修改count_by_id后的信息

9.3 删除

可以使用 DROP 语句来删除存储过程和存储函数：

1
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果存储程序或存储函数不存在，它可以防止发生错误，产生一个用 SHOW WARNINGS 查看的警告。

比如：

1
DROP FUNCTION count_by_id;

10. 使用存储过程的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，有些公司对于大型项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？

10.1 存储过程的优点

1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，提升了 SQL 的执行效率。

2、可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。

3、存储过程的安全性强。在创建存储过程时可以设置对用户的使用权限，和视图一样具有较强的安全性。

4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样可以减少网络传输量。

5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用多条SQL 语句并可能还要连接多次数据库才能完成的操作，在封装成存储过程后，只需要连接一次即可。

10.2 存储过程的缺点

基于上面这些优点，不少大公司都要求在大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？

【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

存储过程的缺点也是显而易见的：

1、可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 或 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，初期开发和后续维护都不容易，并且鲜有的一些可以对存储过程进行调试的第三方工具是需要收费的。

3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新时会很麻烦。

4、不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，并增加数据库的压力。

10.3 小结

存储过程既方便，又有局限性。尽管不同的公司对使用存储过程的态度不一，但是对于开发人员来说，掌握存储过程都是必备的技能之一。