CUBRID数据类型之集合类型SET,MULTISET,LIST or SEQUENCE

本文链接：https://blog.youkuaiyun.com/nhn_devlab/article/details/6031987

本文详细介绍了数据库中SET、MULTISET和LIST三种集合类型的特性及使用方法，并通过实例演示了它们之间的相互转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.下面先以对比方式介绍一下三种集合类型特征（其中LIST和SEQUENCE指的是同一种类型，只是名称不同）

TYPE	DESCRIPTION	DEFINITION	INPUT DATA	STORED DATA
SET	不允许重复数据先按照定义的类型顺序再按照从小到大的顺序存储	col_name SET VARCHAR(20)	{'c','c','c','b','b', 'a'}	{'a','b','c'}
SET	不允许重复数据先按照定义的类型顺序再按照从小到大的顺序存储	col_name SET (int,VARCHAR(20))	{3,3,3,2,2,1,0,'c','c','c','b','b', 'a'} {3,3,2,'d',2,1,0,'c','c', 3,5,'b','b','a'}	{0,1,2,3,'a','b','c'} {0, 1, 2, 3, 5, 'a', 'b', 'c', 'd'}
MULTISET	允许重复数据先按照定义的类型顺序再按照从小到大的顺序存储	col_name MULTISET VARCHAR(20)	{'c','c','c','b','b', 'a'}	{'a','b','b','c','c','c'}
MULTISET	允许重复数据先按照定义的类型顺序再按照从小到大的顺序存储	col_name MULTISET (int, VARCHAR(20))	{3,3,3,2,2,1,0,'c','c','c','b','b', 'a'} {3,3,2,'d',2,1,0,'c','c', 3,5,'b','b','a'}	{0,1,2,2,3,3,3,'a','b','b', 'c','c','c'} {0, 1, 2, 2, 3, 3, 3, 5, 'a', 'b', 'b', 'c', 'c', 'd'}
LIST SEQUENCE	允许重复数据按输入顺序存储	col_name LIST VARCHAR(20)	{'c','c','c','b','b', 'a'}	{'c','c','c','b','b','a'}
LIST SEQUENCE	允许重复数据按输入顺序存储	col_name LIST (int, VARCHAR(20))	{3,3,3,2,2,1,0,'c','c','c','b','b', 'a'} {3,3,2,'d',2,1,0,'c','c', 3,5,'b','b','a'}	{3,3,3,2,2,1,0,'c','c','c','b','b', 'a'} {3, 3, 2, 'd', 2, 1, 0, 'c', 'c', 3, 5, 'b', 'b', 'a'}

集合类型数据具有如下特点

1) 以一对大括号”{}”作为起始标志，元素与元素之间用逗号”,”分割.

2) 集合内部支持多种数据类型，例如set(int, char(1),date)

3) 集合内部也支持用户自定义类型，如以已存在的表作为数据类型

例如

Create table t1 (t int)

Create table t2 (t set t1)

2. 集合类型之间是支持类型转换的，具体参考下表

	TO
FROM		SET	MULTISET	LIST
	SET	-	O	O
	MULTISET	O	-	O
	LIST	O	O	-

3 举例

1) SET

csql> CREATE TABLE set_tbl ( col_1 set(int, CHAR(1)));

csql> INSERT INTO set_tbl VALUES ({3,3,3,2,2,1,0,'c','c','c','b','b','a'});

csql> INSERT INTO set_tbl VALUES ({NULL});

csql> INSERT INTO set_tbl VALUES ({''});

csql> SELECT * FROM set_tbl;

csql> ;xr

=== <Result of SELECT Command in Line 1> ===

col_1

======================

{0, 1, 2, 3, 'a', 'b', 'c'}

{NULL}

{' '}

csql> SELECT CAST(col_1 AS MULTISET), CAST(col_1 AS LIST) FROM set_tbl;

csql> ;xr

=== <Result of SELECT Command in Line 1> ===

cast(col_1 as multiset) cast(col_1 as sequence)

============================================

{0, 1, 2, 3, 'a', 'b', 'c'} {0, 1, 2, 3, 'a', 'b', 'c'}

{NULL} {NULL}

{' '} {' '}

csql> INSERT INTO set_tbl VALUES ('');

ERROR: Cannot coerce '' to type set.

ERROR: Incompatible data type on attribute col_1.

2) MULTISET

csql> CREATE TABLE multiset_tbl ( col_1 multiset(int, CHAR(1)));

csql> INSERT INTO multiset_tbl VALUES ({3,3,3,2,2,1,0,'c','c','c','b','b', 'a'});

csql> SELECT * FROM multiset_tbl;

csql> ;xr

=== <Result of SELECT Command> ===

col_1

======================

{0, 1, 2, 2, 3, 3, 3, 'a', 'b', 'b', 'c', 'c', 'c'}

csql> SELECT CAST(col_1 AS SET), CAST(col_1 AS LIST) FROM multiset_tbl;

csql> ;xr

=== <Result of SELECT Command in Line 1> ===

cast(col_1 as set) cast(col_1 as sequence)

============================================

{0, 1, 2, 3, 'a', 'b', 'c'} {0, 1, 2, 2, 3, 3, 3, 'a', 'b', 'b', 'c', 'c', 'c'}

3) LIST or SEQUENCE

csql> CREATE TABLE list_tbl ( col_1 list(int, CHAR(1)));

csql> INSERT INTO list_tbl VALUES ({3,3,3,2,2,1,0,'c','c','c','b','b', 'a'});

csql> SELECT * FROM list_tbl;

csql> ;xr

=== <Result of SELECT Command in Line 1> ===

col_1

======================

{3, 3, 3, 2, 2, 1, 0, 'c', 'c', 'c', 'b', 'b', 'a'}

csql> SELECT CAST(col_1 AS SET), CAST(col_1 AS MULTISET) FROM list_tbl;

csql> ;xr

=== <Result of SELECT Command in Line 1> ===

cast(col_1 as set) cast(col_1 as multiset)

============================================

{0, 1, 2, 3, 'a', 'b', 'c'} {0, 1, 2, 2, 3, 3, 3, 'a', 'b', 'b', 'c', 'c', 'c'}