# 数组

## 数组使用

### 数组定义

``````-- 一维数组
create table t0(
c0 INT,
c1 ARRAY<INT>
)
duplicate key(c0)
distributed by hash(c0) buckets 3;  -- 以分3个桶为例。

-- 定义嵌套数组
create table t1(
c0 INT,
c1 ARRAY<ARRAY<VARCHAR(10)>>
)
duplicate key(c0)
distributed by hash(c0) buckets 3;``````

``````create table t2(
c0 INT,
c1 ARRAY<INT> NOT NULL
)
duplicate key(c0)
distributed by hash(c0) buckets 3;``````

• 只能在duplicate table中定义数组列
• 数组列不能作为key列(以后可能支持)
• 数组列不能作为distribution列
• 数组列不能作为partition列

### 在SQL中构造数组

``````select [1, 2, 3] as numbers;
select ["apple", "orange", "pear"] as fruit;
select [true, false] as booleans;``````

``````select [1, 1.2] as floats;
select [12, "100"]; -- 结果是 ["12", "100"]``````

``````select ARRAY<float>[1, 2];
select ARRAY<INT>["12", "100"]; -- 结果是 [12, 100]``````

``select [1, NULL];``

``````select [];
select ARRAY<VARCHAR(10)>[];
select array_append([], 10);``````

### 数组导入

• INSERT INTO

``````create table t0(c0 INT, c1 ARRAY<INT>)duplicate key(c0);
INSERT INTO t0 VALUES(1, [1,2,3]);``````
• 从ORC/Parquet文件导入

StarRocks 中的数组类型，与ORC/Parquet格式中的list结构相对应，不需要额外指定，具体请参考StarRocks 企业文档中 `broker load` 导入相关章节。当前ORC的list结构可以直接导入，Parquet格式正在开发中。

• 从CSV文件导入

CSV 文件导入数组，默认采用逗号分隔，可以用 stream load / routine load 导入CSV文本文件或 Kafka 中的 CSV 格式数据。

### 数组元素访问

``````mysql> select [1,2,3][1];

+------------+
| [1,2,3][1] |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)``````

``````mysql> select [1,2,3][0];

+------------+
| [1,2,3][0] |
+------------+
|       NULL |
+------------+
1 row in set (0.01 sec)``````

``````mysql> select [1,2,3][4];

+------------+
| [1,2,3][4] |
+------------+
|       NULL |
+------------+
1 row in set (0.01 sec)``````

``````mysql(ARRAY)> select [[1,2],[3,4]][2];

+------------------+
| [[1,2],[3,4]][2] |
+------------------+
| [3,4]            |
+------------------+
1 row in set (0.00 sec)

mysql> select [[1,2],[3,4]][2][1];

+---------------------+
| [[1,2],[3,4]][2][1] |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.01 sec)``````