Detailed Explanation of MySQL Exchange Partition Example

Detailed Explanation of MySQL Switch Partition Example

Preface

Before introducing swap partitions, let's look at mysql partition under 1.

There are two types of database partitions: horizontal partitions and vertical partitions. However, MySQL does not support vertical partitioning for the time being, so what follows is horizontal partitioning. Horizontal partitioning is partitioning tables in units of rows. For example, according to time partition, one partition every one year, etc.

In MySQL, partitions are interchangeable, and data in one partition table and one ordinary table can be interchanged.

Implementation of swap partition

1. Syntax of swap partition

alter table pt exchange partition p with table nt;

Explanation:

The data in partition p of partition table pt and one common table nt are exchanged.

Prerequisites for exchange:

The normal table nt is not a temporary table and is not a partitioned table. Table structure and partition table pt structure 1. The normal table nt has no foreign key reference. If the general table nt is not empty. In MySQL 5.6 and before, it must be within the scope of partition; After MySQL 5.7, it can no longer be in its partition scope, and it will still be successfully stored in the partition.

2. Experimental swap partition

(1) Create a partitioned table and insert data

#  Create 1 A   Partition table e
CREATE TABLE e (
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30)
)
  PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (50),
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (150),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

#  To partition table e Insert several pieces of data in 
INSERT INTO e VALUES
  (1669, "Jim", "Smith"),
  (337, "Mary", "Jones"),
  (16, "Frank", "White"),
  (2005, "Linda", "Black");

(2) Create a common table e2 with partition table e structure 1

#  Create 1 Table e2
mysql> create table e2 like e;

#  Delete table e2 To make it a partition of 1 Ordinary table 
mysql> alter table e2 remove partitioning;

(3) See how many rows are in each partition of the table e

mysql> select PARTITION_NAME, TABLE_ROWS
  ->   FROM INFORMATION_SCHEMA.PARTITIONS
  ->   WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0       |     1 |
| p1       |     0 |
| p2       |     0 |
| p3       |     3 |
+----------------+------------+
4 rows in set (0.00 sec)

(4) Switch partition p0 of table e to normal table e2

mysql> alter table e exchange partition p0 with table e2;

(5) View the results

#  Table e Medium   Partition P0 The data of is no longer available 
mysql> SELECT PARTITION_NAME, TABLE_ROWS
  ->   FROM INFORMATION_SCHEMA.PARTITIONS
  ->   WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0       |     0 |
| p1       |     0 |
| p2       |     0 |
| p3       |     3 |
+----------------+------------+
4 rows in set (0.00 sec)

#  In the table e2 Have it in  1 Bar from table e Partition of p0  Data of 
mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

It is found that the data of partition p0 of table e is moved to table e2.

In the above example, a partition is exchanged with a common empty table, which is equivalent to moving the data of a partition, which is usually used in data archiving.

Similarly, swap partitions can be swapped between one partition and one non-empty normal table, so that the data in the normal table is moved to the specified partition, and the data in the specified partition is moved to the normal table. Do the following:

(6) Add another piece of data to p0 of partition table e

#  In the table e Add to 1 Bar in partition p0 Data in range 
mysql> INSERT INTO e VALUES (41, "Michael", "Green");      
Query OK, 1 row affected (0.05 sec)               

#  Verify that the inserted data is stored in the partition p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS
  ->   FROM INFORMATION_SCHEMA.PARTITIONS
  ->   WHERE TABLE_NAME = 'e';      
+----------------+------------+       
| PARTITION_NAME | TABLE_ROWS |       
+----------------+------------+       
| p0       |     1 |       
| p1       |     0 |       
| p2       |     0 |       
| p3       |     3 |       
+----------------+------------+       
4 rows in set (0.00 sec)

(7) Execute swap partitioning

​mysql> alter table e exchange partition p0 with table e2;
Query OK, 0 rows affected (0.28 sec)

(8) View the results

#  Put the watch before e Adj. p0 Switch to table e2 Adj. 1 Data, now back to the table e
mysql> SELECT * FROM e;
+------+-------+-------+
| id  | fname | lname |
+------+-------+-------+
|  16 | Frank | White |   
| 1669 | Jim  | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname  | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

It can be seen that the data of p0 in table e and e2 are interchanged! This scenario can be used when ordinary table data is moved to a partition of a partitioned table.

However, there is one point to note:

If the data of the common table is not in the range of the partition to be exchanged, the syntax of the partition exchanged above cannot be successfully executed! Please look at the following operation.

(9) Insert one piece of data beyond the range of p0 of e into the common table e2, and execute the above exchange statement

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> alter table e exchange partition p0 with table e2;
ERROR 1707 (HY000): Found row that does not match the partition

In MySQL version 5.6, this problem cannot be solved; In MySQL 5.7. 5, an option WITHOUT VALIDATION is added to solve the above error report. Do the following:

#  Create 1 A   Partition table e
CREATE TABLE e (
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30)
)
  PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (50),
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (150),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

#  To partition table e Insert several pieces of data in 
INSERT INTO e VALUES
  (1669, "Jim", "Smith"),
  (337, "Mary", "Jones"),
  (16, "Frank", "White"),
  (2005, "Linda", "Black");

0

View results:

#  Create 1 A   Partition table e
CREATE TABLE e (
  id INT NOT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30)
)
  PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (50),
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (150),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

#  To partition table e Insert several pieces of data in 
INSERT INTO e VALUES
  (1669, "Jim", "Smith"),
  (337, "Mary", "Jones"),
  (16, "Frank", "White"),
  (2005, "Linda", "Black");

1

Summarize

Through the above practical operation, it is found that the swap partition function of MySQL has two applicable scenarios:

A partition of the partition table is exchanged with an empty ordinary table, so that the data of the partition of the partition table is moved out.
A partition of the partition table is exchanged with a non-empty ordinary table, so that the data of the ordinary table is moved to the specified partition of the partition table.

Thank you for reading, hope to help everyone, thank you for your support to this site!