--- title: "CREATE TABLE | OceanBase 文档中心" description: "CREATE TABLE 描述 该语句用来在数据库中创建一张新表。 语法 CREATE [GLOBAL TEMPORARY] TABLE table_name (table_definition_list) [table_option_list] [partition_option] [on_commit_optio…" image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.0.0 企业版 # CREATE TABLE 更新时间：2026-06-10 18:08:46 ## 描述 该语句用来在数据库中创建一张新表。 ## 语法 ```sql 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` 为主键。 ```sql obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50)) ; Query OK, 0 rows affected ``` - 创建一个复制表。 ```sql 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 ``` - 创建带索引的表。 ```sql obclient> CREATE TABLE tbl3 (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX idx1 (col2)); Query OK, 0 rows affected ``` - 创建 Hash 分区，分区数为 `8` 的表。 ```sql obclient> CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT) PARTITION BY HASH(col1) PARTITIONS 8; Query OK, 0 rows affected ``` - 创建一级分区为 Range 分区，二级分区为 Hash 分区的表。 ```sql 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%`。 ```sql obclient> CREATE TABLE tbl6 (col1 INT, col2 INT, col3 VARCHAR(64)) COMPRESS FOR ARCHIVE PCTFREE 5; Query OK, 0 rows affected ``` - 创建一个事务级临时表。 ```sql obclient> CREATE GLOBAL TEMPORARY TABLE tbl7(col1 INT) ON COMMIT DELETE ROWS; Query OK, 0 rows affected ``` - 创建一个带约束的表。 ```sql 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 二级分区表。 ```sql 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`。 ```sql obclient> CREATE TABLE tbl10(col1 INT PRIMARY KEY, col2 INT) PARALLEL 3; Query OK, 0 rows affected ``` - 使用函数定义列的默认值。 ```sql 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` 设置为自增列，并且指定为主键。 ```sql CREATE TABLE test_tbl1 ( col1 INT GENERATED BY DEFAULT AS IDENTITY, col2 VARCHAR2(50), PRIMARY KEY (col1) ); ``` 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线