Specify a table group when creating a table

This topic describes how to specify a table group when you create a data table in OceanBase Database in MySQL mode.

Prerequisites and usage notes

Observe the following notes if you specify a table group when creating a table:

• To specify a partitioned table to an existing partitioned table group when you create the table, ensure that this partitioned table is of the same partitioning method and has the same number of partitions as this table group.

  If the locality and primary zone information is specified for the partitioned table and table group, this information must be consistent between the table and table group.

• Ensure that you have the CREATE TABLE privilege on the target table and table group.

Examples

Example 1

Create a non-partitioned table group and specify this table group when you create a table.

1. Create a table group named myTableGroup1.

   obclient> CREATE TABLEGROUP myTableGroup1;
   Query OK, 0 rows affected
   

2. Create a table named myt1 and add it to the myTableGroup1 table group.

   obclient>  CREATE TABLE myt1 (c1 int, c2 int ) TABLEGROUP = myTableGroup1;
   Query OK, 0 rows affected
   

3. Create a table named myt2 and add it to the myTableGroup1 table group.

   obclient> CREATE TABLE myt2 (c1 int, c2 int ) TABLEGROUP = myTableGroup1;
   Query OK, 0 rows affected
   

4. View the myt1 and myt2 tables added to the myTableGroup1 table group.

   obclient> SHOW TABLEGROUPS WHERE Tablegroup_name='myTableGroup1';
   +-----------------+------------+---------------+
   | Tablegroup_name | Table_name | Database_name |
   +-----------------+------------+---------------+
   | myTableGroup1   | myt1       | test          |
   | myTableGroup1   | myt2       | test          |
   +-----------------+------------+---------------+
   2 rows in set7
   

Example 2

Create a partitioned table group and specify this table group when you create a table.

1. Create a partitioned table group named tgh.

   obclient> CREATE TABLEGROUP tgh PARTITION BY HASH PARTITIONS 10;
   Query OK, 0 rows affected
   

2. Create a table named ttgh and add it to the tgh table group.

   obclient>CREATE TABLE ttgh(c1 INT, c2 INT) TABLEGROUP=tgh PARTITION BY HASH(c1) PARTITIONS 10;
   Query OK, 0 rows affected
   

3. Create a table named ttgh2 and add it to the tgh table group.

   obclient> CREATE TABLE ttgh2(c1 INT, c2 INT) TABLEGROUP=tgh PARTITION BY HASH(c1) PARTITIONS 10;
   Query OK, 0 rows affected
   

4. View the ttgh and ttgh2 tables added to the tgh table group.

   obclient> SHOW TABLEGROUPS WHERE Tablegroup_name='tgh';
   +-----------------+------------+---------------+
   | Tablegroup_name | Table_name | Database_name |
   +-----------------+------------+---------------+
   | tgh             | ttgh       | test          |
   | tgh             | ttgh2      | test          |
   +-----------------+------------+---------------+
   2 rows in set
   

Example 3

Add a template-based or non-template-based subpartitioned table to a table group.

1. Create a partitioned table group named tg2_f_rr.

   obclient> CREATE TABLEGROUP tg2_f_rr 
          PARTITION BY RANGE COLUMNS 1
          SUBPARTITION BY RANGE COLUMNS 1
          (PARTITION p0 VALUES LESS THAN (100)
           (SUBPARTITION sp1 VALUES LESS THAN (2019),
            SUBPARTITION sp2 VALUES LESS THAN (2020),
            SUBPARTITION sp3 VALUES LESS THAN (2021)
            ),
           PARTITION p1 VALUES LESS THAN (200)
           (SUBPARTITION sp5 VALUES LESS THAN (2019),
            SUBPARTITION sp6 VALUES LESS THAN (2020),
            SUBPARTITION sp7 VALUES LESS THAN (2021)
           )
          );
   Query OK, 0 rows affected
   

2. Create a subpartitioned table named t2_f_rr without using a template and specify a table group tg2_f_rr.

   obclient> CREATE TABLE t2_f_rr(col1 int, col2 int)
          TABLEGROUP = tg2_f_rr
          PARTITION BY RANGE COLUMNS(col1)
          SUBPARTITION BY RANGE COLUMNS(col2)
          (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION p0_r1 VALUES LESS THAN(2019),
            SUBPARTITION p0_r2 VALUES LESS THAN(2020),
            SUBPARTITION p0_r3 VALUES LESS THAN(2021)
           ),
           PARTITION p1 VALUES LESS THAN(200)
          (SUBPARTITION p1_r1 VALUES LESS THAN(2019),
           SUBPARTITION p1_r2 VALUES LESS THAN(2020),
           SUBPARTITION p1_r3 VALUES LESS THAN(2021)
           )
          );
   Query OK, 0 rows affected
   

3. View the t2_f_rr table added to the tg2_f_rr table group.

   obclient> SHOW TABLEGROUPS WHERE Tablegroup_name='tg2_f_rr';
   +-----------------+------------+---------------+
   | Tablegroup_name | Table_name | Database_name |
   +-----------------+------------+---------------+
   | tg2_f_rr        | t2_f_rr    | test          |
   +-----------------+------------+---------------+
   1 row in set