MAP
更新时间：2025-10-17
MAP
name
MAP
描述
MAP<K, V>
由 K, V 类型元素组成的 map，不能作为 key 列使用。目前支持在 Duplicate，Unique 模型的表中使用。
K,V 支持的类型有：
Plain Text
1BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DECIMALV3, DATE,
2DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING举例
建表示例如下：
SQL
1CREATE TABLE IF NOT EXISTS test.simple_map (
2 `id` INT(11) NULL COMMENT "",
3 `m` Map<STRING, INT> NULL COMMENT ""
4) ENGINE=OLAP
5DUPLICATE KEY(`id`)
6DISTRIBUTED BY HASH(`id`) BUCKETS 1
7PROPERTIES (
8"replication_allocation" = "tag.location.default: 1",
9"storage_format" = "V2"
10);插入数据示例：
SQL
1mysql> INSERT INTO simple_map VALUES(1, {'a': 100, 'b': 200});
Shell
1# load the map data from json file
2curl --location-trusted -uroot: -T events.json -H "format: json" -H "read_json_by_line: true" http://fe_host:8030/api/test/simple_map/_stream_load
3# 返回结果
4{
5 "TxnId": 106134,
6 "Label": "5666e573-9a97-4dfc-ae61-2d6b61fdffd2",
7 "Comment": "",
8 "TwoPhaseCommit": "false",
9 "Status": "Success",
10 "Message": "OK",
11 "NumberTotalRows": 10293125,
12 "NumberLoadedRows": 10293125,
13 "NumberFilteredRows": 0,
14 "NumberUnselectedRows": 0,
15 "LoadBytes": 2297411459,
16 "LoadTimeMs": 66870,
17 "BeginTxnTimeMs": 1,
18 "StreamLoadPutTimeMs": 80,
19 "ReadDataTimeMs": 6415,
20 "WriteDataTimeMs": 10550,
21 "CommitAndPublishTimeMs": 38
22}查询数据示例：
SQL
1mysql> SELECT * FROM simple_map;
2+------+-----------------------------+
3| id | m |
4+------+-----------------------------+
5| 1 | {'a':100, 'b':200} |
6| 2 | {'b':100, 'c':200, 'd':300} |
7| 3 | {'a':10, 'd':200} |
8+------+-----------------------------+查询 map 列示例：
SQL
1mysql> SELECT m FROM simple_map;
2+-----------------------------+
3| m |
4+-----------------------------+
5| {'a':100, 'b':200} |
6| {'b':100, 'c':200, 'd':300} |
7| {'a':10, 'd':200} |
8+-----------------------------+map 取值示例：
SQL
1mysql> SELECT m['a'] FROM simple_map;
2+-----------------------------+
3| %element_extract%(`m`, 'a') |
4+-----------------------------+
5| 100 |
6| NULL |
7| 10 |
8+-----------------------------+map 支持的 functions 示例：
SQL
1# map construct
2
3mysql> SELECT map('k11', 1000, 'k22', 2000)['k11'];
4+---------------------------------------------------------+
5| %element_extract%(map('k11', 1000, 'k22', 2000), 'k11') |
6+---------------------------------------------------------+
7| 1000 |
8+---------------------------------------------------------+
9
10mysql> SELECT map('k11', 1000, 'k22', 2000)['nokey'];
11+-----------------------------------------------------------+
12| %element_extract%(map('k11', 1000, 'k22', 2000), 'nokey') |
13+-----------------------------------------------------------+
14| NULL |
15+-----------------------------------------------------------+
161 row in set (0.06 sec)
17
18# map size
19
20mysql> SELECT map_size(map('k11', 1000, 'k22', 2000));
21+-----------------------------------------+
22| map_size(map('k11', 1000, 'k22', 2000)) |
23+-----------------------------------------+
24| 2 |
25+-----------------------------------------+
26
27mysql> SELECT id, m, map_size(m) FROM simple_map ORDER BY id;
28+------+-----------------------------+---------------+
29| id | m | map_size(`m`) |
30+------+-----------------------------+---------------+
31| 1 | {"a":100, "b":200} | 2 |
32| 2 | {"b":100, "c":200, "d":300} | 3 |
33| 2 | {"a":10, "d":200} | 2 |
34+------+-----------------------------+---------------+
353 rows in set (0.04 sec)
36
37# map_contains_key
38
39mysql> SELECT map_contains_key(map('k11', 1000, 'k22', 2000), 'k11');
40+--------------------------------------------------------+
41| map_contains_key(map('k11', 1000, 'k22', 2000), 'k11') |
42+--------------------------------------------------------+
43| 1 |
44+--------------------------------------------------------+
451 row in set (0.08 sec)
46
47mysql> SELECT id, m, map_contains_key(m, 'k1') FROM simple_map ORDER BY id;
48+------+-----------------------------+-----------------------------+
49| id | m | map_contains_key(`m`, 'k1') |
50+------+-----------------------------+-----------------------------+
51| 1 | {"a":100, "b":200} | 0 |
52| 2 | {"b":100, "c":200, "d":300} | 0 |
53| 2 | {"a":10, "d":200} | 0 |
54+------+-----------------------------+-----------------------------+
553 rows in set (0.10 sec)
56
57mysql> SELECT id, m, map_contains_key(m, 'a') FROM simple_map ORDER BY id;
58+------+-----------------------------+----------------------------+
59| id | m | map_contains_key(`m`, 'a') |
60+------+-----------------------------+----------------------------+
61| 1 | {"a":100, "b":200} | 1 |
62| 2 | {"b":100, "c":200, "d":300} | 0 |
63| 2 | {"a":10, "d":200} | 1 |
64+------+-----------------------------+----------------------------+
653 rows in set (0.17 sec)
66
67# map_contains_value
68
69mysql> SELECT map_contains_value(map('k11', 1000, 'k22', 2000), NULL);
70+---------------------------------------------------------+
71| map_contains_value(map('k11', 1000, 'k22', 2000), NULL) |
72+---------------------------------------------------------+
73| 0 |
74+---------------------------------------------------------+
751 row in set (0.04 sec)
76
77mysql> SELECT id, m, map_contains_value(m, '100') FROM simple_map ORDER BY id;
78+------+-----------------------------+------------------------------+
79| id | m | map_contains_value(`m`, 100) |
80+------+-----------------------------+------------------------------+
81| 1 | {"a":100, "b":200} | 1 |
82| 2 | {"b":100, "c":200, "d":300} | 1 |
83| 2 | {"a":10, "d":200} | 0 |
84+------+-----------------------------+------------------------------+
853 rows in set (0.11 sec)
86
87# map_keys
88
89mysql> SELECT map_keys(map('k11', 1000, 'k22', 2000));
90+-----------------------------------------+
91| map_keys(map('k11', 1000, 'k22', 2000)) |
92+-----------------------------------------+
93| ["k11", "k22"] |
94+-----------------------------------------+
951 row in set (0.04 sec)
96
97mysql> SELECT id, map_keys(m) FROM simple_map ORDER BY id;
98+------+-----------------+
99| id | map_keys(`m`) |
100+------+-----------------+
101| 1 | ["a", "b"] |
102| 2 | ["b", "c", "d"] |
103| 2 | ["a", "d"] |
104+------+-----------------+
1053 rows in set (0.19 sec)
106
107# map_values
108
109mysql> SELECT map_values(map('k11', 1000, 'k22', 2000));
110+-------------------------------------------+
111| map_values(map('k11', 1000, 'k22', 2000)) |
112+-------------------------------------------+
113| [1000, 2000] |
114+-------------------------------------------+
1151 row in set (0.03 sec)
116
117mysql> SELECT id, map_values(m) FROM simple_map ORDER BY id;
118+------+-----------------+
119| id | map_values(`m`) |
120+------+-----------------+
121| 1 | [100, 200] |
122| 2 | [100, 200, 300] |
123| 2 | [10, 200] |
124+------+-----------------+
1253 rows in set (0.18 sec)keywords
Plain Text
1MAP