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 subpartition template
        | LIST{(expr)}       -- List subpartition template
        SUBPARTITION TEMPLATE [(subpartition_definition [, subpartition_definition] ...)]} -- Subpartition template
    ]
    [(partition_definition [, partition_definition] ...)]

subpartition_definition:
  SUBPARTITION logical_name
        [VALUES 
           { LESS THAN {(expr | value_list) | MAXVALUE} -- Value range of the range subpartition
           | IN (value_list)}]  -- Value range of the list subpartition
      [[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) */;

-- Support for subpartitions
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options] [subpartition_options] -- Add subpartition_options.

subpartition_options:
    subpartition_option [subpartition_option] ...

subpartition_options: {
    MODIFY PARTITION partition_name TRUNCATE SUBPARTITION TEMPLATE subpartition_template_name; -- Truncate subpartitions defined using a template named subpartition_template_name within a partition named partition_name.
  | TRUNCATE SUBPARTITION TEMPLATE subpartition_template_name -- Truncate subpartitions defined using a template named subpartition_template_name.
  | ADD SUBPARTITION TEMPLATE subpartition_definitions -- Add a template named subpartition_definitions.
  | DROP SUBPARTITION TEMPLATE subpartition_template_name -- Drop subpartitions defined using a template named subpartition_template_name.
}

-- Partition DDL
ALTER TABLE t1 TRUNCATE p0 WITH GLOBAL INDEX; -- Truncate the p1 partition (truncate all subpartitions under it if any).

-- Subpartition DDL
ALTER TABLE t1 MODIFY PARTITION p0 TRUNCATE SUBPARTITION TEMPLATE s1;    -- Truncate the p0_s1 subpartition (the partition name is p0 and the subpartition template name is s1).
ALTER TABLE t1 TRUNCATE SUBPARTITION TEMPLATE s1; -- Truncate all subpartitions with a name containing the _s1 suffix.
ALTER TABLE t1 ADD SUBPARTITION TEMPLATE (SUBPARTITION s2 values in (1,2,3,4)); -- Add subpartitions with a name containing the _s2 suffix to all partitions.
ALTER TABLE t1 DROP SUBPARTITION TEMPLATE s2;  -- Drop subpartitions with a name containing the _s2 suffix from all partitions.

-- Partition DDL
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (20));  -- Add a partition. If t1 is a subpartition table, subpartitions will be generated using the template by default.
ALTER TABLE t1 DROP PARTITION p1;
ALTER TABLE t1 TRUNCATE p0 WITH GLOBAL INDEX; -- Truncate the p1 partition (truncate all subpartitions under it if any).