跳到主要内容

分区操作

seekdb下的分区操作包括重分区、添加分区、删除分区和清空分区。

重分区​

重分区的语法如下：

ALTER TABLE table_name PARTITION BY (partition_definitions);

重分区示例​

将 Hash 分区更改为 Key 分区方式的示例​

CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2))
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected (0.104 sec)

ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows (0.994 sec)

将一级 Hash 分区表修改为模板化 Hash+Range 二级分区表的示例​

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 affeaffected (0.746 sec)

将 Hash + Range 二级分区表转为 Range Columns+Range Columns 分区示例​

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 (0.836 sec)

添加分区​

添加分区的语法如下：

ALTER TABLE table_name ADD PARTITION (partition_definition)

添加分区的示例如下：

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 (0.094 sec)

  ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );

删除分区​

删除分区的语法如下：

ALTER TABLE table_name DROP PARTITION partition_name;

删除分区的示例如下：

ALTER TABLE t_log_part_by_range DROP PARTITION M202006;
Query OK, 0 rows affected (0.063 sec)

清空分区​

清空分区的语法如下:

ALTER TABLE table_name TRUNCATE PARTITION partition_name;

清空分区的示例如下：

 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 (0.078 sec)

  ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  Query OK, 0 rows affected (0.086 sec)

Contents

编辑此页