mysql可选参数_创建MySQL中的可选参数的函数

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.

解决方案

You cannot set optional parameters in MySQL stored procedures.

You can however set optional parameters in a MySQL UDF.

You do know that MySQL has an AVG aggregate function?

Workaround

If 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.

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