Mysql存储过程中的可选参数

How do I create an optional parameter in a mysql stored procedure?

如何在mysql存储过程中创建可选参数?

3 个解决方案

#1

According to this bug, there is currently no way to create optional parameters.

根据此错误,目前无法创建可选参数。

#2

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。

#3

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);

#1