MySQL - 存储函数

MySQL：存储函数

什么是存储函数？

MySQL 中的存储函数是一个存储在数据库中的命名 SQL 代码块。它旨在执行特定的计算或操作，最重要的是，返回单个值。创建后，存储函数可以直接在 SQL 语句中使用，就像内置函数 NOW() 或 CONCAT() 一样。

它们非常适合封装需要在多个查询中重用的复杂业务逻辑，确保一致性并简化应用程序代码。

存储函数与存储过程对比

尽管相似，但函数和过程有关键区别：

• 返回值： 函数必须返回单个值。过程没有直接返回值，但可以通过 OUT 或 INOUT 参数或返回结果集来返回数据。
• 调用： 函数可以直接在 SELECT、WHERE 或 SET 语句中调用。过程必须使用 CALL 语句调用。
• 用途： 函数用于计算和数据转换。过程用于执行一系列操作，例如复杂的数据更新或管理任务。

创建存储函数 (CREATE FUNCTION)

要创建存储函数，您需要 CREATE ROUTINE 权限。语法涉及定义函数名称、其参数、返回数据类型和函数体。

语法

DELIMITER //

CREATE FUNCTION function_name(parameter1 datatype, ...)
RETURNS return_datatype
[CHARACTERISTICS]
BEGIN
    -- 局部变量声明
    -- SQL 语句和逻辑
    RETURN value;
END //

DELIMITER ;

关于 DELIMITER 的说明：我们将默认的分号（;）分隔符更改为其他符号（例如 //），以便 MySQL 将整个函数体作为一个单独的语句处理，包括其中的分号。

示例：客户等级函数

让我们创建一个函数，该函数将客户的薪资作为输入，并返回其等级（‘Platinum’、‘Gold’、‘Silver’）。我们将使用以下 CUSTOMERS 表：

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    SALARY DECIMAL(10, 2)
);
INSERT INTO CUSTOMERS (NAME, SALARY) VALUES ('Ramesh', 2000.00), ('Khilan', 8500.00), ('Muffy', 12000.00);

现在，让我们定义这个函数。

DELIMITER //

CREATE FUNCTION GetCustomerLevel(salary DECIMAL(10,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE customerLevel VARCHAR(10);
    IF salary > 10000.00 THEN
        SET customerLevel = 'Platinum';
    ELSEIF salary > 5000.00 AND salary <= 10000.00 THEN
        SET customerLevel = 'Gold';
    ELSE
        SET customerLevel = 'Silver';
    END IF;
    RETURN customerLevel;
END //

DELIMITER ;

函数特性

您应该至少指定一个特性来告诉 MySQL 函数的行为，这有助于优化器。最常见的是：

• DETERMINISTIC（确定性）： 指定函数对于相同的输入参数将始终返回相同的结果。我们的 GetCustomerLevel 函数是确定性的。
• NOT DETERMINISTIC（非确定性）： （默认）即使输入相同，结果也可能改变（例如，如果它调用 NOW() 或从表中读取数据）。
• READS SQL DATA（读取 SQL 数据）： 表明函数包含 SELECT 语句，但不修改数据。
• CONTAINS SQL（包含 SQL）： 表明函数包含 SQL 但不读取或写入数据（例如，SET @x = 1）。
• MODIFIES SQL DATA（修改 SQL 数据）： 表明函数包含修改数据的语句（例如，INSERT、UPDATE）。函数很少用于此目的。

调用存储函数

您现在可以在 SELECT 查询中使用此函数。

SELECT
    NAME,
    SALARY,
    GetCustomerLevel(SALARY) AS Level
FROM CUSTOMERS;

输出

NAME	SALARY	等级
Ramesh	2000.00	Silver
Khilan	8500.00	Gold
Muffy	12000.00	Platinum

查看、修改和删除函数

• 查看创建代码： SHOW CREATE FUNCTION GetCustomerLevel;
• 删除函数： DROP FUNCTION IF EXISTS GetCustomerLevel;
• 修改函数： 没有 ALTER FUNCTION 语句可以直接更改函数体或参数。您必须先 DROP（删除）函数，然后使用所需的更改再次 CREATE（创建）它。

安全注意事项（DEFINER, SQL SECURITY）

创建函数时，您可以指定其安全上下文：

• DEFINER： 此子句指定函数执行时将使用的 MySQL 用户账户的权限。默认值是创建函数的用户。这功能强大，但如果定义者具有高权限，可能会带来安全风险。
• SQL SECURITY： 此项可以设置为 DEFINER（默认）或 INVOKER。如果设置为 INVOKER，函数将以调用它的用户的权限运行，而不是创建它的用户的权限。这通常是一种更安全的方法。

CREATE FUNCTION GetCustomerLevel(salary DECIMAL(10,2))
RETURNS VARCHAR(10)
SQL SECURITY INVOKER
-- ... 函数的其余部分

从存储过程调用函数

您可以轻松地在存储过程中重用函数逻辑。这是一个使用我们的 GetCustomerLevel 函数生成完整客户报告的过程。

DELIMITER //

CREATE PROCEDURE GenerateCustomerReport()
BEGIN
    SELECT
        ID,
        NAME,
        SALARY,
        GetCustomerLevel(SALARY) AS Level
    FROM CUSTOMERS
    ORDER BY SALARY DESC;
END //

DELIMITER ;

要运行该过程，只需使用 CALL 语句：

CALL GenerateCustomerReport();