You can execute the CREATE TABLE statement to create a table. CREATE TABLE is a DDL statement.
Use the CREATE TABLE statement to create a table
- The following example shows how to use the CREATE TABLE statement to create two tables: ware and 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.06 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.06 sec)
Note
You cannot add the primary key by executing the ALTER TABLE statement. Therefore, you must specify the primary key when you create a table.
Use the CREATE TABLE statement to copy data from a table
In a MySQL tenant, you can use the CREATE TABLE AS SELECT statement to copy data from a table, but the constraints, indexes, default values, and partitions are lost when you copy the data. The CREATE TABLE LIKE statement allows you to copy the table structure, but it does not copy the data.
- Example: Using the CREATE TABLE statement to copy the table structure and data in a MySQL tenant
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: 0
obclient> 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_like
Create 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 = 10 PROGRESSIVE_MERGE_NUM = 2
partition by hash(id)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7)
1 row in set (0.02 sec)
obclient> show create table t1_copy\G
*************************** 1. row ***************************
Table: t1_copy
Create 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 = 10
1 row in set (0.00 sec)
obclient> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create 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 = 10 PROGRESSIVE_MERGE_NUM = 2
partition by hash(id)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7)
1 row in set (0.01 sec)
About tables and partitions
In OceanBase, data is stored in tables and partitions are the most fine-grained representation of data. A non-partitioned table has only one partition and a partitioned table usually has two or more partitions. By default, the partition name is prefixed with a lower-case p, followed by a number starting from 0. A partition is a subset of a table.
Generally, partitions of a table are transparent to your application. You can read and write the table in your application by executing SQL statements without specifying the partitions. In some cases, to improve the query performance of a partitioned table, you may execute SQL statements to directly access a specified partition. SQL syntax:
SELECT ... FROM parted_table PARTITION (pN) WHERE query_condition ;
- Example: Using an SQL statement to directly access a specified partition of a partitioned table
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)
Note
For a composite partition, you can access more fine-grained partitions. For more information, see Partition routing.
In OceanBase Database, partitions are the most fine-grained data migration unit between nodes. Each partition has three replicas in the cluster. These replicas are synchronized to ensure data consistency and are divided into two roles: leader and follower. The three replicas consist of one leader and two followers. By default, only the leader provides write and read services. When you commit a transaction on the leader, the transaction logs are synchronized to the two followers. The three replicas use the Paxos protocol to vote on whether the transaction is committed. Sometimes, to avoid overloading the leader, the followers can share some read requests. This is the commonly used read/write splitting solution. The process of reading follower replicas is known as weak consistency read . This solution has the risk of stale read caused by the latency between the follower that you read and the leader. You can set the max_stale_time_for_weak_consistency parameter to control the maximum allowed latency.
- Example: Using SQL hints for read/write splitting
Syntax for SQL hints with weak consistency read: /*+ read_consistency(weak) */. Generally, the default read is a strong consistency read and no hint is needed.
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)
Replication
Replication is an advanced optimization method in OceanBase.
Generally, OceanBase uses a three-replica cluster architecture. By default, each partition of a table has three replicas in OceanBase Database, including one leader and two followers. By default, the leader provides read and write services.
You can create a follower on each server in a tenant and maintain strong synchronization between the leader and all replicas by using the full synchronization policy. The enables your application to execute some SQL JOIN queries within the same node for better performance.
The syntax for replication is to add DUPLICATE_SCOPE to the CREATE TABLE statement.
- Example: Creating a replica
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)) 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.06 sec)