CREATE TABLE

描述

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

语法

CREATE TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [table_column_group_option] [AS] select;

CREATE TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition_list
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY}
            [index_name] index_desc
    | [CONSTRAINT [constraint_name]] FOREIGN KEY
            [index_name] index_desc
            REFERENCES reference_definition
            [match_action][opt_reference_option_list]
    | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option_list] [index_column_group_option]
    | [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
     column_name data_type
         [DEFAULT const_value] [AUTO_INCREMENT]
         [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [COMMENT string_value] [SKIP_INDEX(skip_index_option_list)]
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute]

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}


key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | COMMENT string_value

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | table_tablegroup
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | COMMENT string_value
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause
    | READ {ONLY | WRITE}
    | DUPLICATE_SCOPE [=] 'none|cluster'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

table_column_group_option/index_column_group_option:
      WITH COLUMN GROUP(all columns)
    | WITH COLUMN GROUP(each column)
    | WITH COLUMN GROUP(all columns, each column)

参数说明

参数	描述
IF NOT EXISTS	如果指定 `IF NOT EXISTS`，即使待创建的表已存在，也不会报错；如果不指定且待创建的表已存在，则系统会报错。
PRIMARY KEY	为创建的表指定主键。如果不指定，则使用隐藏主键。OceanBase 数据库支持修改表的主键或通过 `ALTER TABLE` 语句为表添加主键，参见ALTER TABLE。
FOREIGN KEY	为创建的表指定外键。如果不指定外键名，则会使用表名 + `OBFK` + 创建时间命名。（例如，在 2021 年 8 月 1 日 00:00:00 为 `t1` 表创建的外键名称为 `t1_OBFK_1627747200000000`）。外键允许跨表交叉引用相关数据，当 `UPDATE` 或 `DELETE` 操作影响与子表相匹配行的父表中键值时，其结果取决于 `ON UPDATE` 和 `ON DELETE` 子句的引用操作： `CASCADE`：表示从父表中删除或更新行，并自动删除或更新子表中匹配的行。 `SET NULL`：表示从父表中删除或更新行，并将子表中的外键列设置为 `NULL`。 `RESTRICT`：表示拒绝对父表的删除或更新操作。 `NO ACTION`：指定延迟检查。此外还支持 `SET DEFAULT` 操作。
KEY \| INDEX	为创建的表指定键或索引。如果不指定索引名，则会使用索引引用的第一列作为索引名，如果命名存在重复，则会使用下划线（_）+ 序号的方式命名。（例如，使用 `c1` 列创建的索引如果命名重复，则会将索引命名为 `c1_2`。）您可以通过 `SHOW INDEX` 语句查看表上的索引。
key_part	创建（函数）索引。
index_col_name	指定索引的列名，每个列名后都支持 ASC（升序），不支持 DESC（降序）。默认为升序。建立索引的排序方式为：首先以 `index_col_name` 中第一个列的值排序；该列值相同的记录，按下一列名的值排序；以此类推。
expr	表示合法的函数索引表达式，且允许是布尔表达式，例如 `c1=c1`。注意 OceanBase 数据库当前版本禁止创建生成列上的函数索引。
ROW_FORMAT	指定表是否开启 Encoding 存储格式。 `redundant`：不开启 Encoding 存储格式。 `compact`：不开启 Encoding 存储格式。 `dynamic`：Encoding 存储格式。 `compressed`：Encoding 存储格式。 `default`：等价 `dynamic` 模式。
[GENERATED ALWAYS] AS (expr) [VIRTUAL \| STORED]	创建生成列，`expr` 为用于计算列值的表达式。 `VIRTUAL`：列值不会被存储，而是在读取行时，在任何 `BEFORE` 触发器之后立即计算。虚拟列不占用存储空间。 `STORED`：在插入或更新行时评估和存储列值。存储列确实需要存储空间并且可以被索引。
BLOCK_SIZE	指定表的微块大小。
lob_inrow_threshold	用于配置 `LOB` 字段 `INROW` 阈值，当某个 `LOB` 字段数据大小超过该阈值时，超过部分会转为`OUTROW` 存储在 LOB Meta 表中，默认值由变量 `ob_default_lob_inrow_threshold` 控制。
COMPRESSION	指定表的压缩算法，取值如下： `none`：不使用压缩算法。 `lz4_1.0`: 使用 `lz4` 压缩算法。 `zstd_1.0`: 使用 `zstd` 压缩算法。 `snappy_1.0`: 使用 `snappy` 压缩算法。
CHARSET \| CHARACTER SET	指定表中列的默认字符集，可使用的字符集请参见字符集。
COLLATE	指定表中列的默认字符序，可使用的字符序请参见字符序。
table_tablegroup	指定表所属的 `tablegroup`。
AUTO_INCREMENT	指定表中自增列的初始值。OceanBase 数据库支持使用自增列作为分区键。
COMMENT	注释。不区分大小写。
PCTFREE	指定宏块保留空间百分比。
parallel_clause	指定表级别的并行度： `NOPARALLEL`：并行度为 `1`，默认配置 `PARALLEL integer`：指定并行度，`integer` 取值大于等于 `1`。
READ {ONLY \| WRITE}	指定表的读写权限。具体如下： `READ ONLY`：表示表只能被读取，不能被修改或删除。注意表级别的 `READ ONLY` 参数对具有 `SUPER` 权限的用户不生效，只对普通用户生效。 `READ WRITE`：默认值，表示表可以被读取、修改和删除。
DUPLICATE_SCOPE	指定复制表的属性，取值如下： `none`：表示该表是一个普通表，为默认值。 `cluster`：表示该表是一个复制表，Leader 需要将事务复制到当前租户的所有 F（全能）副本及 R（只读）副本。 OceanBase 数据库目前仅支持 `cluster` 级别的复制表。
CHECK	限制列中的值的范围。如果对单个列定义 `CHECK` 约束，那么该列级约束可以写到列定义中，并且可以指定名称。如果对一个表定义 `CHECK` 约束，那么此约束会应用于表中多个列，且允许出现在列的定义前。在删除表时，表中创建的 `CHECK` 约束也会一起被删除。可以通过如下方式查看约束信息：使用 `SHOW CREATE TABLE` 命令查看 `information_schema.TABLE_CONSTRAINTS` 视图查看 `information_schema.CHECK_CONSTRAINTS` 视图
constraint_name	约束名称，最多包含 64 个字符。约束名称的开头结尾中间都允许有空格，但需要用"`"标识名称的开头和结尾。约束名称可以包含特殊字符"$"。如果约束名称为保留字，需要用"`"标识，否则会报错。在同一 Database 下不允许 `CHECK` 约束的名称重复。
expression	约束表达式。 `expression` 不允许为空。 `expression` 结果不能为非布尔类型。 `expression` 不能包含不存在的列。
table_column_group_option/index_column_group_option	指定表或索引的列存选项。具体解释如下： `WITH COLUMN GROUP(all columns, each column)`：指定创建行存列存冗余的表或索引。 `WITH COLUMN GROUP(all columns)`：指定添加行存表或索引。 `WITH COLUMN GROUP(each column)`：指定创建列存表或索引。
SKIP_INDEX	标识列的 Skip Index 属性。取值如下： `MIN_MAX`：是 Skip Index 中最通用的聚合数据类型，它会在索引节点粒度下存储被索引列的最大值、最小值和 Null Count。这种类型的数据可以加速下推的 Filter 和 `MIN/MAX` 聚合的下推。 `SUM`：用于加速数值类型的 `SUM` 聚合的下推。注意 JSON 和空间数据类型的列禁止创建 Skip Index。生成列禁止创建 Skip Index。

示例

创建数据库表。

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

创建带索引的表。

obclient> CREATE TABLE tbl2 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2));
Query OK, 0 rows affected

创建带有函数索引的表 tbl3。

obclient> CREATE TABLE tbl3 (c1 INT, c2 INT, INDEX i1 ((c1+1)), UNIQUE KEY ((c1+c2)));
Query OK, 0 rows affected

obclient> SHOW CREATE TABLE tbl3;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl3    | CREATE TABLE `tbl3` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,
KEY `i1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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

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

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

obclient> CREATE TABLE tbl5 (c1 INT, c2 INT, c3 INT) PARTITION BY RANGE(c1)
     SUBPARTITION BY KEY(c2, c3) SUBPARTITIONS 5
     (PARTITION p0 VALUES LESS THAN(0), PARTITION p1 VALUES LESS THAN(100));
Query OK, 0 rows affected

创建一列为 gbk, 一列为 utf8 的表。

obclient> CREATE TABLE tbl6 (c1 VARCHAR(10),
                 c2 VARCHAR(10) CHARSET GBK COLLATE gbk_bin)
             DEFAULT CHARSET utf8 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected

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

obclient> CREATE TABLE tbl7 (c1 INT, c2 INT, c3 VARCHAR(64))
      COMPRESSION 'zstd_1.0'
      ROW_FORMAT DYNAMIC
      PCTFREE 5;
Query OK, 0 rows affected

创建表 tbl8，并设置并行度为 3。

obclient> CREATE TABLE tbl8(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3;
Query OK, 0 rows affected

使用自增列作为分区键。

obclient> CREATE TABLE tbl9(inv_id BIGINT NOT NULL AUTO_INCREMENT,c1 BIGINT,
PRIMARY KEY (inv_id) ) PARTITION BY HASH(inv_id) PARTITIONS 8;
Query OK, 0 rows affected

为表 ref_t2 指定外键，当 UPDATE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY, C2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY, C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON UPDATE SET NULL);
Query OK, 0 rows affected

创建带 CHECK 约束的表 tbl10，并查看约束信息。

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

obclient> SHOW CREATE TABLE tbl10;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl10  | CREATE TABLE `tbl10` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  CONSTRAINT `equal_check1` CHECK ((`col1` = (`col3` * 2)))
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

依照表 tbl10 创建表 tbl11，并查看表信息。

obclient> CREATE TABLE tbl11 LIKE tbl10;
Query OK, 0 rows affected

obclient> SHOW CREATE TABLE tbl11;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl11  | CREATE TABLE `tbl11` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBCHECK_1650793233327894` CHECK ((`col1` = (`col3` * 2)))
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

创建 cluster 级别的复制表 dup_t1。

登录到 sys 租户，创建一个 Unit。

obclient> CREATE RESOURCE UNIT IF NOT EXISTS 2c5g MAX_CPU 2, MEMORY_SIZE '5G';
Query OK, 0 rows affected

创建一个 Unit Num 为 2 的资源池。

obclient> CREATE RESOURCE POOL tenant_pool UNIT = '2c5g', UNIT_NUM = 2, ZONE_LIST = ('z1', 'z2', 'z3');
Query OK, 0 rows affected

创建用户租户 obmysql 并指定 Locality 分布。

obclient> CREATE TENANT obmysql resource_pool_list=('tenant_pool'), LOCALITY = "F@z1, F@z2, R@z3", PRIMARY_ZONE = "z1" SET ob_tcp_invited_nodes='%';
Query OK, 0 rows affected

登录到步骤 3 所创建的 obmysql 用户租户，创建一个复制表。

obclient> USE test;
Database changed
obclient> CREATE TABLE dup_t1(c1 INT) DUPLICATE_SCOPE = 'cluster';
Query OK, 0 rows affected

（可选）查看广播日志流信息，复制表会创建在该日志流上。

obclient> SELECT * FROM oceanbase.DBA_OB_LS WHERE FLAG LIKE "%DUPLICATE%";
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
| LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG      |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
|  1003 | NORMAL | z1;z2        |             0 |           0 | 1683267390195713284 |     NULL | 1683337744205408139 | 1683337744205408139 | DUPLICATE |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
1 row in set

（可选）在 sys 租户下查看复制表的副本分布，REPLICA_TYPE 字段为副本类型。

obclient> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TABLE_NAME = "dup_t1";
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z1   | 11.xxx.xxx.xxx |    36125 | LEADER   | FULL         | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z1   | 11.xxx.xxx.xxx  |    36124 | FOLLOWER | READONLY     | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z2   | 11.xxx.xxx.xxx |    36127 | FOLLOWER | FULL         | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z2   | 11.xxx.xxx.xxx |    36126 | FOLLOWER | READONLY     | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z3   | 11.xxx.xxx.xxx |    36128 | FOLLOWER | READONLY     | CLUSTER         |
|      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z3   | 11.xxx.xxx.xxx  |    36129 | FOLLOWER | READONLY     | CLUSTER         |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
6 rows in set

像普通表一样插入和读写复制表的数据。对于一个读请求，如果使用了 Proxy，读请求可能会路由到任意一个 OBServer 节点；如果直连 OBServer 节点，只要本地副本可读，就会在直连的 OBServer 节点上执行读请求。
```
obclient> INSERT INTO dup_t1 VALUES(1);
Query OK, 1 row affected

obclient> SELECT * FROM dup_t1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set
```

创建列存表 tbl1_cg。

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

创建带列存索引的表。

CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX i1 (col2) WITH COLUMN GROUP(each column));

创建带列存索引的列存表。

CREATE TABLE tbl3_cg (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX i1 (col2) WITH COLUMN GROUP(each column)) WITH COLUMN GROUP(each column);

创建表时标识列 Skip Index 属性。

CREATE TABLE test_skidx(
  col1 INT SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX),
  col4 CHAR(10)
);

描述

语法

参数说明

注意

注意

注意

示例

相关文档