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

subpartition_definition:
  SUBPARTITION logical_name
        [VALUES 
           { LESS THAN {(expr | value_list) | MAXVALUE} --Range-type sub-partition range.
           | IN (value_list)}]  --List-type sub-partition range.
      [[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) */;

-- Secondary partition DDL.
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options] [subpartition_options] -- Add new subpartition_options.

subpartition_options:
    subpartition_option [subpartition_option] ...

subpartition_options: {
    MODIFY PARTITION partition_name TRUNCATE SUBPARTITION TEMPLATE subpartition_template_name; -- Truncate the partitions with the template named subpartition_template_nam in the partition_name.
  | TRUNCATE SUBPARTITION TEMPLATE subpartition_template_name -- Truncate secondary partitions of the subpartition_template_name.
  | ADD SUBPARTITION TEMPLATE subpartition_definitions -- Add a template for subpartition_definitions.
  | DROP SUBPARTITION TEMPLATE subpartition_template_name -- Drop secondary partitions of subpartition_template_name.
}

-- First-level partition DDL.
ALTER TABLE t1 TRUNCATE p0 WITH GLOBAL INDEX; -- Truncate partition p1. (If sub-partitions exist, truncate all the following sub-partitions.)

-- Secondary partition DDL.
ALTER TABLE t1 MODIFY PARTITION p0 TRUNCATE SUBPARTITION TEMPLATE s1;    -- Truncate secondary partition p0_s1 (first-level partition p0, secondary partition template name s1).
ALTER TABLE t1 TRUNCATE SUBPARTITION TEMPLATE s1; --  Sub-partitions with the suffix of _s1 under all partitions will be truncated.
ALTER TABLE t1 ADD SUBPARTITION TEMPLATE （SUBPARTITION s2 values in (1,2,3,4)); -- Sub-partitions with the suffix of _s2 will be added for all partitions.
ALTER TABLE t1 DROP SUBPARTITION TEMPLATE s2;  -- Sub-partitions with the suffix of _s2 will be deleted for all partitions.

-- First-level partition DDL.
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (20));  -- Add a partition. If t1 is a secondary partition table, the template will be used to generate sub-partitions by default.
ALTER TABLE t1 DROP PARTITION p1;
ALTER TABLE t1 TRUNCATE p0 WITH GLOBAL INDEX; -- Truncate partition p1. (If sub-partitions exist, truncate all the following sub-partitions.)