Usage notes
If you want to add a level-1 partition, the version of your Alibaba Cloud PolarDB for Xscale (PolarDB-X) instance must be 5.4.14-16539836 or later.
If you want to add a level-2 partition, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.
You cannot add partitions to a table that is partitioned by using a Hash partitioning method, such as HASH partitioning, KEY partitioning, and CO_HASH partitioning.
You cannot add partitions to a table that is partitioned by using the RANGE or RANGE COLUMNS partitioning method and contains a catch-all partition, such as the MAXVALUE partition.
You cannot add partitions to a table that is partitioned by using the LIST or LIST COLUMNS partitioning method and contains a catch-all partition, such as the DEFAULT partition.
Terms
Table group: a collection of logical tables or global index tables that share identical partition key columns.
Global index: an indexing technique for partitioned tables. You can create global indexes on partitioned tables by using non-partition keys. Global indexes can provide unique constraints.
Syntax
ALTER alter_target_definition add_partition_operation_definition
alter_target_definition:
TABLE [db_name.]tbl_name
| TABLE tbl_name.index_name
| TABLEGROUP tg_name
| INDEX index_name ON TABLE [db_name.]tbl_name
| TABLEGROUP BY TABLE [db_name.]tbl_name
| TABLEGROUP BY INDEX index_name ON TABLE [db_name.]tbl_name
add_partition_operation_definition:
ADD PARTITION (partition_specs_definition)
| ADD SUBPARTITION (subpartition_specs_definition)
| MODIFY PARTITION part_name ADD SUBPARTITION (subpartition_specs_definition)
partition_specs_definition:
range_partition_list
| list_partition_list
subpartition_specs_definition:
range_subpartition_list
| list_subpartition_list
range_partition_list:
( range_partition [, range_partition, ... ] )
range_partition:
PARTITION partition_name VALUES LESS THAN (range_bound_value)
# Specify the RANGE or RANGE COLUMNS level-2 partition.
range_subpartition_list:
( range_subpartition [, range_subpartition, ... ] )
range_subpartition:
SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]
list_partition_list:
(list_partition [, list_partition ...])
list_partition:
PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options]
# Specify the LIST or LIST COLUMNS level-2 partition.
list_subpartition_list:
(list_subpartition [, list_subpartition ...])
list_subpartition:
SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]
Scenario 1: Add a level-1 partition that does not contain level-2 partitions
Example 1: Add a RANGE level-1 partition
In this example, you can execute the following SQL statements to create two RANGE partitioned tables named r_t1 and r_t2:
CREATE TABLE `r_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021)
);
CREATE TABLE `r_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021)
);
You can execute one of the following SQL statements to add a RANGE partition to the r_t1 table. The new RANGE partition is defined by using the PARTITION p2 VALUES LESS THAN (2022) clause.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE r_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the r_t1 and r_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE r_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );Example 2: Add a LIST level-1 partition
In this example, you can execute the following SQL statements to create two LIST partitioned tables named l_t1 and l_t2:
CREATE TABLE `l_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(YEAR(`c`))
(
PARTITION p0 VALUES IN (2020,2022),
PARTITION p1 VALUES IN (2021,2023)
);
CREATE TABLE `l_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(YEAR(`c`))
(
PARTITION p0 VALUES IN (2020,2022),
PARTITION p1 VALUES IN (2021,2023)
);You can execute one of the following SQL statements to add a LIST partition to the l_t1 table. The new LIST partition is defined by using the PARTITION p2 VALUES IN (2024) clause.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE l_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES IN (2024) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the l_t1 and l_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD PARTITION ( PARTITION p2 VALUES IN (2024) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE l_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES IN (2024) );Scenario 2: Add a level-1 partition that contains level-2 partitions
Example 1: Add a level-1 partition with specified level-2 partitions
To use this method, the level-2 partitions must be non-templated level-2 partitions.
In this example, you can execute the following SQL statements to create two RANGE-KEY partitioned tables named r_k_ntp_t1 and r_k_ntp_t2 that contain non-templated level-2 partitions:
CREATE TABLE `r_k_ntp_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY KEY(a)
(
PARTITION p0 VALUES LESS THAN (2020) SUBPARTITIONS 2,
PARTITION p1 VALUES LESS THAN (2021) SUBPARTITIONS 4
);
CREATE TABLE `r_k_ntp_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY KEY(a)
(
PARTITION p0 VALUES LESS THAN (2020) SUBPARTITIONS 2,
PARTITION p1 VALUES LESS THAN (2021) SUBPARTITIONS 4
);You can execute one of the following SQL statements to add a RANGE partition to the r_k_ntp_t1 table. The new RANGE partition is defined by using the PARTITION p2 VALUES LESS THAN (2022) clause. In addition, you can specify that the new RANGE level-1 partition contains three level-2 partitions.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE r_k_ntp_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2024) SUBPARTITIONS 3 ) ;Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the r_k_ntp_t1 and r_k_ntp_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2024) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE r_k_ntp_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2024) );Example 2: Add a level-1 partition without specified level-2 partitions
To use this method, the level-2 partitions can be templated level-2 partitions and non-templated level-2 partitions.
You can add a level-1 partition without specified level-2 partitions in a similar way that you did in scenario 1. You can execute one of the following SQL statements.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE r_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the r_k_ntp_t1 and r_k_ntp_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE r_k_t1 /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );Scenario 3: Add a templated level-2 partition
When you add a templated level-2 partition, the templated level-2 partition is added for all level-1 partitions.
Example 1: Add a RANGE level-2 partition
In this example, you can execute the following SQL statements to create two KEY-RANGE partitioned tables named k_r_tp_t1 and k_r_tp_t2 that contain templated level-2 partitions:
CREATE TABLE `k_r_tp_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY RANGE(YEAR(`c`))
(
SUBPARTITION sp0 VALUES LESS THAN (2020),
SUBPARTITION sp1 VALUES LESS THAN (2021)
);
CREATE TABLE `k_r_tp_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY RANGE(YEAR(`c`))
(
SUBPARTITION sp0 VALUES LESS THAN (2020),
SUBPARTITION sp1 VALUES LESS THAN (2021)
);You can execute one of the following SQL statements to add a RANGE level-2 partition to the k_r_tp_t1 table. The new RANGE level-2 partition is defined by using the UBPARTITION sp2 VALUES LESS THAN (2022) clause.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE k_r_tp_t1 /*Table name*/
ADD SUBPARTITION ( SUBPARTITION p2 VALUES LESS THAN (2022) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the k_r_tp_t1 and k_r_tp_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD SUBPARTITION ( SUBPARTITION p2 VALUES LESS THAN (2022) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE k_r_tp_t1 /*Table name*/
ADD SUBPARTITION ( SUBPARTITION p2 VALUES LESS THAN (2022) );Example 2: Add a LIST level-2 partition
In this example, you can execute the following SQL statements to create two KEY-LIST partitioned tables named k_l_tp_t1 and k_l_tp_t2:
CREATE TABLE `k_l_tp_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY LIST(YEAR(`c`))
(
SUBPARTITION sp0 VALUES IN (2020,2022),
SUBPARTITION sp1 VALUES IN (2021,2023)
);
CREATE TABLE `k_l_tp_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY LIST(YEAR(`c`))
(
SUBPARTITION sp0 VALUES IN (2020,2022),
SUBPARTITION sp1 VALUES IN (2021,2023)
);You can execute one of the following SQL statements to add a LIST level-2 partition to the k_l_tp_t1 table. The new LIST level-2 partition is defined by using the SUBPARTITION p2 VALUES IN (2024) clause.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE k_l_tp_t1 /*Table name*/
ADD SUBPARTITION ( SUBPARTITION p2 VALUES IN (2024) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the k_l_tp_t1 and k_l_tp_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD SUBPARTITION ( SUBPARTITION p2 VALUES IN (2024) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE k_l_tp_t1 /*Table name*/
ADD SUBPARTITION ( SUBPARTITION p2 VALUES IN (2024) );Scenario 4: Add a non-templated level-2 partition
You can add a non-templated level-2 partition only for a specified level-1 partition.
Example 1: Add a RANGE level-2 partition for a specified level-1 partition
In this example, you can execute the following SQL statements to create two LIST-RANGE partitioned tables named l_r_ntp_t1 and l_r_ntp_t2 that contain non-templated level-2 partitions:
CREATE TABLE `l_r_ntp_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(`a`)
SUBPARTITION BY RANGE(YEAR(`c`))
(
PARTITION p0 VALUES IN (2020,2022) (
SUBPARTITION p0sp0 VALUES LESS THAN (2020),
SUBPARTITION p0sp1 VALUES LESS THAN (2022)
),
PARTITION p1 VALUES IN (2021,2023) (
SUBPARTITION p1sp0 VALUES LESS THAN (2021),
SUBPARTITION p1sp1 VALUES LESS THAN (2023),
SUBPARTITION p1sp2 VALUES LESS THAN (2025)
)
);
CREATE TABLE `l_r_ntp_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(`a`)
SUBPARTITION BY RANGE(YEAR(`c`))
(
PARTITION p0 VALUES IN (2020,2022) (
SUBPARTITION p0sp0 VALUES LESS THAN (2020),
SUBPARTITION p0sp1 VALUES LESS THAN (2022)
),
PARTITION p1 VALUES IN (2021,2023) (
SUBPARTITION p1sp0 VALUES LESS THAN (2021),
SUBPARTITION p1sp1 VALUES LESS THAN (2023),
SUBPARTITION p1sp2 VALUES LESS THAN (2025)
)
);You can execute one of the following statements to add a RANGE level-2 partition for the p0 level-1 partition of the l_r_ntp_t1 table. The new RANGE level-2 partition is defined by using the SUBPARTITION p0sp2 VALUES LESS THAN (2024) clause.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE l_r_ntp_t1 /*Table name*/
MODIFY PARTITION p0
ADD SUBPARTITION ( SUBPARTITION p0sp2 VALUES LESS THAN (2024) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the l_r_ntp_t1 and l_r_ntp_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY PARTITION p0
ADD SUBPARTITION ( SUBPARTITION p0sp2 VALUES LESS THAN (2024) );
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY l_r_ntp_t1 /*Table name*/
MODIFY PARTITION p0
ADD SUBPARTITION ( SUBPARTITION p0sp2 VALUES LESS THAN (2024) );Example 2: Add a LIST level-2 partition for a specified level-1 partition
In this example, you can execute the following SQL statements to create two RANGE-LIST partitioned tables named r_l_ntp_t1 and r_l_ntp_t2 that contain non-templated level-2 partitions:
CREATE TABLE `r_l_ntp_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY LIST(`a`)
(
PARTITION p0 VALUES LESS THAN (2020) (
SUBPARTITION p0sp0 VALUES IN (2017),
SUBPARTITION p0sp1 VALUES IN (2018)
),
PARTITION p1 VALUES LESS THAN (2022) (
SUBPARTITION p1sp0 VALUES IN (2020),
SUBPARTITION p1sp1 VALUES IN (2021)
)
);
CREATE TABLE `r_l_ntp_t2` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY LIST(`a`)
(
PARTITION p0 VALUES LESS THAN (2020) (
SUBPARTITION p0sp0 VALUES IN (2017),
SUBPARTITION p0sp1 VALUES IN (2018)
),
PARTITION p1 VALUES LESS THAN (2022) (
SUBPARTITION p1sp0 VALUES IN (2020),
SUBPARTITION p1sp1 VALUES IN (2021)
)
);
You can execute one of the following statements to add a LIST level-2 partition for the p0 level-1 partition of the r_l_ntp_t1 table. The new LIST level-2 partition is defined by using the SUBPARTITION p0sp2 VALUES IN (2019) clause.
Add a partition to a table
## Add a partition to a table based on the table name.
ALTER TABLE r_l_ntp_t1 /*Table name*/
MODIFY PARTITION p0
ADD SUBPARTITION (SUBPARTITION p0sp2 VALUES IN (2020));Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to both the r_l_ntp_t1 and r_l_ntp_t2 tables:
## Add a partition to a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY PARTITION p0
ADD SUBPARTITION (SUBPARTITION p0sp2 VALUES IN (2020));
## Add a partition to a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE r_l_ntp_t1 /*Table name*/
MODIFY PARTITION p0
ADD SUBPARTITION (SUBPARTITION p0sp2 VALUES IN (2020));Scenario 5: Add a partition to a specified global index table
Example 1: Add a RANGE level-1 partition to a specified global index table
In this example, you can execute the following SQL statement to create a table named r_gr_t1 that uses a global index:
CREATE TABLE `r_gr_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL,
GLOBAL INDEX `g_r` (`a`) COVERING (`c`)
PARTITION BY RANGE(`a`)
(PARTITION `p0` VALUES LESS THAN (10000),
PARTITION `p1` VALUES LESS THAN (20000)),
KEY `auto_shard_key_c` USING BTREE (`c`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(PARTITION `p0` VALUES LESS THAN (2020),
PARTITION `p1` VALUES LESS THAN (2021));You can execute one of the following SQL statements to add a RANGE partition to the g_r global index table of the r_gr_t1 table. The new RANGE partition is defined by using the PARTITION p2 VALUES LESS THAN (30000) clause.
Add a partition to a table
## Add a partition to a global index table based on `Table name`.`Global index name`.
ALTER TABLE `r_gr_t1`.`g_r`
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (30000) );
## Add a partition to a global index table based on `Global index name` ON TABLE `Table name`.
ALTER INDEX g_r/*Global index name*/ ON TABLE `r_gr_t1` /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (30000) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, you can execute one of the following statements to add a partition to the g_r global index table:
## Add a partition to a table group based on the table group name. In this example, the name of the table group to which the global index table belongs is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );
## Add a partition to a table group to which the global index table belongs based on `Global index name` ON TABLE `Table name`.
ALTER TABLEGROUP BY INDEX `g_r`/*Global index name*/ ON TABLE `r_gr_t1` /*Table name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (30000) );Example 2: Add a LIST level-2 partition to a specified global index table
In this example, you can execute the following SQL statement to create a table named r_g_kl_t1 that uses a global index. In addition, the global index table is partitioned by using the KEY-LIST partitioning method.
CREATE TABLE `r_g_kl_t1` (
`a` bigint(20) UNSIGNED NOT NULL,
`b` bigint(20) UNSIGNED NOT NULL,
`c` datetime NOT NULL,
`d` varchar(16) NOT NULL,
`e` varchar(16) NOT NULL,
GLOBAL INDEX `g_kl` (`a`,`b`) COVERING (`c`)
PARTITION BY KEY(`b`) PARTITIONS 2
SUBPARTITION BY LIST(`a`)
(SUBPARTITION `sp0` VALUES IN (10000),
SUBPARTITION `sp1` VALUES IN (20000))
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(PARTITION `p0` VALUES LESS THAN (2020),
PARTITION `p1` VALUES LESS THAN (2021));You can execute one of the following SQL statements to add a LIST level-2 partition to the g_kl global index table of the r_g_kl_t1 table. The new LIST level-2 partition is defined by using the SUBPARTITION sp2 VALUES IN (30000) clause.
Add a partition to a table
## Add a partition to a global index table based on `Table name`.`Global index name`.
ALTER TABLE `r_g_kl_t1`.`g_kl`
ADD SUBPARTITION ( SUBPARTITION sp2 VALUES IN (30000) );
## Add a partition to a global index table based on `Global index name` ON TABLE `Table name`.
ALTER INDEX g_kl/*Global index name*/ ON TABLE `r_g_kl_t1` /*Table name*/
ADD SUBPARTITION ( SUBPARTITION sp2 VALUES IN (30000) );Add a partition to a table group
When you add a partition to a table group, the partition is added to all logical tables in the table group. In this example, the partition is added to the table group to which the g_kl global index table belongs.
## Add a partition to a table group based on the table group name. In this example, the name of the table group to which the global index table belongs is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
ADD PARTITION ( PARTITION p2 VALUES LESS THAN (2022) );
## Add a partition to a table group to which the global index table belongs based on INDEX `Global index name` ON TABLE `Table name`.
ALTER TABLEGROUP BY INDEX `g_kl`/*Global index name*/ ON TABLE `r_g_kl_t1` /*Table name*/
ADD SUBPARTITION ( SUBPARTITION sp2 VALUES IN (30000) );