树叶云OceanBase教程：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	用来指定复制表属性，取值如下： none：表示该表是一个普通表 zone：表示该表是一个复制表，leader 需要将事务复制到本 zone 的所有 F 副本及 R 副本 region：表示该表是一个复制表，leader 需要将事务复制到本 region 的所有 F 副本及 R 副本 cluster：表示该表是一个复制表，leader 需要将事务复掉到 cluster 的所有 F 副本及 R 副本不指定 DUPLICATE_SCOPE 的情况下，默认值为 none。
BLOCK_SIZE	指定表的微块大小
COMPRESSION	指定存储格式 flat/encoding 以及压缩方法，对应如下： nocompress：flat 格式, none 压缩 compress [basic]：flat 格式, lz4_1.0 压缩 compress for oltp：flat 格式, zstd_1.0 压缩 query [low\|high]：encoding 格式, lz4_1.0 压缩 archive [low\|high]：encoding 格式, zstd_1.0 压缩
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)
  )
);

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/256582.html<