MySQL 自定义函数

1. MySQL 自定义函数简介

MySQL 自定义函数是用户根据自己的需求编写的函数，可以在 SQL 查询中使用，功能类似于编程语言中的函数。通过 MySQL 自定义函数，我们可以拓展 MySQL 的功能，实现更复杂的数据处理和计算。

MySQL 支持多种类型的自定义函数，包括标量函数（Scalar Function）、聚合函数（Aggregate Function）、表值函数（Table-Valued Function）等。不同类型的函数用途不同，适用于不同的场景。

在本文中，我们将重点介绍标量函数的创建和使用，同时也会简要介绍其他类型的自定义函数。

2. 标量函数

2.1 创建标量函数

标量函数是最常用的一种自定义函数，它接收若干个参数并返回一个标量（单一的值）。创建标量函数的语法如下：

CREATE FUNCTION function_name ([parameter_list]) RETURNS return_type
    BEGIN
        function_body
    END;

其中：

function_name 是函数的名称，可以根据需求自定义；
parameter_list 是函数接收的参数列表，参数可以有多个，每个参数有自己的名称和类型；
return_type 是函数返回的数据类型，可以是 MySQL 支持的任意数据类型；
function_body 是函数的实现代码，在 BEGIN 和 END 之间。

2.2 示例：计算字符串长度

下面我们通过一个示例来演示创建标量函数的过程。假设我们需要计算一个字符串的长度，但是 MySQL 默认并不提供这个函数。

首先，我们使用以下语句创建一个名为 str_length 的标量函数：

DELIMITER //
CREATE FUNCTION str_length(str VARCHAR(255))
    RETURNS INT
    BEGIN
        DECLARE len INT;
        SET len = LENGTH(str);
        RETURN len;
    END//
DELIMITER ;

上述代码中，我们使用了 DELIMITER 语句修改结束符，将其设置为 //，这是因为函数体中可能包含分号。接着，我们定义了一个名为 str_length 的标量函数，它接收一个名为 str 的参数，类型为 VARCHAR(255)，返回一个 INT 类型的结果。

在函数体中，我们使用 DECLARE 语句定义了一个名为 len 的局部变量，然后使用 SET 语句将 str 的长度赋值给 len。最后，我们使用 RETURN 语句返回了结果。

2.3 使用标量函数

创建完标量函数后，我们可以在 SQL 查询中使用它。以下是一个使用 str_length 函数的示例：

SELECT str_length('Hello World!') AS length;

运行以上查询，我们将得到字符串 'Hello World!' 的长度。

+--------+
| length |
+--------+
|     12 |
+--------+

3. 聚合函数

聚合函数是对一组数据进行计算，并返回一个单一的结果。常见的聚合函数有 SUM、AVG、COUNT、MAX、MIN 等。

3.1 创建聚合函数

创建聚合函数的语法如下：

CREATE AGGREGATE FUNCTION function_name ([parameter_list]) RETURNS return_type
    BEGIN
        function_body
    END;

聚合函数的语法与标量函数类似，只是关键字从 FUNCTION 变为了 AGGREGATE FUNCTION。

3.2 示例：计算平均值

下面我们通过一个示例来创建一个计算平均值的聚合函数。

DELIMITER //

CREATE AGGREGATE FUNCTION avg_func RETURNS REAL
    BEGIN
        DECLARE count INT DEFAULT 0;
        DECLARE sum REAL DEFAULT 0;
        DECLARE avg REAL DEFAULT 0;
        DECLARE value INT;

        DECLARE cur CURSOR FOR SELECT value_column FROM table_name;

        OPEN cur;

        FETCH cur INTO value;

        WHILE FETCH_STATUS = 0 DO
            SET count = count + 1;
            SET sum = sum + value;
            FETCH cur INTO value;
        END WHILE;

        IF count > 0 THEN
            SET avg = sum / count;
        END IF;

        CLOSE cur;

        RETURN avg;
    END//

DELIMITER ;

上述代码中，我们创建了一个名为 avg_func 的聚合函数，它不接收任何参数，返回一个 REAL 类型的结果。

在函数体中，我们使用了 DECLARE 语句定义了多个局部变量，包括 count、sum、avg 和 value。然后，我们使用 DECLARE CURSOR 语句创建了一个名为 cur 的游标，用于遍历查询结果。

在循环中，我们对每个值进行累加，并更新 count 的值。最后，我们通过除法计算平均值，并返回结果。

3.3 使用聚合函数

创建完聚合函数后，我们可以在 SQL 查询中使用它。以下是一个使用 avg_func 函数的示例：

SELECT avg_func() AS average;

运行以上查询，我们将得到表 table_name 中 value_column 列的平均值。

4. 表值函数

表值函数是一种特殊的自定义函数，可以返回一个表。它可以接收参数，并基于这些参数生成一个或多个表进行返回。

4.1 创建表值函数

创建表值函数的语法如下：

CREATE FUNCTION function_name ([parameter_list]) RETURNS TABLE
    BEGIN
        function_body
    END;

表值函数与标量函数的语法类似，只是返回类型由标量改为了 TABLE。

4.2 示例：生成指定范围内的整数序列

下面我们通过一个示例来创建一个生成指定范围内的整数序列的表值函数。

DELIMITER //

CREATE FUNCTION range_func(start INT, end INT)
    RETURNS TABLE
    BEGIN
        DECLARE result_table TABLE (value INT);
        DECLARE current_value INT;

        SET current_value = start;

        WHILE current_value <= end DO
            INSERT INTO result_table VALUES (current_value);
            SET current_value = current_value + 1;
        END WHILE;

        RETURN result_table;
    END//

DELIMITER ;

上述代码中，我们创建了一个名为 range_func 的表值函数，它接收两个 INT 类型的参数 start 和 end，返回一个包含整数序列的表。

在函数体中，我们使用了 DECLARE 语句定义了 result_table 表和 current_value 变量。然后，我们初始化 current_value 为 start。

在循环中，我们逐个插入整数到 result_table 表中，并更新 current_value。最后，我们通过 RETURN 语句返回 result_table。