创建具有动态参数数量的 MySQL 存储函数 [英] Create a MySQL stored function with a dynamic number of arguments

问题描述

我正在尝试创建一个 MySQL 函数 IS_IN_ENUM('value', 'val1', 'val2', 'val3') 如果 'value' 在 ('val1','val2'，'val3').我知道我可以做 SELECT 'value' IN ('val1', 'val2', 'val3') 但这没那么有趣，因为我只想学习如何创建这样的函数.

I am trying to create a MySQL function IS_IN_ENUM('value', 'val1', 'val2', 'val3') which return true if 'value' is in ('val1', 'val2', 'val3'). I know I can do SELECT 'value' IN ('val1', 'val2', 'val3') but that's less intersting because I just want to learn how to create such functions.

我举个例子，考虑下面的ADD函数:

I give you an example, consider the following ADD function :

CREATE FUNCTION my_add (
    a DOUBLE,
    b DOUBLE
)
RETURNS DOUBLE
BEGIN

    IF a IS NULL THEN
        SET a = 0;
    END IF;

    IF b IS NULL THEN
        SET b = 0;
    END IF;

    RETURN (a + b);
END;

如果我执行 SELECT my_add(1, 1)，我会得到 2(哇！).

If I do SELECT my_add(1, 1), I get 2 (wow!).

如何改进此功能以使其能够调用:

How can I improve this function to be able to call :

SELECT my_add(1, 1); -- 2
SELECT my_add(1, 1, 1); -- 3
SELECT my_add(1, 1, 1, 1); -- 4
SELECT my_add(1, 1, 1, 1, 1, 1, .....); -- n

推荐答案

您展示的函数示例是 Stored Function，而不是 UDF.正如@Enzino 回答的那样，MySQL 中的存储函数不支持可变数量的参数.

The function example you show is a Stored Function, not a UDF. Stored Functions in MySQL don't support a variable number of arguments, as @Enzino answered.

MySQL UDF 用 C 或 C++ 编写，编译成动态对象文件，然后通过 CREATE FUNCTION的不同语法.

MySQL UDFs are written in C or C++, compiled into dynamic object files, and then linked with the MySQL server with a different syntax of CREATE FUNCTION.

参见 http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html 了解编写 UDF 的详细信息.但我不知道你是否想开始编写 C/C++ 代码来做到这一点.

See http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html for details of writing UDFs. But I don't know if you want to get into writing C/C++ code to do this.

MySQL UDF 确实支持可变数量的参数.事实上，所有 UDF 都隐式接受任意数量的参数，作为程序员，您可以确定给定参数的数量和数据类型是否对您的函数有效.

MySQL UDFs do support variable number of arguments. In fact, all UDFs implicitly accept any number of arguments, and it's up to you as the programmer to determine if the number and datatypes of the arguments given are valid for your function.

UDF 中的处理函数参数记录在 http://dev.mysql.com/doc/refman/5.5/en/udf-arguments.html

Processing function arguments in UDFs is documented in http://dev.mysql.com/doc/refman/5.5/en/udf-arguments.html

这篇关于创建具有动态参数数量的 MySQL 存储函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！