How the EXPORT_SET() Function Works in MySQL

The MySQL EXPORT_SET() function returns a string that represents the bits in a number.

You have the ability to customise how the function outputs the string. The function accepts a number of arguments that allows you to do this. When you call the function, you provide the number in question, as well as arguments that determine how the results are displayed.

Syntax

The syntax goes like this:

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

Here’s an explanation of the arguments:

bits

This is the number for which you’d like the results returned. For every bit that’s set in this value you get an on string, and for every bit that’s not set in the value, you get an off string. The bits are examined from right to left (from low-order to high-order bits).

on

This is what’s returned for any on bits.

off

This is what’s returned for any off bits.

separator

This is an optional argument that you can use to specify the separator to use. The default value is the comma character. Therefore, if you don’t specify this argument a comma is used as the separator.

number_of_bits

The number of bits to examine. The default value is 64. If you provide a larger value, this is silently clipped to 64 if larger than 64.

Example 1 – Basic Usage

Here’s an example to demonstrate the basic usage of this function.

SELECT EXPORT_SET(9,'On','Off',',',4);

Result:

+--------------------------------+
| EXPORT_SET(9,'On','Off',',',4) |
+--------------------------------+
| On,Off,Off,On                  |
+--------------------------------+

In order to understand this result, we need to understand how the number 9 is represented in bits. To do this, we can use the BIN() function to output the binary representation of the number 9.

SELECT BIN(9);

Result:

+--------+
| BIN(9) |
+--------+
| 1001   |
+--------+

So we can see that the binary representation of 9 is 1001. Each 1 is an on bit (it’s set) and each 0 is an off bit (it’s not set).

Example 2 – Change the 2nd and 3rd Arguments

We can take the previous example and change the second and third arguments.

SELECT EXPORT_SET(9,'Y','N',',',4);

Result:

+-----------------------------+
| EXPORT_SET(9,'Y','N',',',4) |
+-----------------------------+
| Y,N,N,Y                     |
+-----------------------------+

So now for each on bit, we get Y, and each off bit returns N.

We could even change it so that ones and zeros are returned:

SELECT EXPORT_SET(9,1,0,',',4);

Result:

+-------------------------+
| EXPORT_SET(9,1,0,',',4) |
+-------------------------+
| 1,0,0,1                 |
+-------------------------+

Example 3 – Change the 4th Argument (the separator)

In the previous examples we explicitly specify a comma as being the separator. This is also the default value.

We can change the separator to something else if required.

SELECT EXPORT_SET(9,1,0,'-',4);

Result:

+-------------------------+
| EXPORT_SET(9,1,0,'-',4) |
+-------------------------+
| 1-0-0-1                 |
+-------------------------+

And here’s what happens if we specify the empty string as the separator:

SELECT EXPORT_SET(9,1,0,'',4);

Result:

+------------------------+
| EXPORT_SET(9,1,0,'',4) |
+------------------------+
| 1001                   |
+------------------------+

Example 4 – Change the 5th Argument

The fifth argument specifies how many bits to examine. In the previous example we used 4 as the value, so only four bits were examined (and returned). We can increase or decrease this as required, by specifying a different value as the fourth argument.

SELECT EXPORT_SET(9,1,0,'-',10);

Result:

+--------------------------+
| EXPORT_SET(9,1,0,'-',10) |
+--------------------------+
| 1-0-0-1-0-0-0-0-0-0      |
+--------------------------+

In this example we increased the number of bits examined to 10.

Remember, the bits are examined from right to left, so the extra zeros in this example are actually for the high-order zeros. So it’s in reverse order to the actual binary representation.

Here’s an example to demonstrate what I mean:

SELECT 
  BIN(567),
  EXPORT_SET(567,1,0,'',10);

Result:

+------------+---------------------------+
| BIN(567)   | EXPORT_SET(567,1,0,'',10) |
+------------+---------------------------+
| 1000110111 | 1110110001                |
+------------+---------------------------+

In this case, they look like mirror images. Basically, the result of EXPORT_SET() is in reverse order to BIN().

The default value for the fifth argument is 64, so if we omit this argument, that’s how many bits will be examined.

SELECT EXPORT_SET(9,1,0,'-');

Result:

+---------------------------------------------------------------------------------------------------------------------------------+
| 1-0-0-1-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0 |
+---------------------------------------------------------------------------------------------------------------------------------+

This is silently clipped to 64 so if you provide a value outside this range, it will be clipped to 64.

SELECT EXPORT_SET(9,1,0,'-',500);

Result:

+---------------------------------------------------------------------------------------------------------------------------------+
| 1-0-0-1-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0 |
+---------------------------------------------------------------------------------------------------------------------------------+

It’s an unsigned integer, so same result if you use a negative value:

SELECT EXPORT_SET(9,1,0,'-',-4);

Result:

+---------------------------------------------------------------------------------------------------------------------------------+
| 1-0-0-1-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0 |
+---------------------------------------------------------------------------------------------------------------------------------+

Example 5 – Using Default Values

We can omit the last two arguments in order to use the default values:

SELECT EXPORT_SET(9,'On','Off');

Result:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| On,Off,Off,On,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off,Off |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+