OceanBase Database supports the CREATE TABLE statement for creating a mapped column.
Considerations
- Default values are not supported.
- For information about supported mapped types, their descriptions, and limitations, see Overview of mapped types.
Examples
You can use the CREATE TABLE statement to create a table with a mapped column. Syntax example:
CREATE TABLE t1 (
id INT,
c1 MAP(INT, FLOAT),
c2 MAP(VARCHAR(10), VARCHAR(2)),
c3 MAP(FLOAT UNSIGNED, ARRAY(INT)),
c4 MAP(VARCHAR(3), ARRAY(ARRAY(VARCHAR(20))))
);
The following example defines a mapped type with the value type as a nested array:
CREATE TABLE t2(c1 MAP(INT, ARRAY(INT)));
CREATE TABLE t3(c1 MAP(INT, INT[][]));
The value can be nested up to six levels (including the MAP itself). Example:
CREATE TABLE t4 (c1 MAP(INT, INT[][][][][]));
DESC t4;
+-------+-------------------------------------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------------------------+------+------+---------+-------+
| c1 | MAP(INT,ARRAY(ARRAY(ARRAY(ARRAY(ARRAY(INT)))))) | YES | | NULL | |
+-------+-------------------------------------------------+------+------+---------+-------+
1 row in set
The following examples show how to write and query mapped data.
Create a test table named t5:
obclient> CREATE TABLE t5(
c1 MAP(INT, INT),
c2 MAP(VARCHAR(256), VARCHAR(256)),
c3 MAP(BIGINT, BIGINT)
);
The return result is as follows:
obclient> DESC t5;
+-------+--------------------------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+------+---------+-------+
| c1 | MAP(INT,INT) | YES | | NULL | |
| c2 | MAP(VARCHAR(256),VARCHAR(256)) | YES | | NULL | |
| c3 | MAP(BIGINT,BIGINT) | YES | | NULL | |
+-------+--------------------------------+------+------+---------+-------+
3 rows in set
Insert data:
obclient> INSERT INTO t5 VALUES (MAP(1,2), MAP(1,2), MAP(1,2));
Query the test table. The result is as follows:
obclient> SELECT * FROM t5;
+-------+-----------+-------+
| c1 | c2 | c3 |
+-------+-----------+-------+
| {1:2} | {"1":"2"} | {1:2} |
+-------+-----------+-------+
1 row in set