在MySQL中使用可选参数创建函数

Question

我想在MySQL中创建一个带有可选参数的函数。例如，我想创建一个函数来计算其参数的平均值。我创建了一个有五个参数的函数，但是当用户只向该函数传递两个参数时，它仍然应该运行并返回这两个参数的平均值。

Answer 1

不能在MySQL存储过程中设置可选参数。

但是，可以在MySQL UDF中设置可选参数。

您一定知道MySQL有一个AVG aggregate function

变通方法如果你能面对这个变通方法的丑陋，这里的示例代码使用逗号分隔的字符串和值作为输入，并返回平均值。

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 ;

使用concat_ws提供函数。

SELECT MyAvg(CONCAT_WS(',',100,200,300,500)) AS test;

您还可以在C(++)或Delphi/Lazarus中使用write an UDF

Answer 2

虽然远不是一个理想的解决方案，但以下是我如何为我需要的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
||

注意:这意味着您必须调用与参数数量相对应的方法。

Answer 3

另一种方法是只传递一个“超级”参数，该参数是字符串，其中用逗号分隔实际参数。然后，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> ..