MySQL存储过程从入门到精通：提升数据库效率与安全性

引言

通常情况下，在处理关系型数据库时，你会在应用程序代码中直接发出各种结构化查询语言（SQL）查询来检索或操作数据，例如SELECT、INSERT、UPDATE或DELETE。这些语句直接作用于和操作底层数据库表。如果多个应用程序访问同一个数据库时需要使用相同的语句或语句组，它们通常会在各个应用程序中重复出现。

MySQL，与许多其他关系型数据库管理系统类似，支持使用存储过程。存储过程有助于将一个或多个SQL语句分组，并在一个通用名称下重复使用，将常见的业务逻辑封装在数据库内部。应用程序可以调用这样的过程来以一致的方式检索或操作数据。

通过使用存储过程，你可以为常见任务创建可重用的例程，供多个应用程序使用，提供数据验证，或者通过限制数据库用户直接访问底层表和发出任意查询来提供额外的数据访问安全层。

在本教程中，你将学习什么是存储过程，以及如何创建返回数据并使用输入和输出参数的基本存储过程。

先决条件

要遵循本指南，你需要一台运行基于SQL的关系型数据库管理系统（RDBMS）的计算机。本指南中的说明和示例已在以下环境中验证：

• 一台运行Ubuntu 20.04的服务器，拥有具有管理权限的非root用户，并配置了UFW防火墙，如我们的Ubuntu 20.04初始服务器设置指南中所述。
• 服务器上已安装并安全配置MySQL，如《如何在Ubuntu 20.04上安装MySQL》所述。本指南已通过非root MySQL用户验证，该用户是按照第3步中描述的过程创建的。
• 对执行SELECT查询以从数据库检索数据有基本了解，如我们的《如何在SQL中从表中选择行》指南所述。

你还需要一个空数据库，你可以在其中创建表来演示存储过程的使用。我们鼓励你阅读以下“连接到MySQL并设置示例数据库”部分，了解连接到MySQL服务器和创建本指南中示例所用测试数据库的详细信息。

连接到MySQL并设置示例数据库

在本节中，你将连接到MySQL服务器并创建一个示例数据库，以便你可以遵循本指南中的示例。

在本指南中，你将使用一个虚构的汽车收藏。你将存储有关当前拥有的汽车的详细信息，包括它们的品牌、型号、制造年份和价值。

如果你的SQL数据库系统在远程服务器上运行，请从本地机器通过SSH连接到你的服务器：

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将sammy替换为你的MySQL用户帐户名称：

mysql -u sammy -p

创建一个名为procedures的数据库：

CREATE DATABASE procedures;

如果数据库创建成功，你将收到如下输出：

Output
Query OK, 1 row affected (0.01 sec)

要选择procedures数据库，请运行以下USE语句：

USE procedures;

你将收到以下输出：

Output
Database changed

选择数据库后，你可以在其中创建示例表。cars表将包含数据库中汽车的简化数据。它将包含以下列：

这是文章《如何在MySQL中使用存储过程》的第2部分（共8部分）。

首先，我们定义cars表的列结构：

• make (品牌): 使用varchar数据类型，最大长度100个字符，存储每辆车的品牌信息。
• model (型号): 使用varchar数据类型，最大长度100个字符，存储汽车的型号名称。
• year (年份): 使用int数据类型，存储汽车的制造年份，用于表示数值。
• value (价值): 使用decimal数据类型，总共10位数字，其中小数点后保留2位，存储汽车的价值。

使用以下命令创建示例表：

  
CREATE TABLE cars (
  
  
    make varchar(100),
  
  
    model varchar(100),
  
  
    year int,
  
  
    value decimal(10, 2)
  
  
);
  

如果看到以下输出，则表示表已成功创建：

Output
Query OK, 0 rows affected (0.00 sec)

接下来，通过运行以下INSERT INTO操作，向cars表中加载一些示例数据：

  
INSERT INTO cars
  
  
VALUES
  
  
('Porsche', '911 GT3', 2020, 169700),
  
  
('Porsche', 'Cayman GT4', 2018, 118000),
  
  
('Porsche', 'Panamera', 2022, 113200),
  
  
('Porsche', 'Macan', 2019, 27400),
  
  
('Porsche', '718 Boxster', 2017, 48880),
  
  
('Ferrari', '488 GTB', 2015, 254750),
  
  
('Ferrari', 'F8 Tributo', 2019, 375000),
  
  
('Ferrari', 'SF90 Stradale', 2020, 627000),
  
  
('Ferrari', '812 Superfast', 2017, 335300),
  
  
('Ferrari', 'GTC4Lusso', 2016, 268000);
  

上述INSERT INTO操作将向表中添加十辆示例跑车，包括五款保时捷（Porsche）和五款法拉利（Ferrari）车型。以下输出表明所有行都已成功添加：

输出
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

至此，您已准备好继续学习本指南的其余部分，并开始在SQL中使用存储过程。

存储过程简介

MySQL以及许多其他关系型数据库系统中的存储过程是命名对象，它们包含一个或多个指令，当被调用时，数据库会按顺序执行这些指令。最基本的例子是，存储过程可以将一个常用语句保存为一个可重用的例程，例如使用常用筛选条件从数据库中检索数据。例如，您可以创建一个存储过程来检索在过去给定月份内下过订单的在线商店客户。在最复杂的场景中，存储过程可以代表描述复杂业务逻辑的广泛程序，用于构建健壮的应用程序。

存储过程中的指令集可以包括常见的SQL语句，例如返回或操作数据的SELECT或INSERT查询。此外，存储过程还可以使用：

• 传递给存储过程或通过其返回的参数。
• 声明变量以直接在过程代码中处理检索到的数据。
• 条件语句，允许根据特定条件（例如IF或CASE指令）执行存储过程代码的某些部分。
• 循环，例如WHILE、LOOP和REPEAT，允许多次执行代码的某些部分，例如对检索到的数据集中的每一行。
• 错误处理指令，例如向访问过程的数据库用户返回错误消息。
• 调用数据库中的其他存储过程。

当通过名称调用过程时，数据库引擎会按照定义，逐条执行指令。

数据库用户必须拥有适当的权限才能执行给定过程。这种权限要求提供了一层安全性，禁止直接访问数据库，同时允许用户访问保证安全执行的单个过程。

存储过程直接在数据库服务器上执行，在本地执行所有计算，并在完成后才将结果返回给调用用户。

如果您想更改过程行为，可以在数据库中更新过程，使用它的应用程序将自动获取新版本。所有用户将立即开始使用新的过程代码，而无需调整其应用程序。

以下是用于创建存储过程的SQL代码的一般结构：

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
    instruction_1;
    instruction_2;
    . . .
    instruction_n;
END //
DELIMITER ;

此代码片段中的第一条和最后一条指令分别是 DELIMITER // 和 DELIMITER ;。通常，MySQL使用分号 (;) 作为语句的分隔符，指示语句的开始和结束。如果在MySQL控制台中执行多条用分号分隔的语句，它们将被视为独立的命令，并依次独立执行。然而，存储过程可以包含多条命令，这些命令在存储过程被调用时会按顺序执行。这在尝试告诉MySQL创建新过程时会带来困难。数据库引擎会在存储过程体中遇到分号，并认为它应该停止执行当前语句。在这种情况下，预期的语句是整个过程创建代码，而不是过程内部的单个指令，因此MySQL会误解您的意图。

为了解决这个限制，您可以使用 DELIMITER 命令，在 CREATE PROCEDURE 调用期间，将分隔符从 ; 临时更改为 //。这样，存储过程体内的所有分号都将原样传递给服务器。在整个过程创建完成后，通过最后的 DELIMITER ; 命令将分隔符改回 ;。

创建新过程的核心代码是 CREATE PROCEDURE 调用，后跟过程的名称：示例中的 procedure_name。过程名称后是可选的参数列表，这些参数将被过程接受。最后一部分是过程体，它被包含在 BEGIN 和 END 语句中。内部是过程代码，可以包含单个SQL语句（例如 SELECT 查询）或更复杂的代码。

END 命令以 // 结尾，这是一个临时分隔符，而不是典型的分号。

在下一节中，您将创建一个不带参数的基本存储过程，其中包含一个简单的查询。

创建不带参数的存储过程

在本节中，您将创建第一个存储过程，它封装了一个简单的SQL SELECT 语句，用于返回按品牌和价值降序排列的汽车列表。

首先执行您将要使用的 SELECT 语句：

SELECT * FROM cars ORDER BY make, value DESC;

数据库将返回 cars 表中的汽车列表，首先按品牌排序，然后在同一品牌内按价值降序排序：

输出
+---------+---------------+------+-----------+
| make    | model         | year | value     |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo    | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso     | 2016 | 268000.00 |
| Ferrari | 488 GTB       | 2015 | 254750.00 |
| Porsche | 911 GT3       | 2020 | 169700.00 |
| Porsche | Cayman GT4    | 2018 | 118000.00 |
| Porsche | Panamera      | 2022 | 113200.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
| Porsche | Macan         | 2019 |  27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)

最有价值的法拉利位于列表顶部，而价值最低的保时捷出现在底部。

假设此查询将频繁用于多个应用程序或由多个用户使用，并且您希望确保每个人都使用完全相同的方式对结果进行排序。为此，您希望创建一个存储过程，将该语句保存为一个可重用的命名过程。

要创建此存储过程，请执行以下代码片段：

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
    SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

正如上一节所述，第一个和最后一个命令（DELIMITER // 和 DELIMITER ;）告诉 MySQL 在存储过程创建期间，停止将分号字符视为语句分隔符。

CREATE PROCEDURE SQL 命令后面跟着存储过程名称 get_all_cars，您可以根据存储过程的功能来定义它。在存储过程名称之后，有一对括号 ()，您可以在其中添加参数。在这个例子中，存储过程不使用参数，所以括号是空的。然后，在定义存储过程代码块开始和结束的 BEGIN 和 END 命令之间，之前使用的 SELECT 语句被原样写入。

更新用户权限后，以 root 身份退出，然后以该用户身份重新登录，并重新运行 CREATE PROCEDURE 语句。

您可以在《存储例程和 MySQL 权限》文档中了解更多关于将存储过程权限应用于数据库用户的信息。

数据库将返回成功消息：

输出
Query OK, 0 rows affected (0.02 sec)

get_all_cars 存储过程现在已保存到数据库中，当被调用时，它将按原样执行已保存的语句。

要执行已保存的存储过程，您可以使用 CALL SQL 命令，后跟存储过程名称。尝试运行新创建的存储过程，如下所示：

CALL get_all_cars;

存储过程名称 get_all_cars 是您使用该存储过程所需的全部。您不再需要手动输入之前使用的 SELECT 语句的任何部分。数据库将显示结果，就像之前运行 SELECT 语句的输出一样：

输出
+---------+---------------+------+-----------+
| make    | model         | year | value     |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo    | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso     | 2016 | 268000.00 |
| Ferrari | 488 GTB       | 2015 | 254750.00 |
| Porsche | 911 GT3       | 2020 | 169700.00 |
| Porsche | Cayman GT4    | 2018 | 118000.00 |
| Porsche | Panamera      | 2022 | 113200.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
| Porsche | Macan         | 2019 |  27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

您现在已成功创建了一个不带任何参数的存储过程，该过程以特定方式返回 cars 表中的所有汽车。您可以在多个应用程序中使用该存储过程。

在下一节中，您将创建一个接受参数的存储过程，以根据用户输入更改存储过程的行为。

创建带有输入参数的存储过程

在本节中，您将在存储过程定义中包含输入参数，以允许执行存储过程的用户向其传递数据。例如，用户可以提供查询过滤器。

之前创建的 get_all_cars 存储过程始终从 cars 表中检索所有汽车。让我们创建另一个存储过程来查找特定制造年份的汽车。为此，您将在存储过程定义中定义一个命名参数。

运行以下代码：

这是文章《如何在MySQL中使用存储过程》的第6部分（共8部分）。

DELIMITER //
CREATE PROCEDURE get_cars_by_year(
    IN year_filter int
)
BEGIN
    SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

与上一节的存储过程创建代码相比，这里有几处改动。

首先，存储过程的名称是 get_cars_by_year，它描述了该过程的功能：根据生产年份检索汽车。

之前为空的括号现在包含一个参数定义：IN year_filter int。IN 关键字告诉数据库，该参数将由调用用户传递到存储过程中。year_filter 是参数的任意名称，您将在存储过程代码中使用它来引用该参数。最后，int 是数据类型。在本例中，生产年份表示为数值。

在存储过程名称后定义的 year_filter 参数出现在 SELECT 语句的 WHERE year = year_filter 子句中，用于根据生产年份过滤 cars 表。

数据库将再次返回成功消息：

Output
Query OK, 0 rows affected (0.02 sec)

尝试不传递任何参数来执行存储过程，就像您之前做的那样：

CALL get_cars_by_year;

MySQL 数据库将返回一个错误消息：

Error message
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0

这次，存储过程需要提供一个参数，但没有提供。要调用带参数的存储过程，您可以在括号内按存储过程期望的相同顺序提供参数值。要检索 2017 年生产的汽车，请执行：

CALL get_cars_by_year(2017);

现在，调用的存储过程将正确执行并返回该年份的汽车列表：

Output
+---------+---------------+------+-----------+
| make    | model         | year | value     |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster   | 2017 |  48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

在这个例子中，您学习了如何将输入参数传递给存储过程，并在存储过程内部的查询中使用它们来提供过滤选项。

在下一节中，您将使用输出参数来创建一次运行返回多个不同值的存储过程。

创建带输入和输出参数的存储过程

在前面两个例子中，您创建的存储过程都调用了 SELECT 语句来获取结果集。但在某些情况下，您可能需要一个存储过程，它能一次性返回多个不同的值，而不是单个查询的单个结果集。

假设您想创建一个存储过程，它能提供给定年份汽车的汇总信息，包括收藏中汽车的数量及其市场价值（最小值、最大值和平均值）。

为此，您可以在创建新的存储过程时使用 OUT 参数。与 IN 参数类似，OUT 参数也有与其关联的名称和数据类型。然而，它们不是将数据传递给存储过程，而是可以由存储过程填充数据，以将值返回给调用用户。

创建一个 get_car_stats_by_year 存储过程，它将使用输出参数返回给定生产年份汽车的汇总数据：

DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
    IN year_filter int,
    OUT cars_number int,
    OUT min_value decimal(10, 2),
    OUT avg_value decimal(10, 2),
    OUT max_value decimal(10, 2)
)
BEGIN

    SELECT COUNT(*), MIN(value), AVG(value), MAX(value)

    INTO cars_number, min_value, avg_value, max_value

    FROM cars

    WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

这次，除了用于按生产年份过滤汽车的IN参数year_filter外，括号块中还定义了四个OUT参数。cars_number参数的数据类型为int，将用于返回集合中汽车的数量。min_value、avg_value和max_value参数表示市场价值，并定义为decimal(10, 2)类型（类似于cars表中的value列）。它们将用于返回集合中最便宜和最昂贵的汽车信息，以及所有匹配汽车的平均价格。

SELECT语句使用SQL数学函数从cars表中查询四个值：COUNT用于获取汽车总数，MIN、AVG和MAX用于从value列中获取最小值、平均值和最大值。

为了告诉数据库查询结果应存储到存储过程的输出参数中，这里引入了一个新关键字INTO。在INTO关键字之后，列出了与检索到的数据对应的四个过程参数的名称。这样，MySQL会将COUNT(*)的值保存到cars_number参数中，将MIN(value)的结果保存到min_value参数中，依此类推。

数据库将确认存储过程创建成功：

输出
Query OK, 0 rows affected (0.02 sec)

现在，通过执行以下命令来运行新创建的存储过程：

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

这四个新参数以@符号开头。它们是MySQL控制台中的局部变量名，可用于临时存储数据。当您将它们传递给刚刚创建的存储过程时，存储过程会将值插入到这些变量中。

数据库将响应：

输出
Query OK, 1 row affected (0.00 sec)

这与之前的行为不同，之前的结果会立即显示在屏幕上。这是因为存储过程的结果已保存到输出参数中，而不是作为查询结果返回。要访问结果，您可以直接在MySQL Shell中进行SELECT查询，如下所示：

SELECT @number, @min, @avg, @max;

通过此查询，您正在从局部变量中选择值，而不是再次调用存储过程。存储过程已将其结果保存在这些变量中，数据将保持可用状态，直到您断开与shell的连接。

输出
+---------+----------+-----------+-----------+
| @number | @min     | @avg      | @max      |
+---------+----------+-----------+-----------+
|       2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)

DROP PROCEDURE get_car_stats_by_year;

输出
Query OK, 0 rows affected (0.02 sec)

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

错误消息
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist