在 MySQL 中创建一个带有可选参数的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4885675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a function with optional arguments in MySQL
提问by Krunal
I want to create a function with optional arguments in MySQL. For instance, I want to create function that calculates the average of its arguments. I create a function of five arguments, but when user passes just two arguments to the function then it should still run and return the average of the two arguments.
我想在 MySQL 中创建一个带有可选参数的函数。例如，我想创建计算其参数平均值的函数。我创建了一个有五个参数的函数，但是当用户只向函数传递两个参数时，它仍然应该运行并返回两个参数的平均值。
回答by Johan
You cannot set optional parameters in MySQL stored procedures.
You can however set optional parameters in a MySQL UDF.
您不能在 MySQL 存储过程中设置可选参数。
但是，您可以在 MySQL UDF 中设置可选参数。
You do know that MySQL has an AVG aggregate function?
你知道 MySQL 有一个AVG 聚合函数吗？
WorkaroundIf you can face the ugliness of this workaround here's samplecode that uses a comma separated string with values as input and returns the average.
变通方法如果您可以面对这种变通方法的丑陋之处，这里的示例代码使用逗号分隔的字符串作为输入，并返回平均值。
DELIMITER $$
CREATE FUNCTION MyAvg(valuestr varchar) RETURNS float
BEGIN
DECLARE output float;
DECLARE arg_count integer;
DECLARE str_length integer;
DECLARE arg float;
DECLARE i integer;
SET output = NULL;
SET i = LENGTH(valuestr);
IF i > 0 THEN BEGIN
SET arg_count = 1;
WHILE i > 0 DO BEGIN
IF MID(valuestr, i, 1)
SET i = i - 1;
END; END WHILE;
/* calculate average */
SET output = 0;
SET i = arg_count;
WHILE i > 0 DO BEGIN
SET arg = SUBSTRING_INDEX(
SUBSTRING_INDEX(valuestr, ',' , i)
, ',', -1 );
SET output = output + arg;
SET i = i - 1;
END; END WHILE;
SET output = output / arg_count;
END; END IF;
RETURN output;
END $$
DELIMITER ;
Use concat_ws to feed the function.
使用 concat_ws 来提供函数。
SELECT MyAvg(CONCAT_WS(',',100,200,300,500)) AS test;
You can also write an UDFin C(++)or Delphi/Lazarus
回答by Ryan Shillington
While far from an ideal solution, here's how I solved optional parameters for a concat function I needed:
虽然远非理想的解决方案，但这里是我如何解决我需要的 concat 函数的可选参数：
delimiter ||
create function safeConcat2(arg1 longtext, arg2 varchar(1023))
returns longtext
return safeConcat3(arg1, arg2, '');
||
create function safeConcat3(arg1 longtext, arg2 varchar(1023), arg3 varchar(1023))
returns longtext
return safeConcat4(arg1, arg2, arg3, '');
||
create function safeConcat4(arg1 longtext, arg2 varchar(1023), arg3 varchar(1023), arg4 varchar(1023))
returns longtext
begin
declare result longText;
set result = concat(arg1, arg2, arg3, arg4);
if( result is null) then
set result=arg1;
end if;
return result;
end
||
Note: This means you have to call the method that corresponds to the number of args.
注意：这意味着您必须调用与 args 数量相对应的方法。
回答by giwyni
Another approach is to pass only one 'super' parameter which is string with commas in it separating the real parameters. The mysql procedure can then parse the 'super' parameter into the separate real parameters. Example:
另一种方法是只传递一个“超级”参数，该参数是用逗号分隔实际参数的字符串。然后，mysql 过程可以将“超级”参数解析为单独的实参数。例子：
create procedure procWithOneSuperParam(param1 varchar(500))
declare param2 varchar(100);
begin
if LOCATE(',',param1) > 0 then
.. param2=<extract the string after the ',' from param1> ..
