OceanBase Cloud快速入门第30期：如何创建表对象？

分布式数据库爱好者 2023-04-10

948

欢迎访问OceanBase官网获取更多信息：https://www.oceanbase.com/

表简介

数据库表是一系列二维数组的集合，用来代表和储存数据对象之间的关系。

从数据的分布上，OceanBase 数据库中的表可以分为非分区表和分区表：

分区表有多个分区。分区表在创建时，至少要指定一级分区列和相关分区信息。
非分区表只有一个分区。

表创建准备

在创建表前，您需要确认以下事项：

请确认您已连接到数据库的 MySQL 租户，连接数据库的操作请参见连接数据库。
说明
当前登录租户所属的租户模式可以由 sys 租户通过查询 oceanbase.DBA_OB_TENANTS 视图进行确认。
请确认您已拥有 CREATE 权限。
请确认已创建数据库，创建数据库的操作请参见创建数据库。

创建表语句

请使用CREATE TABLE 语句创建表，并参考以下内容完成表的创建。

定义表名

创建表时，需要先为表命名，以下是定义表名时需要遵循的要求：

在 OceanBase 数据库的 MySQL 模式中，每个表的名称必须保证在数据库内唯一。
为表命名时，应使用具有实际意义的表名，不要使用 table1 这样的表名。

示例 1：创建一个有关订单信息的表。

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

定义列

在数据库中，列（Column）用于记录一张表上某个属性的字段的值，用户给每个属性起的名称即为列名。除了列名以外，列上还有数据类型以及数据类型的最大长度（精度）等信息。

以下是定义表的列时需要遵循的要求：

根据数据类型的特点，为待存储在列中的数据选择合适的数据类型。
对于字符串数据，建议使用变长字符串数据类型，并指定最大长度。请确保指定的最大长度大于需要存储的最大字符数，避免超出最大长度时出现字符截断现象。
根据主键列的要求，确认是否需要为表定义主键列。
根据其他约束的要求，确认是否需要向列中添加其他约束。
当列上有 NOT NULL 约束时，通常建议为该列设置默认值。当列类型为日期或时间类型时，可以设置默认值为数据库的当前时间。

定义主键列

主键值规则（Primary Key Value Rule）是定义在某一键 Key（键指一列或一个列集）上的规则，其作用是确保表内的每一数据行都可以由某一个键值唯一地确定。每个数据库表上最多只能定义一个 PRIMARY KEY 约束。构成此约束的列（一列或多列）的值可以作为一行数据的唯一标识符，即每个数据行可以由此主键值命名。

要指定某一列为主键列，请在该列的定义后添加 PRIMARY KEY 关键字。如果要在多列上定义主键约束，请在 CREATE TABLE 语句中所有列的列表之后再添加主键约束的定义。

以下是定义主键列时需要遵循的要求：

建议为表定义一个主键，每个数据库表最多拥有一个主键列集合。
在 OceanBase 数据库中，不强制用户为表定义主键，但使用主键可以使表内的每行数据被唯一确定，且不存在重复的数据行。如果没有合适的字段作为主键，可以在创建表时不指定主键，待表创建成功后系统会为无主键表指定自增列作为隐藏主键。
此外，如果在创建时未定义主键列， OceanBase 数据库支持为已存在的表添加主键列。
主键列集合的值在全表范围内唯一。
主键列的数量不能超过 64 列，且主键数据总长度不能超过 16 KB。
主键列的值不能为 NULL 或空字符串，您必须为主键列输入值。
建议明确标识主键约束的名称。例如，主键约束命名为 “PK_xxx”。

示例 2：在多列上定义主键约束。

obclient> CREATE TABLE test(c1 INT, c2 INT, CONSTRAINT PK_c1_c2 PRIMARY KEY(c1, c2));
Query OK, 0 rows affected

obclient> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c2    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

示例 2 中，定义了 c1、c2 列为主键列，且约束名称为 PK_c1_c2。 c1、c2 列的值不允许为 NULL 并且必须是不重复的。

定义其他列约束

除了 PRIMARY KEY 约束外，OceanBase 数据库还支持 NOT NULL 约束、唯一性（UNIQUE）约束、外键（FOREIGN KEY）约束和 CHECK 约束。使用约束可以简化表查询、提高查询性能并确保数据在语义上保持有效。

各约束类型及说明如下：

非空约束（NOT NULL）：表示不允许约束包含的列的值为 NULL。
对于有非空约束的列，在 INSERT 语句中必须指定该列的值，除非该列还定义了非空的默认值。
唯一性约束（UNIQUE）：不允许约束包含的列的值有重复值，但是可以有多个 NULL 值。
外键约束（FOREIGN KEY）：要求约束的列的值取自于另外一个表的主键列。
创建外键约束时，如果不指定外键名，系统会自动分配一个约束名，自动分配的约束名为 表名_OBFK_创建时间戳。例如，t1_OBFK_1627747200000000。
OceanBase 数据库默认开启了外键约束检查，外键约束检查开关由租户变量 foreign_key_checks 来控制。
CHECK 约束：要求数据库中某列的值符合指定的条件。
可以对单个列定义一个或多个 CHECK 约束，使该列只允许特定的值，也可以定义表级的 CHECK 约束，将一个 CHECK 约束应用于多个列。修改表名时，CHECK 约束名不会被修改。删除某个表时，同时也会删除应用于该表的 CHECK 约束。
创建 CHECK 约束时，如果未指定约束名称，系统会自动分配一个约束名，自动分配的约束名为 表名_OBCHECK_创建时间戳，例如，t1_OBCHECK_1629350823880271。

要约束单个列，请在该列的定义中添加一个约束关键字，如果要约束多个列，请在 CREATE TABLE 语句中所有列的列表之后再添加整个约束的定义。

以下是定义其他列约束时需要遵循的要求：

建议给明确不存在 NULL 值的字段加上 NOT NULL 约束。
如果要引用另一个表中的值，请使用外键约束。
组合主键不能作为外键使用。
如果需要防止列中出现重复值，请使用唯一约束。
除 NOT NULL 约束外，建议明确标识其他约束的名称。例如，唯一约束命名为 “UNI_xxx”，外键约束命名为 “FK_xxx”。

示例 3：创建表 tbl1，并设置列 col1 为非空约束。

obclient> CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);
Query OK, 0 rows affected
  
obclient> DESC tbl1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   |     | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

示例 3 中，后续插入数据时，列 col1 插入的值不能为 NULL。

示例 4：创建表 tbl2，并设置列 col2 为唯一性约束。

obclient> CREATE TABLE tbl2(col1 INT UNIQUE,col2 INT);
Query OK, 0 rows affected

obclient> desc tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  | UNI | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

示例 4 中，列 col2 的值不能有重复值。

示例 5：创建外键约束。

obclient> CREATE TABLE test(c1 INT, c2 INT, CONSTRAINT PK_c1 PRIMARY KEY(c1));
Query OK, 0 rows affected

obclient> CREATE TABLE tbl3(col1 INT PRIMARY KEY,col2 INT,CONSTRAINT FK_col2 FOREIGN KEY(col2) REFERENCES test(c1));
Query OK, 0 rows affected

obclient> SELECT * FROM information_schema.TABLE_CONSTRAINTS;
+--------------------+-------------------+-------------------------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME               | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-------------------------------+--------------+------------+-----------------+----------+
| def                | xxx               | PRIMARY                       | xxx          | test       | PRIMARY KEY     | YES      |
| def                | xxx               | PRIMARY                       | xxx          | tbl3       | PRIMARY KEY     | YES      |
| def                | xxx               | FK_col2                       | xxx          | tbl3       | FOREIGN KEY     | YES      |
+--------------------+-------------------+-------------------------------+--------------+------------+-----------------+----------+
3 rows in set

示例 5 中，表 tbl3 的列 col2 关联另一个表 test 的主键列 c1。创建成功后，可以通过 information_schema.TABLE_CONSTRAINTS 视图查看。

示例 6：创建表 tbl4，并设置列 col1 的值需大于 10。

obclient> CREATE TABLE tbl4(col1 INT CHECK(col1>10),col2 INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl4 VALUES(2,2);
ERROR 3819 (HY000): check constraint violated

obclient> INSERT INTO tbl4 VALUES(11,2);
Query OK, 1 row affected

示例 6 中，由于 col1 列添加了 CHECK 约束，后续插入列 col1 的值不大于 10 时就会报错。

定义表的自增列

在 OceanBase 数据库中，如果创建表时需要某个数值列的值不重复并且保持递增，可以将表中列的类型定义为 AUTO_INCREMENT，即自增列。

自增列有三个重要属性，包括自增起始值、自增步长和自增列缓存大小，通过 auto_increment_cache_size、auto_increment_increment 和 auto_increment_offset 三个租户变量来控制。

变量名	说明
auto_increment_cache_size	Global 级变量，用于设置自增的缓存个数，取值范围为 [1, 100000000]，默认值为 `1000000`。
auto_increment_increment	Session 级变量，用于设置自增步长，取值范围为 [1, 65535]，默认值为 `1`。
auto_increment_offset	Session 级变量，用于确定 `AUTO_INCREMENT` 列值的起点，取值范围为 [1, 65535]，默认值为 `1`。

您可以根据业务需要，修改这三个系统变量的值。以下是定义自增列需要遵循的要求：

AUTO_INCREMENT 是数据列的一种属性，仅适用于整数类型的数据列。
AUTO_INCREMENT 所在的数据列必须具备 NOT NULL 属性。
创建分区表时，若使用自增列作为分区键，则在 OceanBase 数据库中，自增列的值全局唯一，但在分区内不保证递增。

自增列创建后，如果使用 INSERT 语句插入数据时指定了自增列的值，且系统变量 SQL_MODE 未设置为 NO_AUTO_VALUE_ON_ZERO ，如果指定的值为 0，则系统会使用自增列的下一个值填充列的值；如果指定的值比当前最大值小，则不影响自增列的下一个值的计算；如果指定的值比当前值最大值大，则自增列会将本次插入值与自增列缓存值的和作为下次自增的起始值。

说明

如果系统变量 SQL_MODE 的值为 NO_AUTO_VALUE_ON_ZERO ，则表示不要在插入值为零的列中生成 AUTO_INCREMENT。

示例 7：创建一个含自增列的表。

obclient> CREATE TABLE personal_info(id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(50), gmt_create timestamp NOT NULL default current_timestamp);
Query OK, 0 rows affected

示例 7 中，由于设置了 id 列为自增列，所以在使用 INSERT 语句插入数据时不需要指定自增列的值，系统就会自动为该列填充值，如下所示。

obclient> INSERT INTO personal_info(name) VALUES('A'),('B'),('C');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM personal_info;
+----+------+---------------------+
| id | name | gmt_create          |
+----+------+---------------------+
|  1 | A    | 2020-04-03 17:09:55 |
|  2 | B    | 2020-04-03 17:09:55 |
|  3 | C    | 2020-04-03 17:09:55 |
+----+------+---------------------+
3 rows in set

选择分区方案

在创建表时，需要明确表的分区方案，如果表中的数据量很大时，建议对表进行分区。创建分区表时，需要根据待存储在表中的数据来选择合适的分区方式。

在 OceanBase 数据库的 MySQL 模式中，按照分区策略，分区表可以分为：

Range 分区 / Range Columns 分区
List 分区 / List Columns 分区
Hash 分区 / key 分区
组合分区

按照分区维度，分区表又可以分为一级分区和二级分区，二级分区是一级分区的二次拆分，因此一级分区表有一个分区键，二级分区表有两个分区键，并且两次拆分的策略可以不一样。在 OceanBase 数据库中，二级分区表包括模板化二级分区表和非模板化二级分区表。

Range 分区/Range Columns 分区

Range 分区和 Range Columns 分区都是按照每个分区建立的分区键值范围来划分分区。通常适用于对分区键需要按照范围的查询。例如，按照时间字段或者价格区间等进行范围分区。

Range 分区和 Range Columns 分区的区别如下：

Range 分区的分区键必须是整数类型，如果对日期字段分区，则需要使用函数进行转换。例如，针对 date 字段进行分区时，要使用 YEAR() 函数进行转换。而 Range Columns 分区的分区键不要求是整数，可以是任意类型。
Range 分区的分区键可以写表达式，但不能是列（列向量），例如，不能写 partition by range(c1, c2)。Range Columns 分区的分区键不能写表达式，但是可以是多列（列向量）。

Range 分区和 Range Columns 分区使用 VALUES LESS THAN(value) 关键字来定义每个分区，其中 value 的值只能是连续不重叠的从小到大的值。

示例 8：创建 Range Columns 分区表。

obclient> CREATE TABLE tb1_rc(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        ); 
Query OK, 0 rows affected

示例 8 中，由于 Range Columns 分区的分区键可以是任意类型，可以使用 col1 列名作为分区键，分区表 tb1_rc 将会按照 100、200、300 的数值范围进行分区。p0、p1、p2 为指定的分区名，可以自定义，只需要满足同一个表中每个分区名不重复即可。

List 分区/List Columns 分区

List 分区是根据具体的数值分区，每个分区的数值不重叠。List 分区的优点是可以方便的对无序或无关的数据集进行分区。

如果要使用多列的 List 分区，或者其他数据类型的 List 分区，可以使用 List Columns 分区。List Columns 分区是 List 分区的一个扩展，支持多个分区键，并且支持 INT 数据、DATE 类型和 DATETIME 类型。

List 分区和 List Columns 分区的区别如下：

List 分区的分区键必须是整数类型，List Columns 分区的分区键可以是任意类型。
List 分区仅支持单分区键，其分区键可以是一列，也可以是表达式，List Columns 分区的分区键不能是表达式，但可以是多列（列向量）。

List 分区和 List Columns 分区使用 VALUES IN(value_list) 关键字来定义每个分区。

示例 9：创建一个 List 分区表。

obclient> CREATE TABLE tbl2_l (col1 INT,col2 DATE)
       PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (100),
         PARTITION p1 VALUES IN (200)
        );
Query OK, 0 rows affected

示例 9 中，由于 List 分区的分区键必须是整数类型，使用 col1 列作为分区键，分区表 tbl2_l 将会按照 100、200 的范围对数值进行分区。

Hash 分区/key 分区

Hash 分区需要指定分区键和分区个数。系统通过 Hash 的分区表达式计算得到一个整数，根据这个结果再跟分区个数取模可以得到具体某行数据属于哪个分区。

Key 分区与 Hash 分区类似，都是通过对分区个数取模的方式来确定数据属于哪个分区。不同的是系统会对 Key 分区键做一个内部默认的 Hash 函数后再取模。所以用户通常没有办法自己通过简单的计算来得知某一行属于哪个分区。

Key 分区与 Hash 分区的区别如下：

Hash 分区的分区键必须是整数，Key 分区没有该要求，且支持字符列。
Hash 分区的分区键支持表达式，Key 分区的分区键不能是表达式。

示例 10：创建一个 HASH 分区表 tbl1_h。

obclient> CREATE TABLE tbl3_h(col1 INT,col2 VARCHAR(50))
    PARTITION BY HASH(col1) PARTITIONS 2;
Query OK, 0 rows affected

示例 10 中，由于 Hash 分区的分区键必须是整数，可以使用 col1 作为分区键，将表 tbl3_h 分为 2 个区。由于创建表时，未指定分区的名称，分区命名由系统根据命名规则完成，即每个分区分别命名为 p0、p1、...、pn。

组合分区（二级分区）

组合分区通常是先使用一种分区策略，然后在子分区再使用另外一种分区策略，适合于业务表的数据量非常大时。使用组合分区能发挥多种分区策略的优点。

Range 分区、Range Columns 分区、List 分区、List Columns 分区、Hash 分区和 key 分区均可以作为组合分区表的二级分区策略。在 OceanBase 数据库中，二级分区表包括模板化二级分区表和非模板化二级分区表。

下面通过几个简单示例，简单介绍二级分区表的创建方法。

示例 11：创建模板化 Range Columns + Range 分区表。

obclient> CREATE TABLE tb1_m_rcr(col1 INT,col2 INT) 
         PARTITION BY RANGE COLUMNS(col1)
         SUBPARTITION BY RANGE(col2)
         SUBPARTITION TEMPLATE 
          (SUBPARTITION mp0 VALUES LESS THAN(3),
           SUBPARTITION mp1 VALUES LESS THAN(6),
           SUBPARTITION mp2 VALUES LESS THAN(9)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200),
           PARTITION p2 VALUES LESS THAN(300)
          ); 
Query OK, 0 rows affected

obclient> SELECT table_name,partition_name,subpartition_name FROM information_schema.partitions;
+------------+----------------+-------------------+
| table_name | partition_name | subpartition_name |
+------------+----------------+-------------------+
| tb1_m_rcr  | p0             | p0smp0            |
| tb1_m_rcr  | p0             | p0smp1            |
| tb1_m_rcr  | p0             | p0smp2            |
| tb1_m_rcr  | p1             | p1smp0            |
| tb1_m_rcr  | p1             | p1smp1            |
| tb1_m_rcr  | p1             | p1smp2            |
| tb1_m_rcr  | p2             | p2smp0            |
| tb1_m_rcr  | p2             | p2smp1            |
| tb1_m_rcr  | p2             | p2smp2            |
+------------+----------------+-------------------+
9 rows in set

示例 11 中，由于 Range 分区的分区键要求为整数类型，二次分区可以直接使用 col2 列名作为分区键，使用 SUBPARTITION TEMPLATE 关键字来创建模板化二级分区表。模板化二级分区表的每个一级分区下的二级分区都按照模板中的二级分区定义，即每个一级分区下的二级分区定义均相同。本示例中，先使用 Range 分区方式进行分区，再使用 List 分区方式进行二次分区。

此外，创建模板化二级分区时，完成二级分区的定义后，不需要单独指定每个分区的分区名，由系统根据命名规则统一完成，每个二级分区的命名规则为 ($part_name)s($subpart_name)，可以看到，本示例中，一级分区 p0 对应的二级分区的分区名分别为 p0smp0、p0smp1、p0smp2。

注意

对于 Hash/Key 分区，如果是通过指定分区数的方式（例如，SUBPARTITIONS 5）进行二次分区，则在创建模板化二级分区表时，不需要指定 SUBPARTITION TEMPLATE 关键字。

示例 12：创建非模板化 List + List Columns 分区表。

obclient> CREATE TABLE tbl2_f_llc(col1 INT,col2 DATE) 
       PARTITION BY LIST(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES IN(100)
           (SUBPARTITION sp0 VALUES IN('2021/04/01'),
            SUBPARTITION sp1 VALUES IN('2021/07/01'),
            SUBPARTITION sp2 VALUES IN('2021/10/01'),
            SUBPARTITION sp3 VALUES IN('2022/01/01')
           ),
         PARTITION p1 VALUES IN(200)
           (SUBPARTITION sp4 VALUES IN('2021/04/01'),
            SUBPARTITION sp5 VALUES IN('2021/07/01'),
            SUBPARTITION sp6 VALUES IN('2021/10/01'),
            SUBPARTITION sp7 VALUES IN('2022/01/01')
            )
         );
Query OK, 0 rows affected

示例 12 中，创建的是非模板化二级分区表，需要为每个一级分区定义其二级分区，每个一级分区下的二级分区的定义可以相同也可以不同。

示例 13：创建非模板化 Hash + Key 分区表。

obclient> CREATE TABLE tbl3_f_hk (col1 INT,col2 VARCHAR(50)) 
     PARTITION BY HASH(col1) 
     SUBPARTITION BY KEY(col2)
      (PARTITION p1
       (SUBPARTITION sp0
       ,SUBPARTITION sp1
       ,SUBPARTITION sp2
       ,SUBPARTITION sp3
       ),
       PARTITION p2
       (SUBPARTITION sp4
       ,SUBPARTITION sp5
       ,SUBPARTITION sp6
       ,SUBPARTITION sp7
       )
      );
Query OK, 0 rows affected

示例 13 中，由于 Key 分区支持字符列作为分区键，可以使用 col2 列作为分区键进行二次分区，其中 sp0、……、sp7 为指定的二级分区名。