CREATE TABLE

更新时间：2026-06-10 18:08:46

描述

该语句用来在数据库中创建一张新表。

语法

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_definition_list) [USING INDEX index_option_list]
    | [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constraint_state
    | [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state
    

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [VISIBLE|INVISIBLE] [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    {
    [DEFAULT expression]
    [NULL | NOT NULL] 
    [CONSTRAINT [constraint_name] [PRIMARY] KEY] [UNIQUE [KEY]]
    [CONSTRAINT [constraint_name] CHECK(expression) constraint_state]
    [CONSTRAINT [constraint_name] references_clause]
    |
    [GENERATED ALWAYS] AS (expression) [VIRTUAL]
    [NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY]
    }
     
references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]

constraint_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)

table_option_list:
    table_option [ table_option ...]

table_option:
    | table_group
    | block_size
    | compression
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute
    | parallel_clause

physical_attribute_list:
    physical_attribute [physical_attribute]

physical_attribute:
    PCTFREE [=] num
    | PCTUSED num
    | INITRANS num
    | MAXTRANS num
    | STORAGE(storage_option [storage_option] ...)
    | TABLESPACE tablespace

parallel_clause:
    {NOPARALLEL | PARALLEL integer}
    
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

参数说明

参数	描述
GLOBAL TEMPORARY	将该表创建为临时表。
DEFAULT expression	指定列的默认值。`expression` 支持包含 Sequence 的函数表达式。注意自增列不能设置默认值。
DUPLICATE_SCOPE	用来指定复制表属性，取值如下： `none`：表示该表是一个普通表。 `cluster`：表示该表是一个复制表，Leader 需要将事务复制到 Cluster 的所有 F 副本及 R 副本。不指定 `DUPLICATE_SCOPE` 的情况下，默认值为 `none`。目前，OceanBase 数据库仅支持 `cluster` 级别的复制表。
BLOCK_SIZE	指定表的微块大小。
COMPRESSION	指定存储格式 Flat/Encoding 以及压缩方法，对应如下： `nocompress`：使用 Flat 格式，不进行压缩。 `compress [basic]`：使用 Flat 格式和 lz4_1.0 压缩方法。 `compress for oltp`：使用 Flat 格式和 zstd_1.0 压缩方法。 `query low` 或者 `query high`：使用 Encoding 格式和 lz4_1.0 压缩方法。 `archive low` 或者 `archive high`：使用 Encoding 格式和 zstd_1.0 压缩方法。
table_tablegroup	指定表所属的 TableGroup 属性。
GENERATED BY DEFAULT AS IDENTITY \| GENERATED ALWAYS AS IDENTITY	可选项，指定一个列为自增列。具体如下： `GENERATED BY DEFAULT AS IDENTITY`：表示在插入数据时，如果没有显式指定自增列的值，系统将使用默认的自增值。 `GENERATED ALWAYS AS IDENTITY`：表示在插入数据时，必须使用自动生成的自增值，而不能显式指定自增列的值。注意该列的数据类型必须是数值类型。
physical_attribute	`PCTFREE`: 指定宏块保留空间百分比。其它属性 `STORAGE`、`TABLESPACE` 等仅为了语法兼容方便迁移，不生效。
ENABLE/DISABLE ROW MOVEMENT	是否允许在不同分区间移动的分区键更新。
ON COMMIT DELETE ROWS	事务级临时表，提交时删除数据。
ON COMMIT PRESERVE ROWS	会话级临时表，会话结束时删除数据。
parallel_clause	指定表级别的并行度： `NOPARALLEL`：并行度为 1，为默认配置。 `PARALLEL integer`：指定并行度，`integer` 取值大于等于 `1`。注意当指定并行度时，优先级关系如下：通过 Hint 指定的并行度 > 通过 `ALTER SESSION` 指定的并行度 > 表级别的并行度。

示例

创建表 tbl1，并设置列 col1 为主键。

obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50)) ;
Query OK, 0 rows affected

创建一个复制表。

obclient> CREATE TABLE tbl2 (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' ;
Query OK, 0 rows affected

创建带索引的表。

obclient> CREATE TABLE tbl3 (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX idx1 (col2));
Query OK, 0 rows affected

创建 Hash 分区，分区数为 8 的表。

obclient> CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT) PARTITION BY HASH(col1) PARTITIONS 8;
Query OK, 0 rows affected

创建一级分区为 Range 分区，二级分区为 Hash 分区的表。

obclient> CREATE TABLE tbl5 (col1 INT, col2 INT, col3 INT)
                 PARTITION BY RANGE(col1) 
                 SUBPARTITION BY HASH(col2)  SUBPARTITIONS  5
                 (PARTITION p0 VALUES LESS THAN(0), 
                  PARTITION p1 VALUES LESS THAN(100));
Query OK, 0 rows affected

开启 Encoding 并使用 zstd 压缩，宏块保留空间为 5%。

obclient> CREATE TABLE tbl6 (col1 INT, col2 INT, col3 VARCHAR(64))
                 COMPRESS FOR ARCHIVE
                 PCTFREE 5;
Query OK, 0 rows affected

创建一个事务级临时表。

obclient> CREATE GLOBAL TEMPORARY TABLE tbl7(col1 INT) ON COMMIT DELETE ROWS;
Query OK, 0 rows affected

创建一个带约束的表。

obclient> CREATE TABLE tbl8 (col1 INT, col2 INT, col3 INT,CONSTRAINT equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
Query OK, 0 rows affected

创建非模板化的 Range + Range 二级分区表。

obclient> CREATE TABLE tbl9 (col1 INT, col2 INT, col3 INT) 
                 PARTITION BY RANGE(col1)
                 SUBPARTITION BY RANGE(col2)
(
  PARTITION p0 VALUES LESS THAN(100)
  (
    SUBPARTITION p0_r1 VALUES LESS THAN(2019),
    SUBPARTITION p0_r2 VALUES LESS THAN(2020),
    SUBPARTITION p0_r3 VALUES LESS THAN(2021)
  ),
  PARTITION p1 VALUES LESS THAN(200)
  (
    SUBPARTITION p1_r1 VALUES LESS THAN(2019),
    SUBPARTITION p1_r2 VALUES LESS THAN(2020),
    SUBPARTITION p1_r3 VALUES LESS THAN(2021)
  ),
  PARTITION p2 VALUES LESS THAN(300)
  (
    SUBPARTITION p2_r1 VALUES LESS THAN(2019),
    SUBPARTITION p2_r2 VALUES LESS THAN(2020),
    SUBPARTITION p2_r3 VALUES LESS THAN(2021)
  )
);
Query OK, 0 rows affected

创建表 tbl10 的并行度为 3。

obclient> CREATE TABLE tbl10(col1 INT PRIMARY KEY, col2 INT) PARALLEL 3;
Query OK, 0 rows affected

使用函数定义列的默认值。

obclient> CREATE SEQUENCE SEQ_PERSONIPTVSEQ START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
Query OK, 0 rows affected 

obclient> SELECT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
+----------------------------------------------------------------------------+
| LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')) |
+----------------------------------------------------------------------------+
| 2022-07-05 11:34:1                                                         |
+----------------------------------------------------------------------------+
1 row in set 

obclient> CREATE TABLE FUNC_DEFAULT_TEST ( OID NUMBER(20,0) DEFAULT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) NOT NULL);
Query OK, 0 rows affected

创建一个名为 test_tbl1 的表，列 col1 设置为自增列，并且指定为主键。

CREATE TABLE test_tbl1 (
  col1 INT GENERATED BY DEFAULT AS IDENTITY,
  col2 VARCHAR2(50),
  PRIMARY KEY (col1)
);

CREATE TABLE

描述

语法

参数说明

注意

注意

示例