是否可以为 mysql 存储过程设置默认参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/982798/
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
Is it possible to have a default parameter for a mysql stored procedure?
提问by aarona
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:
我在谷歌上搜索了这个并不断提出“不，这是不可能的”，但这些帖子的日期是 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 开发人员需要醒来，因为我可以用 MSSQL 做更多的事情。
回答by Paul Sonier
It's still not possible.
这仍然不可能。
回答by Dive50
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 语句来解决这个限制。实际上，每当我们想在数据库中保存默认值时，我们都会传递一个空字符串。
CREATE DEFINER=`test`@`%` PROCEDURE `myProc`(IN myVarParam VARCHAR(40))
BEGIN
IF myVarParam = '' THEN SET myVarParam = 'default-value'; END IF;
...your code here...
END
回答by SKManX
SET myParam = IFNULL(myParam, 0);
Explanation: IFNULL(expression_1, expression_2)
解释： IFNULL(expression_1, expression_2)
The IFNULLfunction returns expression_1if expression_1is not NULL; otherwise it returns expression_2. The IFNULLfunction returns a string or a numeric based on the context where it is used.
该IFNULL函数返回expression_1如果expression_1不是NULL; 否则返回expression_2。该IFNULL函数根据使用它的上下文返回一个字符串或数字。
回答by Michael
If you look into CREATE PROCEDURE Syntaxfor latest MySQL version you'll see that procedure parameter can only contain IN/OUT/INOUT specifier, parameter name and type.
如果您查看最新 MySQL 版本的CREATE PROCEDURE 语法，您将看到过程参数只能包含 IN/OUT/INOUT 说明符、参数名称和类型。
So, default values are still unavailable in latest MySQL version.
所以，默认值在最新的 MySQL 版本中仍然不可用。
回答by Ross Smith II
Unfortunately, MySQL doesn't support DEFAULTparameter values, so:
不幸的是，MySQL 不支持DEFAULT参数值，因此：
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()过程中添加第三个参数时，您的代码将不需要立即更新。
回答by Bill Karwin
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提交功能请求。
