我的书签
添加书签
移除书签可以通过执行 DDL 语句 CREATE TABLE 来创建一个表。
使用 CREATE TABLE 语句建表
- 下面示例使用 CREATE TABLE 语句创建订单表 ware 和 cust 表。
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)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)
说明
由于 ALTER TABLE 语法不支持后期增加主键，所以需要在建表的时候设置主键。
使用 CREATE TABLE 复制表数据
在 MySQL 租户里，可以使用 CREATE TABLE AS SELECT 复制表的数据，但是结构并不完全一致，会丢失约束、索引、默认值、分区等信息。使用 CREATE TABLE LIKE 可以复制表结构，但是不包括数据。
- 示例：MySQL租户的 CREATE TABLE 复制表结构和数据的区别
obclient> create table t1(id bigint not null primary KEY, name varchar(50) not NULL, gmt_create timestamp not null default current_timestamp) partition by hash(id) partitions 8;Query OK, 0 rows affected (0.10 sec)obclient> insert into t1(id,name) values(1,'A'),(2,'B'),(3,'C');Query OK, 3 rows affected (0.03 sec)Records: 3 Duplicates: 0 Warnings: 0obclient> create table t1_like like t1;Query OK, 0 rows affected (0.11 sec)obclient> create table t1_copy as select * from t1;Query OK, 3 rows affected (0.12 sec)obclient> show create table t1_like\G*************************** 1. row ***************************Table: t1_likeCreate Table: CREATE TABLE `t1_like` (`id` bigint(20) NOT NULL,`name` varchar(50) NOT NULL,`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10partition by hash(id) partitions 81 row in set (0.00 sec)obclient> show create table t1_copy\G*************************** 1. row ***************************Table: t1_copyCreate Table: CREATE TABLE `t1_copy` (`id` bigint(20) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`gmt_create` timestamp NULL DEFAULT NULL) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 101 row in set (0.00 sec)obclient> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` bigint(20) NOT NULL,`name` varchar(50) NOT NULL,`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10partition by hash(id) partitions 81 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 (p1) 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 |+------+--------+--------------+----------+-------------+------------+1 row in set (0.01 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 (p1) 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 | 0.00 | 87133 | 1 | 5 || 2100 | 2 | 2020-02-15 | 0.00 | 47413 | 1 | 5 || 2100 | 3 | 2020-02-15 | 0.00 | 9115 | 1 | 5 || 2100 | 4 | 2020-02-15 | 0.00 | 42985 | 1 | 5 || 2100 | 5 | 2020-02-15 | 0.00 | 43621 | 1 | 5 || 2100 | 6 | 2020-02-15 | 0.00 | 5787 | 1 | 5 || 2100 | 7 | 2020-02-15 | 0.00 | 62576 | 1 | 5 || 2100 | 8 | 2020-02-15 | 0.00 | 91592 | 1 | 5 || 2100 | 9 | 2020-02-15 | 0.00 | 34452 | 1 | 5 || 2100 | 10 | 2020-02-15 | 0.00 | 13792 | 1 | 5 || 2100 | 11 | 2020-02-15 | 0.00 | 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 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 |+------+--------+--------------+----------+-------------+------------+1 row in set (0.00 sec)obclient>
复制表
复制表是分布式数据库 OceanBase 的高级优化手段。
通常 OceanBase 集群是三副本架构，默认每个表的每个分区在 OceanBase 中会有三个副本数据，角色上分为一个主副本（Leader 副本）和两个备副本（Follower副本），默认提供读写服务的是主副本。
复制表可以指定在租户的每台机器上都有一个备副本，并且主副本跟所有备份的数据使用全同步策略保持强同步。这样做的目的是为了让业务有些 SQL 关联查询时能在同一节点内部执行，以获取更好的性能。
复制表的语法是在 CREATE TABLE 语句后增加 DUPLICATE_SCOPE选项。
- 示例：创建复制表。
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=16384duplicate_scope='cluster' locality='F,R{all_server}@doc_1, F,R{all_server}@doc_2,F,R{all_server}@doc_3' primary_zone='doc_1';
