OceanBase表分组

创建表分组

创建表分组

mysql> create tablegroup my_group partition by hash partitions 6;
Query OK, 0 rows affected (2.33 sec)

查看表分组：可以看到该表分组属于哪个数据库、表分组中包含哪些表信息

mysql> show tablegroups where tablegroup_name='my_group';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| my_group        | NULL       | NULL          |
+-----------------+------------+---------------+

查看表分组create语句

mysql> show create tablegroup my_group;
+------------+-------------------------------------------------------------------------------+
| Tablegroup | Create Tablegroup                                                             |
+------------+-------------------------------------------------------------------------------+
| my_group   | CREATE TABLEGROUP IF NOT EXISTS `my_group` 
  partition by hash partitions 6
 |
+------------+-------------------------------------------------------------------------------+

创建表指定表分组

创建表时指定表分组

创建表并指定表分组

mysql> create table t1(id int not null auto_increment primary key,name varchar(30)) tablegroup my_group partition by hash(id) partitions 6;

查看表分组：可以看到该表分组属于哪个数据库、表分组中包含哪些表信息

mysql> show tablegroups where tablegroup_name='my_group';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| my_group        | t1         | oceanbase     |
+-----------------+------------+---------------+

ALTER TABLE指定表分组

创建表

create table t2(id int not null primary key,name varchar(30)) partition by hash(id) partitions 6;

查看表分组my_group

mysql> show tablegroups where tablegroup_name='my_group';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| my_group        | t1         | oceanbase     |
+-----------------+------------+---------------+

添加表t2到表分组my_group中

alter table t2 tablegroup='my_group';

查看表分组my_group

mysql> show tablegroups where tablegroup_name='my_group';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| my_group        | t1         | oceanbase     |
| my_group        | t2         | oceanbase     |
+-----------------+------------+---------------+

ALTER TABLEGROUP指定表分组 

创建表

create table t3(id int not null primary key,name varchar(30)) partition by hash(id) partitions 6;

查看表分组my_group

mysql> show tablegroups where tablegroup_name='my_group';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| my_group        | t1         | oceanbase     |
| my_group        | t2         | oceanbase     |
+-----------------+------------+---------------+

 添加表t3到表分组my_group中

alter tablegroup my_group add t3;

查看表分组my_group

mysql> show tablegroups where tablegroup_name='my_group';
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| my_group        | t1         | oceanbase     |
| my_group        | t2         | oceanbase     |
| my_group        | t3         | oceanbase     |
+-----------------+------------+---------------+