How EXPORT_SET() function works in MySQL?

It is also a MySQL string function used for searching. With the help of following syntax of EXPORT_SET() function we can understand its working −

Syntax

EXPORT_SET(bits, on, off, separator, number of bits)

Here in this syntax,

Bits (got after converting the integer to bits) are to be compared if they are 1 or 0.

‘On’ is the second argument of the function, have any value in the quote and it will be returned if the value of a bit, in the first argument, is 1.

‘Off’ is the third argument of the function, have any value in the quote and it will be returned if the value of a bit, in the first argument, is 0.

The separator is used to be placed between the returned values while displaying the output. It can be any character or value placed in quotes.

A number of bits have the value of bits which are to be compared if they are 1 or 0.

Working of EXPORT_SET() function

It works as follows −

• First of all, the integer value given as 1st argument would be converted into a number of bits (i.e. in 0 and 1s). Then every bit is checked whether it is 1 or 0. The order of checking is right to left.
• Now, MySQL decides what to do, after checking, when the bit is 1 or 0. If the bit is 1, it returns the second argument i.e. ‘on’. And if the bit is 0, it returns the third argument i.e. ‘off’.
• The checking continues up to the number of bits specified in the fifth argument.
• Now, based on the above MySQL will return the output and place the separator between the returned values. The values would be returned from left to right.

Example

mysql> Select EXPORT_SET(8, '1','0',',', 8);

+-------------------------------+
| EXPORT_SET(8, '1','0',',', 8) |
+-------------------------------+
| 0,0,0,1,0,0,0,0               |
+-------------------------------+

1 row in set (0.00 sec)

mysql> Select EXPORT_SET(8, '1','0',',', 4);

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

1 row in set (0.00 sec)

In both the examples, we have given 8 as the first argument which will be converted to bits, i.e. 1000, and then will be checked for 1 an 0 up to 8 bits (in 1st example) and up to 4 bits (in second example) because 8 and 4 are specified as the fifth argument in 1st and 2nd example respectively. While displaying the output we can observe that comma (,) is used as a separator because it is specified as the fourth argument in both the examples.

As we know that the order of checking is from Right to Left i.e. in our example 1000, the rightmost bit is 0 hence the third argument i.e. 0 would be returned, then again there are 0s at next two places hence another two 0s would be returned, then it is 1 hence the second argument i.e. 1 has returned. In the 2nd example, we received the output up to 4 digits because checking has been done up to four digits but in 1st example, it is up to 8 digits hence we received 8 digits in the output. The order of output is from left to right hence the output is displayed as 0,0,0,1,0,0,0,0 in 1st example and 0,0,0,1 in 2nd example.