Mapping constructors and operators are used to construct mapping data types. OceanBase Database currently supports the map() constructor and the {} operator.
map
The map() function is used to construct mapping data. The syntax is as follows:
map(key1, value1, key2, value2, ..., keyN, valueN)
The input parameters are described as follows:
- The number of input parameters must be even. Parameters with odd indices form the key array, and parameters with even indices form the value array.
- The data element types of key1~keyN and value1~valueN are described in Overview of mapping data types.
- Note that the keys in the input key-value pairs must be unique. If there are duplicate keys, the later input value will overwrite the earlier one.
The return value is of the mapping data type (MAP).
Here are some examples:
-- The constructed mapping data contains only one key-value pair: key is 1, value is 2.
SELECT map(1,2);
+----------+
| map(1,2) |
+----------+
| {1:2} |
+----------+
1 row in set
-- The constructed mapping data contains three key-value pairs.
SELECT map(1,"a",2,"b",3,"c");
+------------------------+
| map(1,"a",2,"b",3,"c") |
+------------------------+
| {1:"a",2:"b",3:"c"} |
+------------------------+
1 row in set
-- The data type of the Key array is determined by the odd-indexed parameter, and the data type of the Value array is determined by the even-indexed parameter.
SELECT map(1,"a","b",1.8,"ccc",3);
+-------------------------------+
| map(1,"a","b",1.8,"ccc",3) |
+-------------------------------+
| {"1":"a","b":"1.8","ccc":"3"} |
+-------------------------------+
{}
{} is used to construct mapping data as a string. After writing mapping data as a string in the {key1:value1, key2:value2, ..., keyN:valueN} format, OceanBase Database converts it into the mapping data type. Therefore, you can directly use this string when inserting data into a mapping column.
The input parameters are described as follows:
- Each key-value pair is separated by
,. A colon:separates the key from its value. - Keys do not require quotation marks. For example:
{1:1,2:2,3:3}. - In the input key-value pairs, keys must be unique. If duplicate keys exist, the value from the later input will overwrite the previous values.
Here is an example:
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
m MAP(INT, INT)
);
INSERT INTO t1 (m) VALUES ('{1:1,2:2,3:3}');
SELECT m FROM t1;
+---------------+
| m |
+---------------+
| {1:1,2:2,3:3} |
+---------------+
+----------+
1 row in set