EXPORT_SET

描述

EXPORT_SET 用于将一个整数的每一位（bit）转换为指定的字符串，并拼接成结果字符串。对于 bits 中每个为 1 的位，结果中对应位置会显示 on 字符串；每个为 0 的位则显示 off 字符串。位的检查顺序是从右到左（即从低位到高位），而拼接到结果字符串时是从左到右。各位之间通过 separator 分隔（默认为逗号,）。

展示的位数由 number_of_bits 参数决定，默认展示 64 位。如果未指定或超出超出范围[0, 64]时自动按展示 64 位处理。

该函数行为与MySQL的EXPORT_SET函数 一致。

语法

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

参数

参数	说明
bits	用于转换的整数类型(LARGEINT)。若该值小于 -2^63 则按 -2^63 处理，若大于 2^64 - 1 则按 2^63 - 1 处理
on	指定 bit 为 1 时显示的字符串
off	指定 bit 为 0 时显示的字符串
separator	可选，分隔各位的字符串，默认为 ','
number_of_bits	可选，结果展示的位数，支持整数类型(INT)。默认为 64，超出范围[0, 64]时自动按默认值处理

返回值

返回一个字符串。对于 bits 的每一位，从低位到高位，若为 1 则用 on 替换，若为 0 则用 off 替换，并按顺序拼接。各位之间用 separator 分隔，最终展示 number_of_bits 位。

若number_of_bits超过范围或函数中任一参数为 NULL，则返回 NULL。

举例

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

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

SELECT EXPORT_SET(5, '1', '0', '||', 5);

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

SELECT `bits`, `on`, `off`, `sep`, `num_of_b`
FROM `test_export_set`;

+-------------+--------+-------+-------+----------+
| bits        | on     | off   | sep   | num_of_b |
+-------------+--------+-------+-------+----------+
|          -1 | 1      | 0     | ,     |       50 |
|          -2 | 1      | 0     |       |       64 |
|           5 | Y      | N     | ,     |        5 |
|           5 | 1      | 0     |       |       64 |
|           5 |        | 0     |       |       65 |
|           6 | 1      |       |       |       63 |
| 19284249819 | 1      | 0     | ,     |       64 |
|           9 | apache | doris | |123| |       64 |
|        NULL | 1      | 0     | ,     |        5 |
|           5 | NULL   | 0     |       |        5 |
|           5 | 1      | NULL  | ,     |       10 |
|           5 | 1      | 0     | NULL  |       10 |
|           5 | 1      | 0     | ,     |     NULL |
+-------------+--------+-------+-------+----------+

SELECT EXPORT_SET(`bits`, `on`, `off`, `sep`, `num_of_b`)
FROM `test_export_set`;

+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bits        | on     | off   | sep   | num_of_b | ans                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|          -1 | 1      | 0     | ,     |       50 | 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|          -2 | 1      | 0     |       |       64 | 0111111111111111111111111111111111111111111111111111111111111111                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|           5 | Y      | N     | ,     |        5 | Y,N,Y,N,N                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|           5 | 1      | 0     |       |       64 | 1010000000000000000000000000000000000000000000000000000000000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|           5 |        | 0     |       |       65 | 00000000000000000000000000000000000000000000000000000000000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|           6 | 1      |       |       |       63 | 11                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 19284249819 | 1      | 0     | ,     |       64 | 1,1,0,1,1,0,1,1,0,0,1,1,0,0,1,0,0,1,1,1,0,1,1,0,1,0,1,1,1,1,1,0,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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|           9 | apache | doris | |123| |       64 | apache|123|doris|123|doris|123|apache|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris |
|        NULL | 1      | 0     | ,     |        5 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | NULL   | 0     |       |        5 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | 1      | NULL  | ,     |       10 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | 1      | 0     | NULL  |       10 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | 1      | 0     | ,     |     NULL | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- max value: 2^64 - 1
SELECT EXPORT_SET(18446744073709551615, '1', '0');

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

-- 2^64 out of bound, the bits will be set to 2^63 - 1
SELECT EXPORT_SET(18446744073709551616, '1', '0');

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

-- min value: -2^63
SELECT EXPORT_SET(-9223372036854775808, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(-9223372036854775808, '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,0,0,0,0,1 |
+---------------------------------------------------------------------------------------------------------------------------------+

-- out of bound, the bits will be set to -2^63
SELECT EXPORT_SET(-184467440737095516161, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(-184467440737095516161, '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,0,0,0,0,1 |
+---------------------------------------------------------------------------------------------------------------------------------+