PolarDB:Naming rules for partitions and subpartitions

To access or modify a specific partition or subpartition, you must specify the name of the partition or subpartition in the syntax.

This topic describes some rules for automatically generating the names of partitions and subpartitions in a partitioned table.

In a partitioned table, the names of partitions and subpartitions may be automatically generated in the following cases:

• The HASH or KEY partitioning policy is used for a manually or automatically partitioned table or a global index table.

• Subpartitions are created based on templated subpartitioning in a partitioned table.

• Partitions are further split in a HASH partitioned table.

Naming rules for partitions

Partition names are automatically generated when the HASH or KEY partitioning policy is used to manually or automatically partition a table and the number of partitions is specified.

If the HASH or KEY partitioning policy is used to partition a table, partitions are named based on the following rule: p + Serial number of the partition. The serial number starts from 1.

CREATE TABLE test_tbl_part_name(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(id) 
PARTITIONS 4

For example, if you execute the preceding statement to create a partitioned table, partitions are named in the following way: The first partition is named p1, the second partition is named p2, and it goes on. The following sample code provides an example:

CREATE TABLE test_tbl_part_name (
    ->  id bigint not null auto_increment, 
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(id) 
    -> PARTITIONS 4;
Query OK, 0 rows affected (1.13 sec)

select table_name,partition_name from information_schema.partitions where table_name='test_tbl_part_name';
+--------------------+----------------+
| table_name         | partition_name |
+--------------------+----------------+
| test_tbl_part_name | p1             |
| test_tbl_part_name | p2             |
| test_tbl_part_name | p3             |
| test_tbl_part_name | p4             |
+--------------------+----------------+
4 rows in set (0.59 sec)

In addition, if you execute the ALTER statement to split a specific partition of the partitioned table, the new partitions are named based on the following rule: p + (Existing maximum serial number of the partition + 1). Example:

alter table test_tbl_part_name split partition p1;
Query OK, 0 rows affected (5.83 sec)

select table_name,partition_name from information_schema.partitions where table_name='test_tbl_part_name';
+--------------------+----------------+
| table_name         | partition_name |
+--------------------+----------------+
| test_tbl_part_name | p5             |
| test_tbl_part_name | p6             |
| test_tbl_part_name | p2             |
| test_tbl_part_name | p3             |
| test_tbl_part_name | p4             |
+--------------------+----------------+
5 rows in set (0.18 sec)

Naming rules for subpartitions

Subpartitions include templated subpartitions and non-templated subpartitions. Therefore, the naming rules for subpartitions are slightly more complex than those for partitions.

Templated HASH subpartitions are named based on the following rule: sp + Serial number of the subpartition. The serial number starts from 1.

The non-templated HASH subpartitions of each partition are separately named based on the following rule: sp + Serial number of the subpartition. The serial number starts from 1.

The actual names of all subpartitions are generated based on the following rule: Name of the partition + Name of the templated or non-templated subpartition.

The following sample code provides an example on how to create a table that contains templated subpartitions:

CREATE TABLE test_tbl_part_name2(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
SUBPARTITIONS 4

The names of all subpartitions in this table are generated in the following way:

1. Names p1 and p2 are automatically generated for HASH partitions.

2. Names sp1, sp2, sp3, and sp4 are automatically generated for templated HASH subpartitions.

3. The actual names of subpartitions, which are the names of physical partitions, are generated based on the following rule: Name of the partition + Name of the subpartition.

   • p1sp1, p1sp2, p1sp3, p1sp4

   • p2sp1, p2sp2, p2sp3, p2sp4

The following sample code provides an example on how to query the data of the partitioned table:

mysql> CREATE TABLE test_tbl_part_name2(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(bid) 
    -> PARTITIONS 2
    -> SUBPARTITION BY KEY(id) 
    -> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.40 sec)

mysql> select table_name,partition_name,subpartition_name from information_schema.partitions where table_schema='autodb2' and table_name='test_tbl_part_name2';
+---------------------+----------------+-------------------+
| table_name          | partition_name | subpartition_name |
+---------------------+----------------+-------------------+
| test_tbl_part_name2 | p1             | p1sp1             |
| test_tbl_part_name2 | p1             | p1sp2             |
| test_tbl_part_name2 | p1             | p1sp3             |
| test_tbl_part_name2 | p1             | p1sp4             |
| test_tbl_part_name2 | p2             | p2sp1             |
| test_tbl_part_name2 | p2             | p2sp2             |
| test_tbl_part_name2 | p2             | p2sp3             |
| test_tbl_part_name2 | p2             | p2sp4             |
+---------------------+----------------+-------------------+
8 rows in set (0.12 sec)