How MAKE_SET() Works in MariaDB

In MariaDB, MAKE_SET() is a string function that returns a set value, based on the values provided in its arguments.

A set is a string containing substrings separated by comma (,) characters.

Syntax

The syntax goes like this:

MAKE_SET(bits,str1,str2,...)

Where str1, str2,... is one or more string values and bits specifies which of those string values to include in the set.

MAKE_SET() returns a set value consisting of the strings that have the corresponding bit in bits set.

Example

Here’s an example to demonstrate:

SELECT MAKE_SET( 1, 'a', 'b', 'c', 'd' );

Result:

+-----------------------------------+
| MAKE_SET( 1, 'a', 'b', 'c', 'd' ) |
+-----------------------------------+
| a                                 |
+-----------------------------------+

Note that it uses the binary representation of the first argument to return the applicable strings in the subsequent arguments. Therefore, it’s not as simple as saying, the first argument was 1 and so that corresponded to item 1.

Here’s another example that illustrates what I mean:

SELECT MAKE_SET( 4, 'a', 'b', 'c', 'd' );

Result:

+-----------------------------------+
| MAKE_SET( 4, 'a', 'b', 'c', 'd' ) |
+-----------------------------------+
| c                                 |
+-----------------------------------+

How it Works

Consider the following:

SELECT 
    BIN(1) AS '1', 
    BIN(2) AS '2', 
    BIN(3) AS '3', 
    BIN(4) AS '4', 
    BIN(5) AS '5', 
    BIN(6) AS '6', 
    BIN(7) AS '7', 
    BIN(8) AS '8', 
    BIN(9) AS '9', 
    BIN(10) AS '10';

Result (using vertical output):

Here, I use the BIN() function to return each number’s binary value.

We can see that the binary representation of 4 is 100. We need to visualize this backwards in order to apply it to our MAKE_SET() example above. In our case, this is a three-digit binary value, with the right-most digit corresponding to the first string, the next digit corresponds to the second string, and the leftmost digit corresponds with the third string.

In binary terms, 1 is “on” and 0 is “off”. The MAKE_SET() function only returns strings that have a corresponding 1 in their binary value. Therefore, our example above returns the third string.

Here’s another example using a different value:

SELECT MAKE_SET(10, 'a','b','c','d');

Result:

+-------------------------------+
| MAKE_SET(10, 'a','b','c','d') |
+-------------------------------+
| b,d                           |
+-------------------------------+

In this case, the binary value is 1010. It therefore has two 1s, which correspond to the second and fourth string arguments.

Here are some more examples to demonstrate the concept further:

SELECT 
    MAKE_SET(1, 'a','b','c','d') AS '1', 
    MAKE_SET(2, 'a','b','c','d') AS '2', 
    MAKE_SET(3, 'a','b','c','d') AS '3', 
    MAKE_SET(4, 'a','b','c','d') AS '4', 
    MAKE_SET(5, 'a','b','c','d') AS '5', 
    MAKE_SET(6, 'a','b','c','d') AS '6', 
    MAKE_SET(7, 'a','b','c','d') AS '7', 
    MAKE_SET(8, 'a','b','c','d') AS '8', 
    MAKE_SET(9, 'a','b','c','d') AS '9', 
    MAKE_SET(10, 'a','b','c','d') AS '10';

Result (using vertical output):

 1: a
 2: b
 3: a,b
 4: c
 5: a,c
 6: b,c
 7: a,b,c
 8: d
 9: a,d
10: b,d

Here’s an example using a different set of strings:

SELECT MAKE_SET(3, 'Cat','Bat','Rat');

Result (using vertical output):

+--------------------------------+
| MAKE_SET(3, 'Cat','Bat','Rat') |
+--------------------------------+
| Cat,Bat                        |
+--------------------------------+

Multiple Binary Values

Use a pipe symbol (|) to include more than one value in the set:

SELECT MAKE_SET( 1 | 4, 'Cat', 'Bat', 'Rat' );

Result:

+----------------------------------------+
| MAKE_SET( 1 | 4, 'Cat', 'Bat', 'Rat' ) |
+----------------------------------------+
| Cat,Rat                                |
+----------------------------------------+

Null Values

If a string value is null, then it’s omitted from the result:

SELECT MAKE_SET( 1 | 3, 'Cat', null, 'Rat' );

Result:

+---------------------------------------+
| MAKE_SET( 1 | 3, 'Cat', null, 'Rat' ) |
+---------------------------------------+
| Cat                                   |
+---------------------------------------+