The MySQL mode of OceanBase Database allows you to repartition a table, add partitions, drop partitions, and truncate partitions.
Repartition a table
The syntax for repartitioning a table is as follows:
ALTER TABLE table_name PARTITION BY (partition_definitions);
Examples of repartitioning a table
Convert a HASH-partitioned table into a KEY-partitioned table
obclient> CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2))
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows affected
Convert a HASH-partitioned table into a template-based HASH-RANGE-subpartitioned table
obclient> ALTER TABLE tbl2
PARTITION BY HASH(c1)
SUBPARTITION BY RANGE COLUMNS(c2)
SUBPARTITION TEMPLATE(
SUBPARTITION p1 VALUES LESS THAN ('2016-10-10'),
SUBPARTITION p2 VALUES LESS THAN ('2116-3-30')) PARTITIONS 2;
Query OK, 0 rows affected
Convert a HASH-RANGE-subpartitioned table into a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table
obclient> ALTER TABLE tbl2
PARTITION BY RANGE COLUMNS(c1)
SUBPARTITION BY RANGE COLUMNS(c2)
(
PARTITION p0 VALUES LESS THAN (100)
(SUBPARTITION sp0 VALUES LESS THAN ('2020-01-01')
,SUBPARTITION sp1 VALUES LESS THAN ('2021-01-01')
,SUBPARTITION sp2 VALUES LESS THAN ('2022-01-01')
,SUBPARTITION sp3 VALUES LESS THAN ('2023-01-01')
),
PARTITION p1 VALUES LESS THAN (200)
(SUBPARTITION sp4 VALUES LESS THAN ('2020-01-01')
,SUBPARTITION sp5 VALUES LESS THAN ('2021-01-01')
,SUBPARTITION sp6 VALUES LESS THAN ('2022-01-01')
,SUBPARTITION sp7 VALUES LESS THAN ('2023-01-01'))
);
Query OK, 0 rows affected
Add a partition
The syntax for adding a partition is as follows:
ALTER TABLE table_name ADD PARTITION (partition_definition)
Here is an example:
obclient> CREATE TABLE t_log_part_by_range (
log_id bigint NOT NULL
, log_value varchar(50)
, log_date timestamp NOT NULL
) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
(
PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
);
Query OK, 0 rows affected
obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
(PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
);
Drop a partition
The syntax for dropping a partition is as follows:
ALTER TABLE table_name DROP PARTITION partition_name;
Here is an example:
obclient> ALTER TABLE t_log_part_by_range DROP PARTITION M202006;
Query OK, 0 rows affected
Truncate a partition
The syntax for truncating a partition is as follows:
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
Here is an example:
obclient> CREATE TABLE t_log_part_by_range (
log_id bigint NOT NULL
, log_value varchar(50)
, log_date timestamp NOT NULL
) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
(
PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
);
Query OK, 0 rows affected
obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
Query OK, 0 rows affected