是否有可能从mysql函数返回多个值？

I have three tables and I want to sum all the result columns of each table and return these values from the function I define to do all the process. Is it possible to return those three float summation from a single mysql function ?

我有三个表，我想总结每个表的所有结果列，并从我定义的函数返回这些值，以执行所有过程。是否可以从单个mysql函数返回这三个浮点求和？

2 个解决方案

#1

My dirty solution is: 1. Concatenating values in a string. 2 returns string. 3 Splits returned string to values. I assume that it is not elegant and I sure this have limitations but it works for simple cases

我的脏解决方案是：1。连接字符串中的值。 2返回字符串。 3 Splits将字符串返回值。我认为它不优雅，我确信这有局限性，但它适用于简单的情况

Also is necessary create the splitting function because Mysql has not this function:

还有必要创建分裂函数，因为Mysql没有这个函数：

First edit your function.

首先编辑你的功能。

CREATE FUNCTION yourFunctionWith2valuesForReturning()
BEGIN
     DECLARE var1 VARCHAR(255);
     DECLARE var2 VARCHAR(255);

    // Your function logic here. Assign values to var1 and var2

    RETURN CONCAT_WS('|', var1, var2);
END

Now you can split your returned value calling this function:

现在，您可以分割返回的值来调用此函数：

CREATE FUNCTION SPLIT_STR(x VARCHAR(510), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END

For getting the values

为了获得价值

SET value1 = SPLIT_STR(returnedVAlue, '|', 1);
SET value2 = SPLIT_STR(returnedVAlue, '|', 2);

Notes:

笔记：

You can use a string as delimiter, f.e: '|sep|' or another you want.

您可以使用字符串作为分隔符，f.e：'| sep |'或者你想要的另一个。

SPLIT_STR function is cortesy of http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

SPLIT_STR函数是http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

About splitting a string in mysql see also this question: Split Varchar into Character in MySQL

关于在mysql中拆分字符串请参阅此问题：在MySQL中将Varchar拆分为字符

#2

The correct way to do this would be using a stored procedure:

执行此操作的正确方法是使用存储过程：

CREATE PROCEDURE stored_proc(IN arg1 INT, IN arg2 INT, OUT retVal1 FLOAT, OUT retVal2 FLOAT, OUT retVal3 FLOAT)

You can then assign the variables with

然后，您可以使用分配变量

SELECT x*y INTO retVal1;
...

and call the stored procedure with @variables to access them:

并使用@variables调用存储过程来访问它们：

CALL stored_proc(1, 2, @retVal1, @retVal2, @retVal3);

#1