建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
創(chuàng)新互聯(lián)OceanBase教程：OceanBaseCREATETABLE
描述
該語(yǔ)句用來(lái)在數(shù)據(jù)庫(kù)中創(chuàng)建一張新表。
格式
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)
/*模板化二級(jí)分區(qū)*/
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)
/*非模板化二級(jí)分區(qū)*/
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/*僅非模板化二級(jí)分區(qū)可定義*/]
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/*僅非模板化二級(jí)分區(qū)可定義*/]
[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/*僅非模板化二級(jí)分區(qū)可定義*/]
[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參數(shù)說(shuō)明
|
參數(shù) |
描述 |
|---|---|
DUPLICATE_SCOPE |
用來(lái)指定復(fù)制表屬性，取值如下：
不指定 DUPLICATE_SCOPE 的情況下，默認(rèn)值為 none。 |
BLOCK_SIZE |
指定表的微塊大小 |
COMPRESSION |
指定存儲(chǔ)格式 flat/encoding 以及壓縮方法，對(duì)應(yīng)如下：
|
primary_zone |
指定主 Zone（副本 Leader 所在 Zone）。 |
replica_num |
指定副本數(shù)。 |
table_tablegroup |
指定表所屬的 talegroup。 |
comment |
注釋。 |
LOCALITY |
描述副本在 Zone 間的分布情況，如：F@z1,F@z2,F@z3,R@z4 表示 z1, z2, z3 為全功能副本，z4 為只讀副本。 |
physical_attribute |
PCTFREE: 指定宏塊保留空間百分比 其它屬性：STORAGE, TABLESPACE 等僅為了語(yǔ)法兼容方便遷移，不生效 |
ENABLE/DISABLE ROW MOVEMENT |
是否允許更會(huì)致行在不同分區(qū)間移動(dòng)的分區(qū)鍵更新 |
ON COMMIT DELETE ROWS |
事務(wù)級(jí)臨時(shí)表：提交時(shí)刪除數(shù)據(jù) |
ON COMMIT PRESERVE ROWS |
會(huì)話級(jí)臨時(shí)表：會(huì)話結(jié)束時(shí)刪除數(shù)據(jù) |
示例
-
創(chuàng)建數(shù)據(jù)庫(kù)表。
CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';-
創(chuàng)建一個(gè)復(fù)制表。
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';-
創(chuàng)建帶索引的表。
create table t1 (c1 int primary key, c2 int, c3 int, index i1 (c2));-
創(chuàng)建 hash 分區(qū)，分區(qū)數(shù)為 8 的表。
create table t1 (c1 int primary key, c2 int) partition by hash(c1) partitions 8;-
創(chuàng)建一級(jí)分區(qū)為 range 分區(qū)，二級(jí)分區(qū)為 hash 分區(qū)的表。
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;-
創(chuàng)建一個(gè)事務(wù)級(jí)臨時(shí)表。
create global temporary table t1 (c1 int) on commit delete rows ;-
創(chuàng)建一個(gè)帶約束的表。
create table t1 (c1 int, c2 int, c3 int, CONSTRAINT equal_check CHECK(c2 = c3 * 2) ENABLE VALIDATE);-
創(chuàng)建非模板化的二級(jí)分區(qū)表。
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)
)
);名稱欄目：創(chuàng)新互聯(lián)OceanBase教程：OceanBaseCREATETABLE
網(wǎng)站網(wǎng)址：http://www.5511xx.com/article/dhgeoid.html
