创建表

您可以使用 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 AI 数据库支持创建行存表、行存列存转换的功能。

当配置项 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);

说明

通过设置 WITH COLUMN GROUP(all columns) 选项创建行存表，即使用户后来执行了 DROP COLUMN GROUP(all columns) 命令来删除这个列组，表仍然保持为行存储格式。

创建列存表

OceanBase AI 数据库支持创建列存表、行存列存转换以及创建列存索引的功能。OceanBase AI 数据库默认建表时，构建的是行存表，通过设置 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 监控收集进度。

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

创建堆组织表（堆表）

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

使用限制

堆组织表的本地唯一索引键（Unique Key Local）或堆组织表主键表的主键（Primary Key），必须包含所有分区列（Partition Key）。
堆组织表创建索引时，索引名不能和主键第一列列名相同。
OceanBase AI 数据库堆组织表暂不支持以下 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 仅适用于用户租户，sys （系统）租户不支持。

将配置项 default_table_organization 的值设置为 HEAP
```
ALTER SYSTEM SET default_table_organization = 'HEAP';
```

创建表 ora_tbl2。

CREATE TABLE ora_tbl2(col1 INT, col2 INT);

查看表 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

创建表时指定聚集列

创建表时指定聚集列的语法是在 CREATE TABLE 语句后增加表选项 CLUSTER BY，SQL 语句如下：

CREATE TABLE table_name (table_definition_list) CLUSTER BY (column_name_list);

column_name_list:
    column_name [, column_name ...]

参数说明：

column_name：表示聚集列用来排序列名称。

注意

在 OceanBase AI 数据库下，如果用户没有显式指定表模式（ORGNIZATION = HEAP），但定义了 CLUSTER BY 选项，OceanBase AI 数据库将默认创建为堆组织表。

示例如下：

创建一个按 col2 和 col4 排序的表 cb_tbl1。

obclient> CREATE TABLE cb_tbl1 (
    col1 INT PRIMARY KEY,
    col2 INT,
    col3 DECIMAL(10, 2),
    col4 TIMESTAMP
    )
    CLUSTER BY (col2, col4);

创建临时表

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

使用限制

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

具体操作如下：
- 执行如下命令修改 OBProxy 的通信协议配置。
```
ALTER proxyconfig SET server_protocol = 'OceanBase 2.0';
```
- 执行如下命令将生成客户端会话 ID（Client Session ID）的算法设置到版本 2。
```
ALTER proxyconfig SET client_session_id_version = 2;
```
- 执行如下命令将 ODP 的 ID 修改为 1，不同 ODP 需要设置不一样的数字以保障生成的 Client Session ID 不会发生冲突。
```
ALTER proxyconfig SET proxy_id = 1;
```

使用示例

创建临时表 tbl1:

CREATE TEMPORARY TABLE tbl1(col1 INT);

指定表的更新模型

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

CREATE TABLE table_name column_definition
    MERGE_ENGINE = {delete_insert | partial_update | append_only};

说明

参数 MERGE_ENGINE 在创建表时指定之后，其配置值将不可修改。

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

partial_update：表示采用部分更新模式。每次更新仅记录修改的列（delta），存储空间更节省，但查询时需合并多份数据以获取最新值，适用于高频更新、查询需求较低的 OLTP 场景。
delete_insert：表示采用全列更新模式。每次更新写入完整行（delete 旧行 + insert 新行），以查询性能优先。该模式支持增量数据（Memtable/Delta SSTable）的 Skip Index，查询时可对增量数据进行过滤下推，若过滤结果不涉及对基线数据的更新，则可分别批处理基线与增量数据，减少读放大，适用于增量数据比例较高、需要频繁执行复杂查询或批处理分析的 OLAP 场景。
append_only：表示表的更新模式只允许 INSERT 操作，禁止其他所有会产生存储数据修改的 DML 和 DDL 操作。该模式下如果要做过期数据清理可以使用 TTL 功能。使用限制详细介绍可参见更新模型使用限制。

说明

delete_insert 与 partial_update 的核心差异在于写入粒度：前者每次更新写全列，后者仅写变更列。全列写入使得 delete_insert 能够支持 Delta SSTable 的 Skip Index，从而在分析型查询中实现更好的过滤下推效果。

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

适用场景

场景	推荐取值	说明
OLAP、分析型查询、批处理	`delete_insert`	增量数据可过滤时，查询性能更优；需注意增量数据会占用更多存储空间。
OLTP、高频更新、存储敏感	`partial_update`	存储更省，适合更新频繁但查询需求较低的场景。
不可变性、高效追加、简化查询	`append_only`	适合高吞吐场景（如日志、IoT 数据监控）；范围查询和聚合计算效率高。

更多配置场景介绍信息，参见配置最佳实践。

更新模型使用限制

当表更新模型 MERGE_ENGINE 取值为 append_only 时，有以下限制：

禁止以下 DML 语法：
- UPDATE
- DELETE
- MERGE INTO
- REPLACE
INSERT [IGNORE] xxx ON DUPLICATE KEY UPDATE 语法不禁止，但是当产生实际数据修改时会报错。
禁止以下 DDL：
- TRUNCATE 表和分区
- 分区交换
- 删除分区
- 删除列
- 减小列值域
- 动态分区
- 清除废弃列
- 增加列（指定 DEFAULT VALUE）
- 添加自增列

示例

创建表 mer_tbl1，更新模型为全列更新（delete_insert）。

obclient> CREATE TABLE mer_tbl1 (col1 INT, col2 INT)
    MERGE_ENGINE = delete_insert;

创建列存表 mer_tbl2，更新模型为全列更新（delete_insert）。

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

创建一个 append_only 模式的表，并执行 INSERT、SELECT、UPDATE 和 DELETE 操作。

创建表 mer_tbl3，更新模型为 append_only。

obclient> CREATE TABLE mer_tbl3 (col1 INT PRIMARY KEY, col2 VARCHAR(100))
    MERGE_ENGINE = append_only;

向表 mer_tbl3 中插入数据。

obclient> INSERT INTO mer_tbl3 VALUES(1, 'oceanbase');

查询表 mer_tbl3 数据。

obclient> SELECT * FROM mer_tbl3;

返回结果如下：

+------+-----------+
| col1 | col2      |
+------+-----------+
|    1 | oceanbase |
+------+-----------+
1 row in set

更新表 mer_tbl3 数据报错。

obclient> UPDATE mer_tbl3 SET col2 = 'database' WHERE col1 = 1;

返回结果如下：

ERROR 1235 (0A000): update append_only table is not supported

删除表 mer_tbl3 数据报错。

obclient> DELETE FROM mer_tbl3 WHERE col1 = 1;

返回结果如下：

ERROR 1235 (0A000): delete from append_only table is not supported

创建非分区表

说明

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

复制表数据

复制表结构

创建行存表

说明

创建列存表

创建堆组织表（堆表）

使用限制

指定表组织形式的语法

说明

创建表时指定聚集列

注意

创建临时表

使用限制

使用示例

指定表的更新模型

说明

说明

适用场景

更新模型使用限制

示例

相关文档