Mysql 存储过程中的可选参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/91150/
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
Optional Parameters in Mysql stored procedures
提问by
How do I create an optional parameter in a mysql stored procedure?
如何在 mysql 存储过程中创建可选参数？
回答by xsl
回答by Cory House
Optional parameters are not supported in mySQL stored procedures, nor are there any current plans to add this functionality at this time. I'd recommend passing null for optional parameters.
mySQL 存储过程不支持可选参数，目前也没有任何添加此功能的计划。我建议为可选参数传递 null。
回答by Anthony Geoghegan
The (somewhat clunky) workaround I used was to define a number of parameters that I thought I might use and then test to see if the optional parameter's value is NOT NULL before using it:
我使用的（有点笨拙的）解决方法是定义一些我认为可能会使用的参数，然后在使用之前测试可选参数的值是否为 NOT NULL：
CREATE PROCEDURE add_product(product_name VARCHAR(100), product_price FLOAT,
cat1 INT, cat2 INT, cat3 INT)
-- The cat? parameters are optional; provide a NULL value if not required
BEGIN
...
-- Add product to relevant categories
IF cat1 IS NOT NULL THEN
INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat1);
END IF;
IF cat2 IS NOT NULL THEN
INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat2);
END IF;
IF cat3 IS NOT NULL THEN
INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat3);
END IF;
END
If I don't want to use the parameter when calling the stored, I simply pass a NULL value. Here's an example of the above stored procedure being called:
如果我不想在调用存储时使用参数，我只需传递一个 NULL 值。下面是调用上述存储过程的示例：
CALL add_product("New product title", 25, 66, 68, NULL);
