有没有可能为mysql存储过程设置一个默认参数?

I have googled this and keep coming up with "No it is not possible" but these posts were dated 2005-2007 so I'm wondering if this has been changed. A code example:

我在google上搜索了一下，然后继续说“不可能”，但是这些帖子是在2005-2007年发布的，所以我想知道这是不是已经改变了。代码示例:

CREATE PROCEDURE `blah`
(
  myDefaultParam int = 0 -- This breaks the code for some reason
)
BEGIN
  -- Do something here
END

One of the solutions has been to pass null and then check for null and set the variable. I don't want to do that and I shouldn't have to. If this is true then MySql devs need to wake up because there is so much more I could do with MSSQL.

其中一个解决方案是传递null，然后检查null并设置变量。我不想这么做，我也不应该这么做。如果这是真的，那么MySql devs需要唤醒，因为我可以用MSSQL做更多的事情。

6 个解决方案

#1

It's still not possible.

它仍然是不可能的。

#2

We worked around this limitation by adding a simple IF statement in the stored procedure. Practically we pass an empty string whenever we want to save the default value in the DB.

我们通过在存储过程中添加一个简单的IF语句来解决这个限制。实际上，每当我们想要在DB中保存默认值时，我们都会传递一个空字符串。

CREATE DEFINER=`test`@`%` PROCEDURE `myProc`(IN myVarParam VARCHAR(40))
BEGIN
  IF myVarParam = '' THEN SET myVarParam = 'default-value'; END IF;

  ...your code here...
END

#3

SET myParam = IFNULL(myParam, 0);

Explanation: IFNULL(expression_1, expression_2)

解释:IFNULL(expression_1 expression_2)

The IFNULL function returns expression_1 if expression_1 is not NULL; otherwise it returns expression_2. The IFNULL function returns a string or a numeric based on the context where it is used.

如果expression_1不是NULL，则IFNULL函数返回expression_1;否则返回expression_2。IFNULL函数根据使用字符串的上下文返回字符串或数字。

#4

If you look into CREATE PROCEDURE Syntax for latest MySQL version you'll see that procedure parameter can only contain IN/OUT/INOUT specifier, parameter name and type.

如果您查看创建最新MySQL版本的过程语法，您会发现过程参数只能包含/OUT/INOUT说明符、参数名和类型。

So, default values are still unavailable in latest MySQL version.

因此，在最新的MySQL版本中，默认值仍然不可用。

#5

Unfortunately, MySQL doesn't support DEFAULT parameter values, so:

不幸的是，MySQL不支持默认参数值，所以:

CREATE PROCEDURE `blah`
(
  myDefaultParam int DEFAULT 0
)
BEGIN
  -- Do something here
END

returns the error:

返回错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use 
near 'DEFAULT 0) BEGIN END' at line 3

To work around this limitation, simply create additional procedures that assign default values to the original procedure:

要解决这个限制，只需创建附加的过程，为原始过程分配默认值:

DELIMITER //

DROP PROCEDURE IF EXISTS blah//
DROP PROCEDURE IF EXISTS blah2//
DROP PROCEDURE IF EXISTS blah1//
DROP PROCEDURE IF EXISTS blah0//

CREATE PROCEDURE blah(param1 INT UNSIGNED, param2 INT UNSIGNED)
BEGIN
    SELECT param1, param2;
END;
//

CREATE PROCEDURE blah2(param1 INT UNSIGNED, param2 INT UNSIGNED)
BEGIN
    CALL blah(param1, param2);
END;
//

CREATE PROCEDURE blah1(param1 INT UNSIGNED)
BEGIN
    CALL blah2(param1, 3);
END;
//

CREATE PROCEDURE blah0()
BEGIN
    CALL blah1(4);
END;
//

Then, running this:

然后,运行:

CALL blah(1, 1);
CALL blah2(2, 2);
CALL blah1(3);
CALL blah0();

will return:

将返回:

+--------+--------+
| param1 | param2 |
+--------+--------+
|      1 |      1 |
+--------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+--------+
| param1 | param2 |
+--------+--------+
|      2 |      2 |
+--------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+--------+
| param1 | param2 |
+--------+--------+
|      3 |      3 |
+--------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------+--------+
| param1 | param2 |
+--------+--------+
|      4 |      3 |
+--------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Then, if you make sure to only use the blah2(), blah1() and blah0() procedures, your code will not need to be immediately updated, when you add a third parameter to the blah() procedure.

然后，如果您确保只使用blah2()、blah1()和blah0()过程，那么在向blah()过程添加第三个参数时，您的代码不需要立即更新。

#6

No, this is not supported in MySQL stored routine syntax.

不，这在MySQL存储的例程语法中是不支持的。

Feel free to submit a feature request at bugs.mysql.com.

请在bugs.mysql.com上提交特性请求。

#1