创建表

您可以使用 CREATE TABLE 语句来创建表。

本节主要介绍非分区表的创建，分区表的创建及使用请参见 创建分区表。

创建非分区表

创建非分区表是指创建只有一个分区的表。

创建非分区表的示例语句如下：

obclient>CREATE TABLE table_name1(w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);

Query OK, 0 rows affected (0.09 sec)

obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT null
, c_discount decimal(4, 4)
, c_credit char(2)
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, c_ytd_payment decimal(12, 2)
, c_payment_cnt int
, c_credit_lim decimal(12, 2)
, c_street_1 varchar(20)
, c_street_2 varchar(20)
, c_city varchar(20)
, c_state char(2)
, c_zip char(9)
, c_phone char(16)
, c_since date
, c_delivery_cnt int
, c_data varchar(500)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);

Query OK, 0 rows affected

示例中创建了 2 个表，并同时对表中的列定义了一些约束信息，包括在不同列上创建的主键和外键等。更多主键、外键等的介绍，请参见 定义列的约束类型。

创建表的列时请选择正确的数据类型，有关 SQL 数据类型的详细描述，请参见 数据类型。

复制已有表的数据创建新表

复制表数据

可以使用 CREATE TABLE AS SELECT 语句复制表的数据，但是结构并不完全一致，并且会丢失约束、索引、默认值、分区等信息。

示例语句如下:

obclient>CREATE TABLE t1_copy AS SELECT * FROM t1;
Query OK, 3 rows affected

复制表结构

可以使用 CREATE TABLE LIKE 语句复制表结构，但是不能复制表数据。

示例语句如下：

obclient>CREATE TABLE t1_like like t1;
Query OK, 0 rows affected

创建行存表

OceanBase 数据库支持创建行存表、行存列存转换的功能。

当配置项 default_table_store_format=‘row’（默认值）时，默认创建的表为行存表，当 default_table_store_format 不为 row 时，可通过设置 WITH COLUMN GROUP(all columns) 选项创建行存表。

有关行存列存转换的信息请参见 更改表。有关创建列存索引的信息，请参见 创建索引。

通过指定 WITH COLUMN GROUP(all columns) 创建行存表。

示例如下：

CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns);

创建列存表

OceanBase 数据库支持创建列存表、行存列存转换以及创建列存索引的功能。OceanBase 数据库默认建表时，构建的是行存表，通过设置 WITH COLUMN GROUP 选项可以显式指定为列存或者行存列存冗余状态。

有关行存列存转换的信息请参见 更改表。有关创建列存索引的信息，请参见 创建索引。

通过指定 WITH COLUMN GROUP(all columns, each column) 创建行存列存冗余表。

示例如下：

CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns, each column);

通过指定 WITH COLUMN GROUP(each column) 创建列存表。

示例如下：

CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);

当创建并使用列存储表时，如果你导入了大量数据，那么需要注意执行一次合并操作以改善读取性能，并且进行统计信息收集，调整执行策略。

• 合并操作：在批量导入数据之后，建议进行一次合并操作。这有助于改善读取性能，因为合并操作会整理片段化数据，使其在物理存储上更连续，从而减少读取时的磁盘 I/O。导入数据后，在租户内触发一次合并操作，以确保所有数据都被合并到基线层，操作请参考 MAJOR 和 MINOR

• 统计信息收集：合并操作完成之后，推荐进行统计信息的收集。这对于优化器生成有效的查询计划和执行策略是非常重要的。执行 GATHER_SCHEMA_STATS 查看对所有表收集统计信息，并可以通过视图 GV$OB_OPT_STAT_GATHER_MONITOR 监控收集进度。

需要注意的是，随着列存表数据量的增加，合并操作的速度可能会下降。

指定表的更新模型

创建表时指定表更新模型的语法是在 CREATE TABLE 语句后增加表选项 MERGE_ENGINE，SQL 语句如下：

CREATE TABLE table_name column_definition
    MERGE_ENGINE = {delete_insert | partial_update}
    WITH COLUMN GROUP([all columns,] each column);

其中，MERGE_ENGINE 参数用于指定表的更新模型，取值如下：

• partial_update：默认值，表示采用现有的更新模式不变。
• delete_insert: 表示采用 merge on write 的方式，将 update 转为 delete 和 insert，并且写全列。

如果不指定 MERGE_ENGINE 选项，则该选项取值与配置项 default_table_merge_engine 的值相同。

适用场景

除了 OLAP 场景推荐使用 delete_insert，其他场景都推荐使用 partial_update。更多配置场景介绍信息，参见 配置最佳实践。

示例如下：

• 创建纯列存表 mer_tbl1，更新模型是 merge on write。

  CREATE TABLE mer_tbl1 (col1 INT, col2 INT)
      MERGE_ENGINE = delete_insert
      WITH COLUMN GROUP(each column);
  

• 创建行存列存冗余表 mer_tbl2，更新模型是 merge on write。

  CREATE TABLE mer_tbl2 (col1 INT, col2 INT)
      MERGE_ENGINE = delete_insert
      WITH COLUMN GROUP(all columns, each column);
  

创建堆组织表

OceanBase 数据库支持索引组织表（Index Organized Table）和堆组织表（Heap Organized Table）两种表的组织形式。

使用限制

• 堆组织表的本地唯一索引键（Unique Key Local）或堆组织表主键表的主键（Primary Key），必须包含所有分区列（Partition Key）。

• 堆组织表创建索引时，索引名不能和主键第一列列名相同。

• OceanBase 数据库堆组织表暂不支持以下 DDL 操作：

  • 主键约束操作
  • 修改列为主键
  • 主键列类型长度由大改小
  • 加主键列
  • 修改主键为自增列
  • 删主键列
  • 分区分裂

指定表组织形式的语法

创建表时指定表组织形式的语法是在 CREATE TABLE 语句后增加表选项 ORGANIZATION，SQL 语句如下：

CREATE TABLE table_name column_definition ORGANIZATION [=] {INDEX | HEAP};

其中，ORGANIZATION 参数用来指定表中数据行的存储顺序，取值如下：

• INDEX：表示表模型为聚集索引表模型，即索引组织表。
• HEAP：表示表模型为非聚集索引表模型，即堆组织表。

如果不指定 ORGANIZATION 选项，则该选项取值与配置项 default_table_organization 的值相同。

示例如下：

• 建表时指定 ORGANIZATION 属性的值为 HEAP。

  CREATE TABLE ora_tbl1 (col1 INT, col2 INT) ORGANIZATION = HEAP;
  

• 创建表时，如果不指定 ORGANIZATION。

  更改租户级配置项 default_table_organization 后，再创建表。有关配置项 default_table_organization 的详细介绍，参见 default_table_organization。

  说明

  当前版本配置项 default_table_organization 仅适用于 OceanBase 数据库 MySQL 模式用户租户，sys （系统）租户和 Oracle 模式用户租户不支持。

  1. 将配置项 default_table_organization 的值设置为 HEAP

     ALTER SYSTEM SET default_table_organization = 'HEAP';
     

  2. 创建表 ora_tbl2。

     CREATE TABLE ora_tbl2(col1 INT, col2 INT);
     

  3. 查看表 ora_tbl2 定义。

     SHOW CREATE TABLE ora_tbl2;
     

     返回结果如下：

     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table    | Create Table                                                                                                                                                                                                                                                                                                                 |
     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | ora_tbl2 | CREATE TABLE `ora_tbl2` (
       `col1` int(11) DEFAULT NULL,
       `col2` int(11) DEFAULT NULL
     ) ORGANIZATION HEAP DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
     +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

创建临时表

临时表是一种会话级别的特殊表，主要用于临时存储中间数据。

使用限制

• 临时表路由：由于临时表需要和普通表一样支持负载均衡，默认情况下不能将临时表绑定在某一台机器上。可以考虑将负载均衡关掉使用。
• OceanBase 数据库在可串行化隔离级别（Serializable）下，不支持对同一事务内创建的临时表执行写入操作。
• 当本 Session 存在与普通表同名的临时表时，不建议在本 Session 使用 Database 级别统计信息收集功能，结果可能会不符合预期。
• 临时表通过后台逻辑进行清理，为了避免其 DDL 对业务造成明显影响，其清理速度受到限制。因此大量使用临时表的场景推荐及时手动删除。
• DBLink 访问不到临时表。

使用示例

创建临时表 tbl1:

CREATE TEMPORARY TABLE tbl1(col1 INT);

相关文档

• CREATE TABLE

• ALTER TABLE