tencent cloud

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [SUBPARTITION BY
      {   RANGE{(expr)}    -- range二级分区模板
        | LIST{(expr)}       -- list二级分区模板
        SUBPARTITION TEMPLATE [(subpartition_definition [, subpartition_definition] ...)]} -- 子分区模板
    ]
    [(partition_definition [, partition_definition] ...)]

subpartition_definition:
  SUBPARTITION logical_name
        [VALUES 
           { LESS THAN {(expr | value_list) | MAXVALUE} --Range子分区范围
           | IN (value_list)}]  --List子分区范围
      [[STORAGE] ENGINE [=] engine_name]
      [COMMENT [=] 'string' ]
      [DATA DIRECTORY [=] 'data_dir']
      [INDEX DIRECTORY [=] 'index_dir']
      [MAX_ROWS [=] max_number_of_rows]
      [MIN_ROWS [=] min_number_of_rows]
      [TABLESPACE [=] tablespace_name]

CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `purchased` int DEFAULT NULL,
  KEY `idx` (`id`,`purchased`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
SUBPARTITION BY RANGE (`purchased`)
SUBPARTITION TEMPLATE
(SUBPARTITION s0 VALUES LESS THAN (10) ENGINE = InnoDB,
 SUBPARTITION s1 VALUES LESS THAN (20) ENGINE = InnoDB)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB) */;

-- 二级分区的支持
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options] [subpartition_options] -- 新增subpartition_options

subpartition_options:
    subpartition_option [subpartition_option] ...

subpartition_options: {
    MODIFY PARTITION partition_name TRUNCATE SUBPARTITION TEMPLATE subpartition_template_name; -- truncate 分区partition_name中模版名为subpartition_template_name的分区
  | TRUNCATE SUBPARTITION TEMPLATE subpartition_template_name -- truncate subpartition_template_name的二级分区
  | ADD SUBPARTITION TEMPLATE subpartition_definitions -- 新增subpartition_definitions模版
  | DROP SUBPARTITION TEMPLATE subpartition_template_name -- drop subpartition_template_name的二级分区
}

-- 一级分区DDL
ALTER TABLE t1 TRUNCATE p0 WITH GLOBAL INDEX; -- truncate p1分区（若存在子分区，则truncate下面所有子分区）

-- 二级分区 DDL
ALTER TABLE t1 MODIFY PARTITION p0 TRUNCATE SUBPARTITION TEMPLATE s1;    -- truncate 二级分区p0_s1（一级分区p0，二级分区模板名s1）
ALTER TABLE t1 TRUNCATE SUBPARTITION TEMPLATE s1; --  所有分区后缀为_s1的子分区都将被truncate
ALTER TABLE t1 ADD SUBPARTITION TEMPLATE （SUBPARTITION s2 values in (1,2,3,4)); -- 所有分区将添加后缀为_s2的的子分区
ALTER TABLE t1 DROP SUBPARTITION TEMPLATE s2;  -- 所有分区将删除后缀为_s2的子分区；

-- 一级分区 DDL
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (20));  -- 添加分区，若t1是二级分区表，这里将默认使用模板来生成子分区
ALTER TABLE t1 DROP PARTITION p1;
ALTER TABLE t1 TRUNCATE p0 WITH GLOBAL INDEX; -- truncate p1分区（若存在子分区，则truncate下面所有子分区）