Usage notes
To modify a level-1 partition, the version of your Alibaba Cloud PolarDB for Xscale (PolarDB-X) instance must be 5.4.14-16539836 or later.
To modify a level-2 partition, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.
You cannot modify a partition of a table that is partitioned by using the HASH, KEY, or CO_HASH partitioning method.
You cannot modify a partition of a table that is partitioned by using the RANGE or RANGE COLUMNS partitioning method.
You cannot modify a partition of a table that is partitioned by using the LIST or LIST COLUMNS partitioning method and contains a catch-all partition, DEFAULT.
You cannot delete values by performing the DROP VALUES operation in a global index table.
You cannot delete values by performing the DROP VALUES operation in a partitioned table that contains global indexes.
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 modify_partition_values_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
modify_partition_values_operation_definition:
MODIFY PARTITION part_name values_operation_definition
| MODIFY SUBPARTITION subpart_name values_operation_definition
values_operation_definition:
ADD VALUES (value_list)
| DROP VALUES (value_list)
Scenario 1: Modify values in a level-1 partition
In this example, the l_t1 and l_t2 tables are partitioned by using the LIST partitioning method. You can execute the following SQL statements to create the tables:
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,2024,2026),
PARTITION p1 VALUES IN (2021,2023,2025,2027)
);
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,2024,2026),
PARTITION p1 VALUES IN (2021,2023,2025,2027)
);Example 1: Add values to a LIST level-1 partition
Add values, 2017 and 2019, to a LIST level-1 partition (p1 in this example) of the l_t1 table. The following section describes the statements that are used to add values to partitions:
Add values to a partition of a table
## Add values to a partition of a table based on the table name.
ALTER TABLE l_t1 /*Table name*/
MODIFY PARTITION p1 ADD VALUES (2017,2019);Add values to partitions in a table group
Execute the following statements to add values to partitions of both the l_t1 and l_t2 tables in the tg1 table group:
## Add values to partitions in a table group based on the table group name. In this example, the table group name is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY PARTITION p1 ADD VALUES (2017,2019);
## Add values to partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are modified.
ALTER TABLEGROUP BY TABLE l_t1 /*Table name*/
MODIFY PARTITION p1 ADD VALUES (2017,2019);Example 2: Remove values from a LIST level-1 partition
Remove values, 2021 and 2025, from a LIST level-1 partition (p1 in this example) of the l_t1 table. The following section describes the statements that are used to remove values from partitions:
Remove values from a partition of a table
## Remove values from a partition of a table based on the table name.
ALTER TABLE l_t1 /*Table name*/
MODIFY PARTITION p1 DROP VALUES (2021,2025);Remove values from partitions in a table group
Execute the following statements to remove values from partitions of both the l_t1 and l_t2 tables in the tg1 table group:
## Remove values from partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY PARTITION p1 DROP VALUES (2021,2025);
## Remove values from partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are modified.
ALTER TABLEGROUP BY TABLE l_t1 /*Table name*/
MODIFY PARTITION p1 DROP VALUES (2021,2025);Scenario 2: Modify values in a templated level-2 partition
In this example, the KEY-LIST partitioned tables named k_l_tp_t1 and k_l_tp_t2 contain templated level-2 partitions. You can execute the following SQL statements to create the tables:
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,2024,2026),
SUBPARTITION sp1 VALUES IN (2021,2023,2025,2027)
);
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,2024,2026),
SUBPARTITION sp1 VALUES IN (2021,2023,2025,2027)
);Example 1: Add values to a templated LIST level-2 partition
Add values, 2017 and 2019, to a templated LIST level-2 partition (sp1 in this example) of the k_l_tp_t1 table. The following section describes the statements that are used to add values to partitions:
Add values to a partition of a table
## Add values to a partition of a table based on the table name.
ALTER TABLE k_l_ntp_t1 /*Table name*/
MODIFY SUBPARTITION sp1 /*Name of the level-2 partition*/ADD VALUES (2017,2019);Add values to a partition in a table group
Execute the following statements to add values to partitions of both the k_l_tp_t1 and k_l_tp_t2 tables in the tg1 table group:
## Add values to partitions in a table group based on the table group name. In this example, the table group name is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY SUBPARTITION sp1 /*Name of the level-2 partition*/ ADD VALUES (2017,2019);
## Add values to partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are modified.
ALTER TABLEGROUP BY TABLE k_l_tp_t1 /*Table name*/
MODIFY SUBPARTITION sp1 /*Name of the level-2 partition*/ADD VALUES (2017,2019);Example 2: Remove values from a templated LIST level-2 partition
Remove values, 2021 and 2025, from a templated LIST level-2 partition (sp1 in this example) of the k_l_tp_t1 table. The following section describes the statements that are used to remove values from partitions:
Remove values from a partition of a table
## Remove values from a partition of a table based on the table name.
ALTER TABLE k_l_ntp_t1 /*Table name*/
MODIFY SUBPARTITION sp1 /*Name of the level-2 partition*/ DROP VALUES (2021,202 5);Remove values from partitions in a table group
Execute the following statements to remove values from partitions of both the k_l_tp_t1 and k_l_tp_t2 tables in the tg1 table group:
## Remove values from partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY SUBPARTITION sp1 /*Name of the level-2 partition*/ DROP VALUES (2021,202 5);
## Remove values from partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are modified.
ALTER TABLEGROUP BY TABLE k_l_tp_t1 /*Table name*/
MODIFY SUBPARTITION sp1 /*Name of the level-2 partition*/ DROP VALUES (2021,202 5);Scenario 3: Modify values in a non-templated level-2 partition
In this example, the KEY-LIST partitioned tables named k_l_ntp_t1 and k_l_ntp_t2 contain non-templated level-2 partitions. You can execute the following SQL statements to create the tables:
CREATE TABLE `k_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 KEY(`a`) PARTITIONS 2
SUBPARTITION BY LIST(YEAR(`c`))
(
PARTITION p1 (
SUBPARTITION p1sp1 VALUES IN (2020,2022,2024,2026,2028),
SUBPARTITION p1sp2 VALUES IN (2021,2023,2025,2027,2029)
),
PARTITION p2 (
SUBPARTITION p2sp1 VALUES IN (2020,2022,2024,2026,2028),
SUBPARTITION p2sp2 VALUES IN (2021,2023,2025,2027,2029),
SUBPARTITION p2sp3 VALUES IN (2030,2031)
)
);
CREATE TABLE `k_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 KEY(`a`) PARTITIONS 2
SUBPARTITION BY LIST(YEAR(`c`))
(
PARTITION p1 (
SUBPARTITION p1sp1 VALUES IN (2020,2022,2024,2026,2028),
SUBPARTITION p1sp2 VALUES IN (2021,2023,2025,2027,2029)
),
PARTITION p2 (
SUBPARTITION p2sp1 VALUES IN (2020,2022,2024,2026,2028),
SUBPARTITION p2sp2 VALUES IN (2021,2023,2025,2027,2029),
SUBPARTITION p2sp3 VALUES IN (2030,2031)
)
);Example 1: Add values to a non-templated level-2 partition
Add values, 2017 and 2019, to a non-templated level-2 partition (p1sp2 in this example) under the level-1 partition p1 of the k_l_ntp_t1 table. The following section describes the statements that are used to add values to a partition:
Add values to a partition of a table
## Add values to a partition of a table based on the table name.
ALTER TABLE k_l_ntp_t1 /*Table name*/
MODIFY SUBPARTITION p1sp2 ADD VALUES (2017,2019);Add values to partitions in a table group
Execute the following statements to add values to partitions of both the k_l_ntp_t1 and k_l_ntp_t2 tables in the tg1 table group:
## Add values to partitions in a table group based on the table group name. In this example, the table group name is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY SUBPARTITION p1sp2 /*Name of the non-templated level-2 partition*/ ADD VALUES (2017,2019);
## Add values to partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are modified.
ALTER TABLEGROUP BY TABLE k_l_ntp_t1 /*Table name*/
MODIFY SUBPARTITION p1sp2 /*Name of the non-templated level-2 partition*/ ADD VALUES (2017,2019);Example 2: Remove values from a non-templated LIST level-2 partition
Remove values, 2021 and 2025, from a non-templated LIST level-2 partition (p1sp2 in this example) under the level-1 partition p1 of the k_l_ntp_t1 table. The following section describes the statements that are used to remove values from partitions:
Remove values from a partition of a table
## Remove values from a partition of a table based on the table name.
ALTER TABLE k_l_ntp_t1 /*Table name*/
MODIFY SUBPARTITION p1sp2 /*Name of the non-templated level-2 partition*/ DROP VALUES (2021,202 5);Remove values from partitions in a table group
Execute the following statements to remove values from partitions of both the k_l_ntp_t1 and k_l_ntp_t2 tables in the tg1 table group:
## Remove values from partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY SUBPARTITION p1sp2 DROP VALUES (2021,2025);
## Remove values from partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are modified.
ALTER TABLEGROUP BY TABLE k_l_ntp_t1 /*Table name*/
MODIFY SUBPARTITION p1sp2 /*Name of the non-templated level-2 partition*/ DROP VALUES (2021,202 5);Scenario 4: Modify values in a partition of a global index table
Example 1: Add values to a specified LIST level-1 partition of a specified global index table
In this example, the partitioned table r_gl_t1 contains a global index table named g_l. The g_l global index table is partitioned by using the LIST partitioning method. You can execute the corresponding SQL statement shown in the following code block to create the r_gl_t1 table:
CREATE TABLE `r_gl_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_l` (`a`) COVERING (`c`)
PARTITION BY LIST(`a`) (
PARTITION `p0` VALUES IN (10000,10002,10004,10006,10008),
PARTITION `p1` VALUES IN (20000,20001,20002,20003,20004),
PARTITION `p2` VALUES IN (30000,30001,30002)
)
) 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_gl_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,
GLOBAL INDEX `g_l` (`a`) COVERING (`c`)
PARTITION BY LIST(`a`) (
PARTITION `p0` VALUES IN (10000,10002,10004,10006,10008),
PARTITION `p1` VALUES IN (20000,20001,20002,20003,20004),
PARTITION `p2` VALUES IN (30000,30001,30002)
)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(PARTITION `p0` VALUES LESS THAN (2020),
PARTITION `p1` VALUES LESS THAN (2021));Add values, 30003 and 30004, to a level-1 partition (p2 in this example) of the g_l global index table of the r_gl_t1 table. The following section describes the statements that are used to add values to a partition:
Add values to a partition of a table
## Add values to a partition of a global index table based on `Table name`.`Global index name`.
ALTER TABLE `r_gl_t1`.`g_l`
MODIFY PARTITION p2 ADD VALUES (30003,30004);
## Add values to a partition of a global index table based on `Global index name` ON TABLE `Table name`.
ALTER INDEX `g_l`/*Global index name*/ ON TABLE `r_gl_t1` /*Table name*/
MODIFY PARTITION p2 ADD VALUES (30003,30004);
Add values to partitions in a table group
Execute the following statements to add values to partitions of both the g_l global index tables of the r_gl_t1 and r_gl_t2 tables in the tg1 table group:
## Add values to partitions in a table group based on the table group name. In this example, the name of the table group to which the global index tables belong is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY PARTITION sp2 ADD VALUES (30003,30004);;
## Add values to partitions of global index tables in the same table group based on `Global index name` ON TABLE `Table name`.
ALTER TABLEGROUP BY INDEX `g_l`/*Global index name*/ ON TABLE `r_gl_t1` /*Table name*/
MODIFY PARTITION p2 ADD VALUES (30003,30004);Example 2: Add values to a specified LIST level-2 partition of a specified global index table
In this example, the partitioned table r_g_kl_t1 contains a global index table named g_kl. The KEY-LIST global index table g_kl contains level-2 partitions. You can execute the corresponding SQL statement shown in the following code block to create the r_g_kl_t1 table:
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,10002,10004,10006,10008),
SUBPARTITION `sp1` VALUES IN (20000,20001,20002,20003,20004),
SUBPARTITION `sp2` VALUES IN (30000,30001,30002)
)
) 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_g_kl_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,
GLOBAL INDEX `g_kl` (`a`,`b`) COVERING (`c`)
PARTITION BY KEY(`b`) PARTITIONS 2
SUBPARTITION BY LIST(`a`)
(
SUBPARTITION `sp0` VALUES IN (10000,10002,10004,10006,10008),
SUBPARTITION `sp1` VALUES IN (20000,20001,20002,20003,20004),
SUBPARTITION `sp2` VALUES IN (30000,30001,30002)
)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(PARTITION `p0` VALUES LESS THAN (2020),
PARTITION `p1` VALUES LESS THAN (2021));Add values, 30003 and 30004, to a level-2 partition (sp2 in this example) of the g_kl global index table of the r_g_kl_t1 table. The following section describes the statements that are used to add values to a partition:
Add values to a partition of a table
## Add values to a partition of a global index table based on `Table name`.`Global index name`.
ALTER TABLE `r_g_kl_t1`.`g_kl`
MODIFY SUBPARTITION sp2 ADD VALUES (30003,30004);
## Add values to a partition of 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*/
MODIFY SUBPARTITION sp2 ADD VALUES (30003,30004);Add values to partitions in a table group
Execute the following statements to add values to partitions of both the g_kl global index tables of the r_g_kl_t1 and r_g_kl_t2 tables in the tg1 table group:
## Add values to partitions in a table group based on the table group name. In this example, the name of the table group to which the global index tables belong is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
MODIFY SUBPARTITION sp2 ADD VALUES (30003,30004);
## Add values to partitions of all global index tables in the same table group 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*/
MODIFY SUBPARTITION sp2 ADD VALUES (30003,30004);