OceanBase CREATE TABLE
描述
该语句用来在数据库中创建一张新表。
格式
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [table_option_list] [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [table_option_list] [partition_option] [AS] select;
table_definition_list:
table_definition [, table_definition ...]
table_definition:
column_definition
| INDEX [index_name] index_desc
| [CONSTRAINT [constraint_name]] [PRIMARY KEY|UNIQUE] (column_desc_list) [USING INDEX index_option_list]
| [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constranit_state
| [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[VISIBLE|INVISIBLE]
{
[DEFAULT expression]
[NULL | NOT NULL]
[CONSTRAINT [constraint_name] [PRIMARY] KEY] [UNIQUE [KEY]]
[CONSTRAINT [constraint_name] CHECK(expression) constranit_state]
[CONSTRAINT [constraint_name] references_clause
|
[GENERATED ALWAYS] AS (expression) [VIRTUAL]
[NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [COMMENT string]
}
references_clause:
REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]]
constranit_state:
[RELY|NORELY] [USING INDEX index_option_list] [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
index_desc:
(column_desc_list) [index_option_list]
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [ASC | DESC][NULL LAST|NULL FIRST]
index_option_list:
index_option [ index_option ...]
index_option:
[GLOBAL | LOCAL]
| block_size
| compression
| STORING(column_name_list)
| comment
table_option_list:
table_option [ table_option ...]
table_option:
primary_zone
| replica_num
| table_tablegroup
| block_size
| compression
| comment
| DUPLICATE_SCOPE [=] "none|zone|region|cluster"
| LOCALITY [=] "locality description"
| ENABLE ROW MOVEMENT
| DISABLE ROW MOVEMENT
| physical_attribute
physical_attribute_list:
physical_attribute [physical_attribute]
physical_attribute:
PCTFREE [=] num
| PCTUSED num
| INITRANS num
| MAXTRANS num
| STORAGE(storage_option [storage_option] ...)
| TABLESPACE tablespace
compression:
NOCOMPRESS
| COMPRESS { BASIC | FOR OLTP | FOR QUERY [LOW|HIGH] | FOR ARCHIVE [LOW|HIGH]}
storage_option:
INITIAL_ num [K|M|G|T|P|E]
| NEXT num [K|M|G|T|P|E]
| MINEXTENTS num [K|M|G|T|P|E]
| MAXEXTENTS num [K|M|G|T|P|E]
partition_option:
PARTITION BY HASH(column_name_list)
[subpartition_option] hash_partition_define
| PARTITION BY RANGE (column_name_list)
[subpartition_option] (range_partition_list)
| PARTITION BY LIST (column_name_list)
[subpartition_option] (list_partition_list)
/*模板化二级分区*/
subpartition_option:
SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
| SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
(range_subpartition_list)
| SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
(list_subpartition_list)
/*非模板化二级分区*/
subpartition_option:
SUBPARTITION BY HASH (column_name_list)
| SUBPARTITION BY RANGE (column_name_list)
| SUBPARTITION BY LIST (column_name_list)
subpartition_list:
(hash_subpartition_list)
| (range_subpartition_list)
| (list_subpartition_list)
hash_partition_define:
PARTITIONS partition_count [TABLESPACE tablespace] [compression]
| (hash_partition_list)
hash_partition_list:
hash_partition [, hash_partition, ...]
hash_partition:
partition [partition_name] [subpartition_list/*仅非模板化二级分区可定义*/]
hash_subpartition_define:
SUBPARTITIONS subpartition_count
| SUBPARTITION TEMPLATE (hash_subpartition_list)
hash_subpartition_list:
hash_subpartition [, hash_subpartition, ...]
hash_subpartition:
subpartition [subpartition_name]
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION [partition_name]
VALUES LESS THAN {(expression_list) | (MAXVALUE)}
[subpartition_list/*仅非模板化二级分区可定义*/]
[ID = num] [physical_attribute_list] [compression]
range_subpartition_list:
range_subpartition [, range_subpartition ...]
range_subpartition:
SUBPARTITION subpartition_name
VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]
list_partition_list:
list_partition [, list_partition] ...
list_partition:
PARTITION [partition_name]
VALUES (DEFAULT|expression_list)
[subpartition_list/*仅非模板化二级分区可定义*/]
[ID num] [physical_attribute_list] [compression]
list_subpartition_list:
list_subpartition [, list_subpartition] ...
list_subpartition:
SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]
expression_list:
expression [, expression ...]
column_name_list:
column_name [, column_name ...]
partition_name_list:
partition_name [, partition_name ...]
partition_count | subpartition_count:
INT_VALUE
on_commit_option:
ON COMMIT DELETE ROWS
| ON COMMIT PRESERVE ROWS参数说明
|
参数 |
描述 |
|---|---|
|
DUPLICATE_SCOPE |
用来指定复制表属性，取值如下：
不指定 DUPLICATE_SCOPE 的情况下，默认值为 none。 |
|
BLOCK_SIZE |
指定表的微块大小 |
|
COMPRESSION |
指定存储格式 flat/encoding 以及压缩方法，对应如下：
|
|
primary_zone |
指定主 Zone（副本 Leader 所在 Zone）。 |
|
replica_num |
指定副本数。 |
|
table_tablegroup |
指定表所属的 talegroup。 |
|
comment |
注释。 |
|
LOCALITY |
描述副本在 Zone 间的分布情况，如：F@z1,F@z2,F@z3,R@z4 表示 z1, z2, z3 为全功能副本，z4 为只读副本。 |
|
physical_attribute |
PCTFREE: 指定宏块保留空间百分比 其它属性：STORAGE, TABLESPACE 等仅为了语法兼容方便迁移，不生效 |
|
ENABLE/DISABLE ROW MOVEMENT |
是否允许更会致行在不同分区间移动的分区键更新 |
|
ON COMMIT DELETE ROWS |
事务级临时表：提交时删除数据 |
|
ON COMMIT PRESERVE ROWS |
会话级临时表：会话结束时删除数据 |
示例
-
创建数据库表。
CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';-
创建一个复制表。
CREATE TABLE item (i_id INT
, i_name VARCHAR(24)
, i_price DECIMAL(5,2)
, i_data VARCHAR(50)
, i_im_id INT
, PRIMARY KEY(i_id)) COMPRESS FOR QUERY pctfree=0 BLOCK_SIZE=16384
DUPLICATE_SCOPE='cluster' LOCALITY='F@zone1, F@zone2,R{all_server}@zone3' primary_zone='zone1';-
创建带索引的表。
create table t1 (c1 int primary key, c2 int, c3 int, index i1 (c2));-
创建 hash 分区，分区数为 8 的表。
create table t1 (c1 int primary key, c2 int) partition by hash(c1) partitions 8;-
创建一级分区为 range 分区，二级分区为 hash 分区的表。
create table t1 (c1 int, c2 int, c3 int)
partition by range(c1) subpartition by hash(c2) subpartitions 5
(partition p0 values less than(0), partition p1 values less than(100));-
开启 encoding 并使用 zstd 压缩，宏块保留空间为 5%。
create table t1 (c1 int, c2 int, c3 varchar(64))
COMPRESS FOR ARCHIVE
PCTFREE 5;-
创建一个事务级临时表。
create global temporary table t1 (c1 int) on commit delete rows ;-
创建一个带约束的表。
create table t1 (c1 int, c2 int, c3 int, CONSTRAINT equal_check CHECK(c2 = c3 * 2) ENABLE VALIDATE);-
创建非模板化的二级分区表。
create table t_range_range1 (c1 int, c2 int, c3 int) partition by range(c1)
subpartition by range (c2)
(
partition p0 values less than (100)
(
subpartition p0_r1 values less than (100),
subpartition p0_r2 values less than (200),
subpartition p0_r3 values less than (300)
),
partition p1 values less than (200)
(
subpartition p1_r1 values less than (100),
subpartition p1_r2 values less than (200),
subpartition p1_r3 values less than (300)
),
partition p2 values less than (300)
(
subpartition p2_r1 values less than (100),
subpartition p2_r2 values less than (200),
subpartition p2_r3 values less than (300)
)
);