创建和管理表 - 创建表 - 《OceanBase v3.1.1 官方教程》

使用 CREATE TABLE 语句建表
使用 CREATE TABLE 复制表数据
关于表和分区
复制表

可以通过执行 DDL 语句 CREATE TABLE 来创建一个表。

使用 CREATE TABLE 语句建表

下面示例对 Oracle 模式内建类型的支持。

CREATE TABLE t_data_types(
    id                  number NOT NULL PRIMARY KEY
    ,c_char             char(100)
    ,c_varchar          varchar2(100)
    ,c_nchar            nchar(100)
    ,c_nvarchar         nvarchar2(100)
    ,c_numeric          numeric(10,2)
    ,c_int              int
    ,c_float            float(2)
    ,c_binary_float     binary_float
    ,c_binary_double    binary_double
    ,c_date             date
    ,c_timestamp        timestamp
    ,c_timestamp2       timestamp WITH time ZONE
    ,c_timestamp3       timestamp WITH LOCAL time zone
    ,c_interval_year    INTERVAL YEAR TO MONTH 
    ,c_interval_day     INTERVAL DAY TO SECOND 
,c_raw              raw(2000)
    ,c_blob             blob
    ,c_clob             clob
);
Query OK, 0 rows affected (0.14 sec)

创建后可以通过DESC命令查看列属性。

+-----------------+-----------------------------------+------+-----+---------+-------+
| FIELD           | TYPE                              | NULL | KEY | DEFAULT | EXTRA |
+-----------------+-----------------------------------+------+-----+---------+-------+
| ID              | NUMBER                            | NO   | PRI | NULL    | NULL  |
| C_CHAR          | CHAR(100)                         | YES  | NULL | NULL    | NULL  |
| C_VARCHAR       | VARCHAR2(100)                     | YES  | NULL | NULL    | NULL  |
| C_NCHAR         | NCHAR(100)                        | YES  | NULL | NULL    | NULL  |
| C_NVARCHAR      | NVARCHAR2(100)                    | YES  | NULL | NULL    | NULL  |
| C_NUMERIC       | NUMBER(10,2)                      | YES  | NULL | NULL    | NULL  |
| C_INT           | NUMBER(38)                        | YES  | NULL | NULL    | NULL  |
| C_FLOAT         | FLOAT(2)                          | YES  | NULL | NULL    | NULL  |
| C_BINARY_FLOAT  | BINARY_FLOAT                      | YES  | NULL | NULL    | NULL  |
| C_BINARY_DOUBLE | BINARY_DOUBLE                     | YES  | NULL | NULL    | NULL  |
| C_DATE          | DATE                              | YES  | NULL | NULL    | NULL  |
| C_TIMESTAMP     | TIMESTAMP(6)                      | YES  | NULL | NULL    | NULL  |
| C_TIMESTAMP2    | TIMESTAMP(6) WITH TIME ZONE       | YES  | NULL | NULL    | NULL  |
| C_TIMESTAMP3    | TIMESTAMP(6) WITH LOCAL TIME ZONE | YES  | NULL | NULL    | NULL  |
| C_INTERVAL_YEAR | INTERVAL YEAR (2) TO MONTH        | YES  | NULL | NULL    | NULL  |
| C_INTERVAL_DAY  | INTERVAL DAY (2) TO SECOND (6)    | YES  | NULL | NULL    | NULL  |
| C_RAW           | RAW(2000)                         | YES  | NULL | NULL    | NULL  |
| C_BLOB          | BLOB                              | YES  | NULL | NULL    | NULL  |
| C_CLOB          | CLOB                              | YES  | NULL | NULL    | NULL  |
+-----------------+-----------------------------------+------+-----+---------+-------+
19 rows in set (0.01 sec)

下面示例使用 CREATE TABLE 语句创建订单表 ware 和 cust 表。

obclient> create table ware(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 cust (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 ware(w_id)
  , primary key (c_w_id, c_d_id, c_id)
);
Query OK, 0 rows affected (0.10 sec)

说明

建议建表的时候为表设计主键或者唯一键，这样对性能或者后期维护都很有益。如果没有合适的字段作为主键，可以为表增加一个数值列作为主键，使用 OceanBase Oracle 租户的序列为这一列填充值。有关序列介绍请参见创建和管理序列。

由于 ALTER TABLE 语法不支持后期增加主键，所以在建表的时候设置主键就更加有必要了。

使用 CREATE TABLE 复制表数据

Oracle 租户不支持 CREATE TABLE LIKE 复制表结构的方法，所以完全的复制表结构必须拿到源表的 DDL，更改名字重新创建新表。

Oracle 租户支持 CREATE TABLE AS SELECT 复制表的基本数据类型和数据，但不包含约束、索引和非空等属性。

obclient> show create table t3\G
*************************** 1. row ***************************
       TABLE: T3
CREATE TABLE: CREATE TABLE "T3" (
  "ID" NUMBER NOT NULL,
  "NAME" VARCHAR2(50),
  "GMT_CREATE" DATE DEFAULT sysdate NOT NULL,
  CONSTRAINT "T3_OBPK_1582718853302518" PRIMARY KEY ("ID"),
  CONSTRAINT "T3_UK" UNIQUE ("NAME", "ID")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
 partition by hash(id) partitions 8
1 row in set (0.01 sec)
obclient> create table t3_like like t3;
ERROR-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'like t3' at line 1
obclient> create table t3_copy as select * from t3;
Query OK, 0 rows affected (0.10 sec)
obclient> show create table t3_copy\G
*************************** 1. row ***************************
       TABLE: T3_COPY
CREATE TABLE: CREATE TABLE "T3_COPY" (
  "ID" NUMBER,
  "NAME" VARCHAR2(50),
  "GMT_CREATE" DATE
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
1 row in set (0.00 sec)

关于表和分区

在 OceanBase 数据库中，数据存储在表中，而数据表示的最小粒度是分区。普通的非分区表，就只有一个分区；而分区表，通常有多个分区，分区名默认以 p 开头，按数字顺序从 0 开始编号。所以分区是表的子集。

通常分区对用户的应用是透明的，应用只需要使用 SQL 读写表即可。只有某些场景下，为了提升分区表的查询性能，应用也可以使用 SQL 直接访问某个具体的分区，SQL 语法格式是：

SELECT ... FROM parted_table PARTITION (pN) WHERE query_condition ;

示例：通过 SQL 直接访问分区表的分区

obclient> select o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d from ordr partition (p5) where o_w_id=1 and o_d_id=2 and o_id=2100;
+------+--------+--------------+----------+-------------+---------------------+
| O_ID | O_C_ID | O_CARRIER_ID | O_OL_CNT | O_ALL_LOCAL | O_ENTRY_D           |
+------+--------+--------------+----------+-------------+---------------------+
| 2100 |      8 |            8 |       11 |           1 | 2020-02-15 18:57:10 |
+------+--------+--------------+----------+-------------+---------------------+
1 row in set (0.00 sec)
obclient> select ol_o_id, ol_number,ol_delivery_d,ol_amount,ol_i_id,ol_supply_w_id,ol_quantity from ordl partition (p5) where ol_w_id=1 and ol_d_id=2 and ol_o_id=2100;
+---------+-----------+---------------------+-----------+---------+----------------+-------------+
| OL_O_ID | OL_NUMBER | OL_DELIVERY_D       | OL_AMOUNT | OL_I_ID | OL_SUPPLY_W_ID | OL_QUANTITY |
+---------+-----------+---------------------+-----------+---------+----------------+-------------+
|    2100 |         1 | 2020-02-15 18:57:10 |         0 |   87133 |              1 |           5 |
|    2100 |         2 | 2020-02-15 18:57:10 |         0 |   47413 |              1 |           5 |
|    2100 |         3 | 2020-02-15 18:57:10 |         0 |    9115 |              1 |           5 |
|    2100 |         4 | 2020-02-15 18:57:10 |         0 |   42985 |              1 |           5 |
|    2100 |         5 | 2020-02-15 18:57:10 |         0 |   43621 |              1 |           5 |
|    2100 |         6 | 2020-02-15 18:57:10 |         0 |    5787 |              1 |           5 |
|    2100 |         7 | 2020-02-15 18:57:10 |         0 |   62576 |              1 |           5 |
|    2100 |         8 | 2020-02-15 18:57:10 |         0 |   91592 |              1 |           5 |
|    2100 |         9 | 2020-02-15 18:57:10 |         0 |   34452 |              1 |           5 |
|    2100 |        10 | 2020-02-15 18:57:10 |         0 |   13792 |              1 |           5 |
|    2100 |        11 | 2020-02-15 18:57:10 |         0 |   94326 |              1 |           5 |
+---------+-----------+---------------------+-----------+---------+----------------+-------------+
11 rows in set (0.01 sec)

说明

如果是组合分区，可以访问更细粒度的分区，详细描述请参考分区路由。

在 OceanBase 数据库里，节点间的数据迁移的最小粒度是分区，每个分区在集群里有三个副本，内容保持同步，角色上有区分。三副本会有一个主副本（Leader 副本）和两个备副本（Follower 副本），只有主副本可以提供写服务，默认也只有主副本可以提供读服务。主副本上的事务提交时会将事务日志同步到两个备副本，三副本使用 Paxos 协议表决事务是否提交成功。有时候为了不影响主副本，可以让备副本承担部分读请求，这就是应用常用的读写分离的解决方案，

这种读备称为弱一致性读。使用这种方案，应用读需要承担读延时的风险，这个延时最大允许值会通过参数（max_stale_time_for_weak_consistency）控制。

示例：使用 SQL Hint 实现读写分离。

弱一致读的 Hint 语法是 /*+ read_consistency(weak) */ 。通常的读默认是强一致性读，就不用 Hint 了。

obclient> select /*+ read_consistency(weak) */ o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d from ordr partition (p5) where o_w_id=1 and o_d_id=2 and o_id=2100;
+------+--------+--------------+----------+-------------+---------------------+
| O_ID | O_C_ID | O_CARRIER_ID | O_OL_CNT | O_ALL_LOCAL | O_ENTRY_D           |
+------+--------+--------------+----------+-------------+---------------------+
| 2100 |      8 |            8 |       11 |           1 | 2020-02-15 18:57:10 |
+------+--------+--------------+----------+-------------+---------------------+
1 row in set (0.00 sec)

复制表

复制表是分布式数据库 OceanBase 的高级优化手段。

通常 OceanBase 集群是三副本架构，默认每个表的每个分区在 OceanBase 集群中会有三个副本数据，角色上分为一个主副本（Leader 副本）和两个备副本（Follower副本），默认提供读写服务的是主副本。

复制表可以指定在租户的每台机器上都有一个备副本，并且主副本跟所有备份的数据使用全同步策略保持强同步。这样做的目的是为了让业务有些 SQL 关联查询时能在同一节点内部执行，以获取更好的性能。

复制表的语法是在 CREATE TABLE 语句后增加 DUPLICATE_SCOPE 选项。

示例：创建复制表。

obclient> create table item (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' locality='F@zone1, F@zone2,R{all_server}@zone3' primary_zone='zone1';
Query OK, 0 rows affected (0.10 sec)