MySQL 函数与自定义逻辑

简介

在这个实验中，你将学习如何在 MySQL 中创建和使用自定义函数。本实验的重点是通过创建一个计算矩形面积的函数，将可复用的逻辑封装在数据库中。

步骤包括连接到 MySQL 服务器，创建数据库（如果需要），定义 calculate_rectangle_area 函数，该函数包含长度和宽度的输入参数，并指定返回数据类型。函数体计算面积并返回结果。后续步骤将涵盖在 SELECT 查询中使用此函数，使用示例数据测试其输出，最后删除该函数。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/delete("Data Deletion") subgraph Lab Skills mysql/use_database -.-> lab-550908{{"MySQL 函数与自定义逻辑"}} mysql/create_database -.-> lab-550908{{"MySQL 函数与自定义逻辑"}} mysql/create_table -.-> lab-550908{{"MySQL 函数与自定义逻辑"}} mysql/select -.-> lab-550908{{"MySQL 函数与自定义逻辑"}} mysql/insert -.-> lab-550908{{"MySQL 函数与自定义逻辑"}} mysql/delete -.-> lab-550908{{"MySQL 函数与自定义逻辑"}} end

创建一个计算值的函数

在这一步中，你将学习如何在 MySQL 中创建函数。函数是一种强大的方式，可将可复用的逻辑封装在数据库中。我们将创建一个简单的函数来计算矩形的面积。

首先，让我们连接到 MySQL 服务器。打开你的终端并执行以下命令：

mysql -u root -p

系统会提示你输入 root 密码。输入密码后按回车键。

现在，让我们创建一个数据库来进行操作。如果你已经有想要使用的数据库，可以跳过这一步。否则，请执行以下 SQL 命令：

CREATE DATABASE IF NOT EXISTS my_functions_db;
USE my_functions_db;

接下来，我们将创建函数。在 MySQL 中创建函数的语法如下：

CREATE FUNCTION function_name (parameter1 data_type, parameter2 data_type, ...)
RETURNS return_data_type
DETERMINISTIC
BEGIN
  -- Function body
  RETURN return_value;
END;

function_name：函数的名称。
parameter1、parameter2 等：函数的输入参数及其数据类型。
return_data_type：函数返回值的数据类型。
DETERMINISTIC：此关键字表示该函数对于相同的输入值始终返回相同的结果。这对于优化很重要。
BEGIN 和 END：这些关键字用于包含函数体。
RETURN return_value：此语句返回函数计算的值。

现在，让我们创建一个名为 calculate_rectangle_area 的函数，该函数将矩形的长度和宽度作为输入，并返回其面积。执行以下 SQL 命令：

CREATE FUNCTION calculate_rectangle_area (length INT, width INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE area INT;
  SET area = length * width;
  RETURN area;
END;

在这个函数中：

我们声明了两个输入参数 length 和 width，它们的类型均为 INT。
该函数返回一个 INT 类型的值，代表矩形的面积。
我们声明了一个类型为 INT 的局部变量 area 来存储计算出的面积。
我们通过将长度和宽度相乘来计算面积，并将其存储在 area 变量中。
最后，我们返回 area 变量的值。

要验证函数是否已成功创建，你可以执行以下 SQL 命令：

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

此命令将显示 calculate_rectangle_area 函数的相关信息，包括其名称、所属数据库和创建日期。

在 SELECT 查询中使用函数

在这一步中，你将学习如何在 SELECT 查询中使用上一步创建的函数。这使你能够将函数的逻辑应用于从数据库表中检索的数据。

首先，确保你已连接到 MySQL 服务器并使用 my_functions_db 数据库（即你创建函数的数据库）。如果你关闭了连接，请使用以下命令重新连接：

mysql -u root -p

然后，选择数据库：

USE my_functions_db;

现在，让我们创建一个名为 rectangles 的表，用于存储不同矩形的长度和宽度。执行以下 SQL 命令：

CREATE TABLE rectangles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  length INT,
  width INT
);

此命令创建了一个包含三列的表：

id：一个自增的整数，用作主键。
length：一个整数，表示矩形的长度。
width：一个整数，表示矩形的宽度。

接下来，让我们向 rectangles 表中插入一些示例数据。执行以下 SQL 命令：

INSERT INTO rectangles (length, width) VALUES (5, 10);
INSERT INTO rectangles (length, width) VALUES (7, 8);
INSERT INTO rectangles (length, width) VALUES (12, 3);

现在，rectangles 表中已有一些数据，我们可以在 SELECT 查询中使用 calculate_rectangle_area 函数来计算每个矩形的面积。执行以下 SQL 命令：

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

此查询从 rectangles 表中检索 id、length 和 width 列，并且还调用 calculate_rectangle_area 函数，将 length 和 width 列作为输入。函数调用的结果被别名为 area。

查询的输出将是一个包含四列的表：id、length、width 和 area。area 列将包含每个矩形的计算面积。

你应该会看到类似于以下的输出：

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
+----+--------+-------+------+

这展示了如何在 SELECT 查询中使用函数对从表中检索的数据进行计算。

使用示例数据测试函数输出

在这一步中，我们将使用不同的示例数据来测试函数的输出，以确保其正常工作。我们会向 rectangles 表中插入新数据，然后使用 calculate_rectangle_area 函数来验证计算出的面积。

首先，确保你已连接到 MySQL 服务器并使用 my_functions_db 数据库。如果你关闭了连接，请使用以下命令重新连接：

mysql -u root -p

然后，选择数据库：

USE my_functions_db;

现在，让我们向 rectangles 表中插入更多具有不同长度和宽度值的示例数据。执行以下 SQL 命令：

INSERT INTO rectangles (length, width) VALUES (10, 10);
INSERT INTO rectangles (length, width) VALUES (2, 5);
INSERT INTO rectangles (length, width) VALUES (15, 4);

这些命令向 rectangles 表中插入了三行新数据，具体值如下：

长度 = 10，宽度 = 10
长度 = 2，宽度 = 5
长度 = 15，宽度 = 4

现在，让我们使用包含 calculate_rectangle_area 函数的 SELECT 查询来计算所有矩形（包括新插入的矩形）的面积。执行以下 SQL 命令：

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

此查询将从 rectangles 表中检索所有行，并使用 calculate_rectangle_area 函数为每个矩形计算面积。

查询的输出现在应该包含新矩形的面积：

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
|  4 |     10 |    10 |  100 |
|  5 |      2 |     5 |   10 |
|  6 |     15 |     4 |   60 |
+----+--------+-------+------+

验证新数据计算出的面积是否正确：

长度为 10 且宽度为 10 的矩形面积为 100（10 * 10 = 100）。
长度为 2 且宽度为 5 的矩形面积为 10（2 * 5 = 10）。
长度为 15 且宽度为 4 的矩形面积为 60（15 * 4 = 60）。

如果计算出的面积正确，则表明 calculate_rectangle_area 函数按预期工作。这一步通过使用各种输入测试函数，有助于确保函数的可靠性。

删除函数

在这一步中，你将学习如何删除之前创建的函数。删除函数会将其从数据库中移除，因此在删除之前，确保你不再需要该函数非常重要。

首先，确保你已连接到 MySQL 服务器并使用 my_functions_db 数据库。如果你关闭了连接，请使用以下命令重新连接：

mysql -u root -p

然后，选择数据库：

USE my_functions_db;

要删除 calculate_rectangle_area 函数，请执行以下 SQL 命令：

DROP FUNCTION IF EXISTS calculate_rectangle_area;

DROP FUNCTION：这是用于删除函数的 SQL 命令。
IF EXISTS：如果函数不存在，此子句可防止出现错误。包含此子句是避免意外错误的良好做法。
calculate_rectangle_area：这是你要删除的函数的名称。

执行此命令后，calculate_rectangle_area 函数将从 my_functions_db 数据库中移除。

要验证函数是否已被删除，你可以尝试在 rectangles 表的查询中使用该函数。执行以下 SQL 命令：

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

此命令现在应该会返回一个错误，因为 calculate_rectangle_area 函数已不存在。错误消息类似于：ERROR 1305 (42000): FUNCTION my_functions_db.calculate_rectangle_area does not exist。

或者，你可以再次使用 SHOW FUNCTION STATUS 命令：

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

此命令应返回一个空结果集，表明该函数已不存在。

通过删除函数，你已将其从数据库中移除并释放了资源。请记住，只删除你确定不再需要的函数。

总结

在本次实验中，你学习了如何在 MySQL 中创建函数以封装可复用的逻辑。这个过程包括连接到 MySQL 服务器、创建数据库（如果需要），然后定义一个带有输入参数、返回数据类型和 DETERMINISTIC 关键字的函数。

具体来说，你创建了 calculate_rectangle_area 函数，该函数以矩形的长度和宽度作为输入，并返回其面积。函数体中包含声明一个局部变量来存储计算出的面积，并使用 RETURN 语句返回结果。