大网数据广告活动
数据库部署与 SQL 参考

Apache Doris 中文参考:EXPORT_SET

· 52CE 我爱测

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

描述

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。

举例

CREATE TABLE test_export_set (`bits` BIGINT, `on` VARCHAR(20), `off` VARCHAR(20), `sep` VARCHAR(20), `num_of_b` INT) DISTRIBUTED BY HASH(`bits`) BUCKETS 1 PROPERTIES("replication_num"="1");
INSERT INTO test_export_set VALUES (-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(-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 `bits`, `on`, `off`, `sep`, `num_of_b`,
       EXPORT_SET(`bits`, `on`, `off`, `sep`, `num_of_b`) AS ans
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 |
+---------------------------------------------------------------------------------------------------------------------------------+

本文来自 Apache Doris 文档贡献者,按 Apache 2.0 整理版式与相对链接,保留正文与代码。图示请参阅原文。作者与贡献记录。

资料来源与适用范围

资料来源
Apache Doris 文档贡献者
适用范围
master 文档快照(2026-11-16)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
Apache 2.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。