Variable number of parameters and optional parameters in MySQL/MariaDB procedures

A long method
Hope your procedures don’t look like this

In most programming languages, a function can have optional parameters that can be passed or not. Or it can accept any number of parameters. I will globally call these features dynamic parameters. In this article we’ll see if, and how, we can use dynamic parameters in MySQL and MariaDB stored procedures.

What dynamic parameters are

But first, let me write a paragraph for those who don’t know what I’ve talking about. Let’s see a couple of examples in the PHP language – I’ve chosen it because it’s very widely used and it has these features.

function makeCoffee($coffeeType = NULL)
{
    ...
}
makecoffee();
makecoffee('italian');

Or they can do something like this:

function sumSomeNumbers()
{
    $numArgs = func_num_args();
    echo "Summing $numArgs numbers \n";
    $argList = func_get_args();
    $r = 0;
    for ($i = 0; $i < $argList; $i++) {
        $r += $argList[$i];
    }
    return $r;
}

In the first example, there is a single parameter, and it’s optional. If it’s not specified, its value will be NULL. In the second example, zero or more parameters are accepted. In this case parameters don’t have a name, but they can be read with a function called func_get_args(), while their number can be obtained with func_num_args(). If we look at C language, we have an array argv and an integer argc, but the basic idea is the same.

Dynamic parameters in MySQL and MariaDB

Bad news: these nice features don’t exist in MySQL and MariDB.

But we can use them anyway, with recent versions. After all, some features allow us to pass a procedure a single structured value, that can nest any number of values. These features are:

  • MySQL 5.7 JSON: MySQL has a real JSON type, and several functions that allow us to work with it. In MySQL 8 we can even turn the JSON value into a table, with the JSON_TABLE() function.
  • MariaDB 10.2 JSON: MariaDB doesn’t have a JSON type, it uses BLOB instead. But it has a set of JSON functions, mostly compatible with MySQL. It doesn’t support JSON_TABLE(). Function differences between MariaDB and MySQL in several pages, and they have a JSON section.
  • MariaDB dynamic columns: This set of functions operates on a BLOB value. This is actually treated as a semi-structured value, containing any number of columns, without a fixed structure.

So basically we have two options: JSON or dynamic columns. I think that JSON is usually the best option, so I will use it in this post, testing the examples on MariaDB for better compatibility.

A JSON-based solution

Let’s see how we can have optional parameters and a variable number of parameters using JSON documents.

Optional parameters

Optional parameters are just named parameters that could be specified or not. We can achieve this with a JSON object:

CREATE FUNCTION table_exists(params BLOB)
    RETURNS BOOL
    NOT DETERMINISTIC
    READS SQL DATA
    COMMENT '
Return whether a table exists.
Parameters must be passed in a JSON document:
* schema (optional). : Schema that could contain the table.
                       By default, the schema containing this procedure.
* table              : Name of the table to check.
'
BEGIN
    DECLARE v_table VARCHAR(64)
        DEFAULT JSON_UNQUOTE(JSON_EXTRACT(params, '$.table'));
    DECLARE v_schema VARCHAR(64)
        DEFAULT JSON_UNQUOTE(JSON_EXTRACT(params, '$.schema'));

    IF v_schema IS NULL THEN
        RETURN EXISTS (
            SELECT TABLE_NAME
                FROM information_schema.TABLES
                WHERE
                    TABLE_SCHEMA = SCHEMA()
                    AND TABLE_NAME = v_table
        );
    ELSE
        RETURN EXISTS (
            SELECT TABLE_NAME
                FROM information_schema.TABLES
                WHERE
                    TABLE_SCHEMA = v_schema
                    AND TABLE_NAME = v_table
        );
    END IF;
END;

And now, let’s test it:

MariaDB [test]> SET @params := JSON_OBJECT(
    ->     'table', 'TABLES'
    -> );
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT table_exists(@params);
+-----------------------+
| table_exists(@params) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.001 sec)

MariaDB [test]>
MariaDB [test]> SET @params := JSON_OBJECT(
    ->     'schema', 'information_schema',
    ->     'table', 'TABLES'
    -> );
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> SELECT table_exists(@params);
+-----------------------+
| table_exists(@params) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.001 sec)

 Jolly good, it works!

What we did here is simple. We’ve created a JSON object withJSON_OBJECT(), and we’ve read its properties withJSON_EXTRACT(). The JSON_EXTRACT() function takes a JSON document followed by a path parameter. To access an object members, the path syntax is'$.property_name'.

Notes on MySQL syntax

In MySQL we must use JSON for the params parameter, instead of BLOB. Otherwise, we will get an error:

ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'

Also, we can use a syntax sugar. Instead of:

JSON_UNQUOTE(JSON_EXTRACT(params, '$.table'))

we can write:

params->>'$.table'

Syntax sugar tends to hide some meaningful complexity, and this particular one is only supported in MySQL. But still.

For more information on how to use JSON objects see: Working with JSON objects in MySQL/MariaDB.

Variable number of parameters

As mentioned before, a variable number of parameters can just be implemented with an array of parameters. Here’s a JSON example:

CREATE FUNCTION number_stats(in_numbers BLOB)
    RETURNS INTEGER
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Accept an array of integers and their median'
BEGIN
    DECLARE v_count INT UNSIGNED
        DEFAULT JSON_LENGTH(in_numbers);
    RETURN JSON_EXTRACT(
        in_numbers,
        CONCAT('$[', FLOOR(v_count / 2), ']')
    );
END;

What we did here is not much different from the former example. We used the JSON_ARRAY() function instead ofJSON_OBJECT(). Then we used JSON_LENGTH() to find the number of items in the array. It is worth noting that this function also works with objects. Also, if we accept nested arrays/objects as parameters, it still serves the purpose because it is not recursive. Finally, we used JSON_EXTRACT() with a different syntax, to extract an elements of the array: '$[10]'.

For more info on how to work with arrays: Working with JSON arrays in MySQL/MariaDB.

Validating parameters

You may have noticed that one of the problems with this solution is that there is no automated validation. A normal parameter must be of a certain type. If the type is not a string type, and the procedure was created with a strict SQL_MODE, trying to pass a wrong type will result in an error. A JSON document however does not have a schema, so its properties can be of any type.

However, there are still some ways to do some basic validation:

  • MySQL JSON type automatically validates documents. In MariaDB we can use the JSON_VALID() function.
  • We can assume that the output of JSON_OBJECT() and JSON_ARRAY() is always valid (or NULL).
  • For nested objects/arrays, we can also useJSON_TYPE(), that returns the JSON type of a property/element.
  • Sometimes, JSON_LENGTH() will be sufficient.

See also

Related articles:

Related courses:

Conclusions

Optional parameters and variable number of parameters are not really implemented for MySQL/MariaDB stored procedures.

Why? MySQL initially implemented the minimal procedural language set that was necessary for the SAP world. Unfortunately, they never went much beyond that. Very basic features are missing, like external languages, or a real debugger. Even performance is quite poor (though I’d argue that stored procedures are by nature the most performant solution in some cases).

Those flaws are also in MariaDB. Yet… there is some home for the future. MariaDB implemented a parser for Oracle procedures – even though I think it is not yet properly documented. Probably this feature was only developed to make migrations from Oracle to MariaDB easier. But they added other minor features, like EXECUTE IMMEDIATE statement and anonymous blocks.

MySQL tends to implement interesting MariaDB features – and to be clear, the opposite is also true. So, at some point, their competition may extend to this area.

Optional parameters and variable number of parameters are good examples of missing features that can easily be emulated by the users. They make stored procedures more flexible, allowing us to develop procedures that are easier to use and adapt to more use cases. This is very important when developing procedures designed to be reused in different contexts, like an open source library.

If you have more ideas about this topic, please share your thoughts – or your code – in a comment.

And if you found this article useful,
you may consider to contact me for the training:
MySQL Stored Procedures

Toodle pip,
Federico

 

Comments (11)

  1. Pingback: 艾德資訊
  2. These are exactly the solutions I’ve been pondering myself for this problem – JSON or MariaDB dynamic columns! Thank you for this article and writing up the code, much appreciated.

    As for optional parameters being implemented in MariaDB or MySQL at some point, can I suggest that everyone creates an account in the MariaDB Jira and votes for this ticket?
    https://jira.mariadb.org/browse/MDEV-10862
    Or similar for MySQL:
    https://bugs.mysql.com/bug.php?id=15975

    1. Thank you for your feedback, Karl. Adding feature requests about features that are important for us is a good practice indeed. Honestly I think that a better solution would be to implement external languages, then we could simply use a language that allows optional parameters. There is already a very old community patch for this, but it was never merged for some reason.

      1. External languages would be amazing! Is there a feature request for it?

        I discovered someone (!!!) had already made a feature request titled “Request: Variable number of arguments in Stored Routines” back in 2012! This request is a bit different from optional parameters, suggesting their approach is more powerful! I don’t really see the big advantage myself, but I decided to link the two tickets anyway. https://jira.mariadb.org/browse/MDEV-524

        1. Yes I made that request, but don’t hold your breath… probably they will never do it.
          For external languages, I remember this very old MySQL workload:
          https://dev.mysql.com/worklog/task/?id=820
          There was also an implementation from Antony Curtis. It worked with multiple languages. Unfortunately, MySQL and MariaDB did not merge it, and probably nowadays that code is obsolete.

          1. External Language Stored Procedure feature never was merged due to lacklustre commitment from MySQL/MariaDB. In spite of being able to demonstrate Perl, C, C++, XMLRPC, Java and Python, there was always just one more language that was required before merge. I maintained a “fork” for a couple of years but that took a lot of effort to maintain. The only part of the work which survived is visible today in MariaDB as compound statement feature. I can revive it but only if there’s interest.

          2. From users, there is interest for sure.
            I am curious, do you think that your code would still be revived with reasonable effort even after the changes made by MariaDB to implement a PL/SQL parser?

  3. When I originally commented I clicked the -Notify me when new feedback are added- checkbox and now each time a comment is added I get four emails with the identical comment. Is there any manner you’ll be able to remove me from that service? Thanks!

    1. Kelley, I would be happy to help, but WordPress doesn’t give me a way to cancel your subscription. However, I am reasonably sure that every email you receive has a link to unsubscribe from these update. If I’m wrong, please let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *