blocks|key|1532276|text|这仍然不可能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1532277|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

blocks|key|1532448|text|我们通过在存储过程中添加一个简单的IF语句绕过了这个限制。实际上，每当我们想要在数据库中保存缺省值时，我们都会传递一个空字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1532449|CREATE+DEFINER=`test`@`%25`+PROCEDURE+`myProc`(IN+myVarParam+VARCHAR(40))
BEGIN
++IF+myVarParam+=+''+THEN+SET+myVarParam+=+'default-value';+END+IF;

++...your+code+here...
END|code-block|syntax|javascript|1532450|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

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

 ...your code here...
END
</code></pre>

blocks|key|1317424|text|SET+myParam+=+IFNULL(myParam,+0);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1317425|说明：IFNULL(expression_1,+expression_2)|unstyled|offset|length|style|CODE|1317426|如果expression_1不是NULL，则IFNULL函数返回expression_1；否则返回expression_2。IFNULL函数根据使用的上下文返回字符串或数字。|1317427|entityMap^0|0|3|Y|0|2|C|G|4|M|6|W|C|1D|C|1Q|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@$G|T|H|U|I|J]|$G|V|H|W|I|J]|$G|X|H|Y|I|J]|$G|Z|H|10|I|J]|$G|11|H|12|I|J]|$G|13|H|14|I|J]]|9|@]|A|$]]|$1|M|3|-4|5|F|7|15|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>SET myParam = IFNULL(myParam, 0);
</code></pre>

Explanation: <code>IFNULL(expression_1, expression_2)</code>

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

blocks|key|1532337|text|如果查看最新MySQL版本的CREATE+PROCEDURE+Syntax，您会发现过程参数只能包含IN/OUT/INOUT说明符、参数名称和类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1532338|因此，在最新的MySQL版本中，默认值仍然不可用。|1532339|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/6.0/en/create-procedure.html^0|E|N|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

If you look into <a href="http://dev.mysql.com/doc/refman/6.0/en/create-procedure.html" rel="noreferrer">CREATE PROCEDURE Syntax</a> for latest MySQL version you'll see that procedure parameter can only contain IN/OUT/INOUT specifier, parameter name and type.

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

blocks|key|1532516|text|遗憾的是，MySQL不支持DEFAULT参数值，因此：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1532517|CREATE+PROCEDURE+`blah`
(
++myDefaultParam+int+DEFAULT+0
)
BEGIN
++--+Do+something+here
END|code-block|syntax|javascript|1532518|返回错误：|1532519|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|1532520|要解决此限制，只需创建将默认值分配给原始过程的附加过程：|1532521|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;
//|1532522|然后，运行以下命令：|1532523|CALL+blah(1,+1);
CALL+blah2(2,+2);
CALL+blah1(3);
CALL+blah0();|1532524|将返回：|1532525|%2B--------%2B--------%2B
%7C+param1+%7C+param2+%7C
%2B--------%2B--------%2B
%7C++++++1+%7C++++++1+%7C
%2B--------%2B--------%2B
1+row+in+set+(0.00+sec)

Query+OK,+0+rows+affected+(0.00+sec)

%2B--------%2B--------%2B
%7C+param1+%7C+param2+%7C
%2B--------%2B--------%2B
%7C++++++2+%7C++++++2+%7C
%2B--------%2B--------%2B
1+row+in+set+(0.00+sec)

Query+OK,+0+rows+affected+(0.00+sec)

%2B--------%2B--------%2B
%7C+param1+%7C+param2+%7C
%2B--------%2B--------%2B
%7C++++++3+%7C++++++3+%7C
%2B--------%2B--------%2B
1+row+in+set+(0.00+sec)

Query+OK,+0+rows+affected+(0.00+sec)

%2B--------%2B--------%2B
%7C+param1+%7C+param2+%7C
%2B--------%2B--------%2B
%7C++++++4+%7C++++++3+%7C
%2B--------%2B--------%2B
1+row+in+set+(0.00+sec)

Query+OK,+0+rows+affected+(0.00+sec)|1532526|然后，如果您确保只使用blah2()、blah1()和blah0()过程，那么当您向blah()过程添加第三个参数时，您的代码将不需要立即更新。|1532527|entityMap^0|D|7|0|0|0|0|0|0|0|0|0|0|B|7|J|7|R|7|16|6|0^^$0|@$1|2|3|4|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1A|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1C|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|H|7|1F|8|@]|D|@]|E|$I|J]]|$1|10|3|11|5|6|7|1G|8|@$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]|$9|1L|A|1M|B|C]|$9|1N|A|1O|B|C]]|D|@]|E|$]]|$1|12|3|-4|5|6|7|1P|8|@]|D|@]|E|$]]]|13|$]]

Unfortunately, MySQL doesn't support <code>DEFAULT</code> parameter values, so:

<pre><code>CREATE PROCEDURE `blah`
(
 myDefaultParam int DEFAULT 0
)
BEGIN
 -- Do something here
END
</code></pre>

returns the error:

<pre><code>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
</code></pre>

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

<pre><code>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;
//
</code></pre>

Then, running this:

<pre><code>CALL blah(1, 1);
CALL blah2(2, 2);
CALL blah1(3);
CALL blah0();
</code></pre>

will return:

<pre><code>+--------+--------+
| 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)
</code></pre>

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

blocks|key|1317224|text|不，这在MySQL存储例程语法中不受支持。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1317225|请随时在bugs.mysql.com上提交功能请求。|offset|length|1317226|entityMap|0|LINK|mutability|MUTABLE|url|http://bugs.mysql.com/how-to-report.php^0|0|4|E|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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

Feel free to submit a feature request at <a href="http://bugs.mysql.com/how-to-report.php" rel="nofollow noreferrer">bugs.mysql.com</a>.

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:

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

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.

Is it possible to have a default parameter for a mysql stored procedure?

我已经在谷歌上搜索过了，一直在想“不，这是不可能的”，但这些帖子的日期是2005-2007年，所以我想知道这是不是已经改变了。代码示例：CREATE PROCEDURE `blah`(  myDefaultParam int = 0 -- This breaks the code for some reason)BEGIN  -- Do something hereEND其中一个解决方案是传递nu

问mysql存储过程可以有一个默认参数吗？
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql存储过程可以有一个默认参数吗？EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql存储过程可以有一个默认参数吗？
EN