虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


数组数据类型 ​

📄字数 3.8K
👁️阅读量 加载中...

一、简介 ​

ARRAY类型允许将表的列定义为可变长多维数组，数组允许在单个数据库字段中存储多个值，ARRAY数据类型支持虚谷数据库基本数据类型。

由于ARRAY数组类型的特性，在利用数据库处理列表或数据集等场景中，ARRAY类型可以发挥良好的作用，高效的实现场景下的特定需求。

二、实现 ​

ARRAY数据类型在XuguDB中有两种使用方式，分别为字符串类型的数组与ARRAY构造函数数组。两种使用方式通过不同的逻辑接口，统一转换为由BLOB大对象管理结构管理的ArrayType，最大支持2GB输入文本。

ARRAY数据类型是数据库内置的基础类型，支持在表、视图中创建ARRAY类型字段，也支持创建ARRAY类型的常量与变量，两者之间的声明方式略有不同。

ARRAY数据类型相关系统函数的详细信息请参见 数组函数 章节。

三、声明 ​

3.1 非字段类型数组 ​

ARRAY类型结构在非字段中有两种声明方式，分别是构造函数声明和字符串声明。

  • 构造函数声明：
sql
-- 一维数组
ARRAY[v1, v2, v3...];

-- 多维数组，以三维数组为例
ARRAY[[[v1, v2, v3...]]];
  • 字符串声明：
sql
-- 一维数组
'{v1, v2, v3}';
-- 多维数组，以三维数组为例
'{{{v1, v2, v3}}}';

在使用方式上，两者的区别有以下几点：

  • 由ARRAY构造函数声明的数组，维度是用成对的方括号进行标识的；字符串声明方式下的数组，是以大括号进行数组维度标识的。最大支持六维数组的声明与使用。
  • 由字符串声明的数组由于设计原因，一些本身存在大括号的数据类型暂时不支持以此方式使用。
  • 由字符串声明的数组使用仅支持一些明确类型的场景，例如字段插入、更新、删除，并且不支持通过类型强制转换的方式转为数组类型。
  • 由ARRAY构造函数声明的数组当成员为字符串类型时需要添加成对单引号进行包裹，声明为特定类型时，需要指明具体的数据类型，例如：
sql
-- CHAR类型数组
SELECT ARRAY['aaa', 'bbb']; 

-- DATE类型数组
SELECT ARRAY['2024-01-01'::DATE, '2024-12-31'::DATE];

3.2 字段类型数组 ​

ARRAY类型结构在字段中可通过在数据类型后添加方括号[]的方式进行声明。

sql
column_name type_name[]
  • column_name：字段的名称。
  • type_name：基础数据类型的名称。
  • []：表示这是一个数组类型。可以根据需要添加多个方括号来声明多维数组。数组维度与类型名后的方括号对数量相同，例如VARCHAR[][][]表示三维数组。

ARRAY类型支持多种基础类型作为成员数据，支持的基础类型以及其一维数组的声明方式如下表所示：

数据类型声明方式
integerinteger[]
tinyinttinyint[]
smallintsmallint[]
bigintbigint[]
floatfloat[]
doubledouble[]
numericnumeric[]
datedate[]
datetimedatetime[]
datetime with time zonedatetime with time zone[]
timetime[]
time with time zonetime with time zone[]
charchar[]
varcharvarchar[]
BLOBBLOB[]
CLOBCLOB[]
interval dayinterval day[]
interval day to hourinterval day to hour[]
interval day to minuteinterval day to minute[]
interval day to secondinterval day to second[]
interval hourinterval hour[]
interval hour to minuteinterval hour to minute[]
interval hour to secondinterval hour to second[]
interval minuteinterval minute[]
interval monthinterval month[]
interval minute to secondinterval minute to second[]
interval secondinterval second[]
interval yearinterval year[]
interval year to monthinterval year to month[]
pointpoint[]
boxbox[]
circlecircle[]
polygonpolygon[]
pathpath[]
lineline[]
lseglseg[]
xmlxml[]

为了更好的说明ARRAY类型在字段上的用法，以数据库中建表为例：

  • 示例1
    创建一个名为array_types的表，其中包含INTEGER一维数组类型字段和VARCHAR二维字符类型字段。
sql
CREATE TABLE array_types (
 name VARCHAR,
 int_arr INTEGER[],
 var_arr_2 VARCHAR[][]
);

提示

在声明数组类型时，[]中可以指定参数大小，但此行为无效！与未指定大小结果相同。

  • 示例2
    若声明一维数组，数据库提供构造函数的声明方式。
sql
-- 添加大小参数
CREATE TABLE array_types (
 name VARCHAR,
 int_arr INTEGER ARRAY[3],
 var_arr_2 VARCHAR[][]
);

-- []中不添加参数时不单独使用[]，int_arr INTEGER ARRAY[]会导致语法错误
CREATE TABLE array_types (
 name VARCHAR,
 int_arr INTEGER ARRAY,
 var_arr_2 VARCHAR[][]
);

注意

构造函数声明方式仅用于字段内的一维数组，使用此声明方式需注意：参数为空时禁止使用ARRAR[]，需使用ARRAY。

四、数组的增删改查 ​

4.1 插入 ​

ARRAY类型结构支持使用构造函数ARRAY和使用{}字符串作为输入。

以输入一维数组与二维数组为例，更高维度的输入同样遵循此原则。

  • {}字符串输入：
sql
INSERT INTO array_types VALUES('int', '{1, 2, 3}', '{{1, 2, 3}, {4, 5, 6},{7, 8, 9}}');
  • 构造函数ARRAY输入：
sql
INSERT INTO array_types VALUES('int', ARRAY[1, 2, 3], ARRAY[[1, 2, 3], [4, 5,6], [7, 8, 9]]);

4.2 查询 ​

本小节以一维数组表和三维数组表为例进行说明：

sql
-- 创建一维数组表并插入数据
SQL> CREATE TABLE test_arr (a int[]);
SQL> INSERT INTO test_arr VALUES ('{1, 2, 3}');

-- 创建三维数组表并插入2x2x3的三维数组
SQL> CREATE TABLE test_arr_3 (a int[][][]);
SQL> INSERT INTO test_arr_3 VALUES ('{{{1, 2, 3}, {4, 5, 6}}, {{7, 8, 9}, {10, 11, 12}}}');

直接查询字段名即可查询整组字段信息。

  • 查询一维数组
sql
SQL> SELECT a FROM test_arr;
+---------+
|    A    |
+---------+
| {1,2,3} |
+---------+
  • 查询多维数组
sql
SQL> SELECT a FROM test_arr_3;
+------------------------------------------+
|                    A                     |
+------------------------------------------+
| {{{1,2,3},{4,5,6}},{{7,8,9},{10,11,12}}} |
+------------------------------------------+

通过字段名[数组下标]的方式进行查询。

  • 查询一维数组的1号元素
sql
SQL> SELECT a[1] FROM test_arr;
+-------+
| EXPR1 |
+-------+
| 1     |
+-------+
  • 查询多维数组，a[1][2][3]访问的是第一个维度的第一个元素（即第一个2x3的二维数组[[1, 2, 3], [4, 5, 6]]），第二个维度的第二个元素（即第二行[4, 5, 6]），第三个维度的第三个元素（即 6）
sql
SQL> SELECT a[1][2][3] FROM test_arr_3;
+-------+
| EXPR1 |
+-------+
| 6     |
+-------+

提示

更高维的数组元素查询方式与之类似，方括号的数量需要与表中数据的维度匹配。

以切片字段名[起始下标:结束下标]的方式查询数组字段中的部分数据。

  • 查询一维数组中第1到第2个元素
sql
SQL> SELECT a[1:2] FROM test_arr;
+-------+
| EXPR1 |
+-------+
| {1,2} |
+-------+
  • 查询多维数组，a[1:2][1:2][1:2]表示查询第一个维度的第1到第2个元素、第二个维度的第1到第2个元素以及第三个维度的第1到第2个元素
sql
SQL> SELECT a[1:2][1:2][1:2] FROM test_arr_3;
+---------------------------------+
|              EXPR1              |
+---------------------------------+
| {{{1,2},{4,5}},{{7,8},{10,11}}} |
+---------------------------------+

注意：

多维数组的切片查询也应当与表中字段的维度匹配。当切片查询的结束下标超过当前字段中数组的个数时，数据库将以最大的元素个数修正结束下标。

4.3 更新 ​

在更新指定下标位置的元素时，需要在字段名后添加想要修改元素的位置下标。

  • 一维数组
sql
SQL> UPDATE test_arr SET a[1] = 4;

SQL> SELECT a, a[1] FROM test_arr;
+---------+-------+
|    A    | EXPR1 |
+---------+-------+
| {4,2,3} | 4     |
+---------+-------+
  • 多维数组
sql
SQL> UPDATE test_arr_3 SET a[1][1][1] = 4;

SQL> SELECT a, a[1][1][1] FROM test_arr_3;
+------------------------------------------+-------+
|                    A                     | EXPR1 |
+------------------------------------------+-------+
| {{{4,2,3},{4,5,6}},{{7,8,9},{10,11,12}}} | 4     |
+------------------------------------------+-------+
  • 使用切片更新
sql
SQL> UPDATE test_arr_3 SET a[1:1][1:1][1:2] = '{{{4, 5}}}';

SQL> SELECT a, a[1:1][1:1][1:2] FROM test_arr_3;
+------------------------------------------+-----------+
|                    A                     |   EXPR1   |
+------------------------------------------+-----------+
| {{{4,5,3},{4,5,6}},{{7,8,9},{10,11,12}}} | {{{4,5}}} |
+------------------------------------------+-----------+

通过以上方式，可以对指定位置的元素进行更新。

4.4 删除 ​

数组类型支持使用DELETE语句结合WHERE条件删除数组类型字段数据。

  • 通过全等于删除
sql
SQL> SELECT * FROM test_arr_1;

+---------+
|    A    |
+---------+
| {4,2,3} |
+---------+

SQL> DELETE test_arr_1 WHERE a = ARRAY[4,2,3];

SQL> SELECT * FROM test_arr_1;

+---+
| A |
+---+
+---+
  • 通过下标等于删除
sql
SQL> select * from test_arr_1;

+---------+
|    A    |
+---------+
| {4,2,3} |
| {1,2,3} |
+---------+

SQL> delete test_arr_1 where a[1] = 4;

SQL> select * from test_arr_1;

+---------+
|    A    |
+---------+
| {1,2,3} |
+---------+
  • 通过切片等于删除
sql
SQL> select * from test_arr_1;

+---------+
|    A    |
+---------+
| {1,2,3} |
| {4,2,3} |
+---------+

SQL> delete test_arr_1 where a[1:2] = array[1, 2];

SQL> select * from test_arr_1;

+---------+
|    A    |
+---------+
| {4,2,3} |
+---------+

注意

目前数组类型通过DELETE语句删除时，仅能删除整个字段的数组。删除部分元素或切片，详情可参考 数组函数。