The partitioned tables of PolarDB-X databases in automatic partitioning mode allow you to directly read data from and write data to a specific partition by using the MySQL syntax.
SQL syntax
SELECT operation on a specific partition
SELECT ... FROM tbl_name [PARTITION ( part_name[, part_name, ...] )]UPDATE operation on a specific partition
UPDATE tbl_name [PARTITION ( part_name[, part_name, ...] )] SET ... WHERE ...DELETE operation on a specific partition
DELETE FROM tbl_name [PARTITION ( part_name[, part_name, ...] )] WHERE ...Query data from partitions
Example 1: Query data from one or more partitions in a partitioned table
CREATE TABLE tb_k(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(id, bid)
-> PARTITIONS 8;
Query OK, 0 rows affected (2.06 sec)
explain SELECT * FROM tb_k PARTITION( p1,p2 );
+-----------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+-----------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="tb_k[p1,p2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k` AS `tb_k`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: e210fe50 |
+-----------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.05 sec)
Example 2: Delete data from one or more partitions in a partitioned table
CREATE TABLE tb_k(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(id, bid)
-> PARTITIONS 8;
Query OK, 0 rows affected (3.59 sec)
explain DELETE FROM tb_k PARTITION( p1,p2 );
+---------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+---------------------------------------------------------------------------------+
| LogicalModifyView(tables="tb_k[p1,p2]", shardCount=2, sql="DELETE FROM `tb_k`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 19bd2adf |
+---------------------------------------------------------------------------------+
Query data from subpartitions
Example 3: Query data from one or more partitions or subpartitions in a partitioned table
CREATE TABLE tb_k_k_tp(
-> id bigint not null auto_increment,
-> bid int,
-> name varchar(30),
-> birthday datetime not null,
-> primary key(id)
-> )
-> PARTITION BY KEY(bid,name)
-> PARTITIONS 2
-> SUBPARTITION BY KEY(id)
-> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.94 sec)
explain SELECT * FROM tb_k_k_tp PARTITION( p1sp1,p1sp2 )/* Specify two subpartitions. */;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: 38bba74d |
+--------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)
explain SELECT * FROM tb_k_k_tp PARTITION( p1,p2sp2 )/* Specify a partition and a subpartition. */;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2,p1sp3,p1sp4,p2sp2]", shardCount=5, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: dbc4cb56 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)