Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to exchange zones in MySQL. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Implementation of switching Partition

1. Syntax of swap partition

Alter table pt exchange partition p with table nt

Explanation:

Swap the partition p of the partition table pt with the data in a normal table nt.

Prerequisites for exchange:

The normal table nt is not a temporary table and is not a partitioned table.

The structure of the table is the same as that of the partitioned table pt.

The normal table nt has no foreign key references.

If the universal table nt is not empty. In MySQL5.6 and previous versions, it must be within the scope of the partition; after MySQL5.7, it can no longer be within the scope of its partition and will still be successfully stored in that partition.

2. Experimental switching zone

(1) create a partition table and insert data

# create 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)) # insert several data INSERT INTO e VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black") into partition table e

(2) create a general table e2 which is consistent with the structure of partition table e

# create a table e2 mysql > create table e2 like e; # delete the partition of table e2 to make it a regular table mysql > alter table e2 remove partitioning

(3) check how many rows are in the partition of 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) swap the partition p0 of table e to ordinary table e2

Mysql > alter table e exchange partition p0 with table e2

(5) View the results

# No mysql > SELECT PARTITION_NAME, TABLE_ROWS-> FROM INFORMATION_SCHEMA.PARTITIONS-> WHERE TABLE_NAME ='e' for partition P0 in table e +-+-+ | PARTITION_NAME | TABLE_ROWS | +-+-+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | | 3 | +-+-+ 4 rows in set (0.00 sec) # there is a data mysql > SELECT * FROM e2 from the partition p0 of table e in table e2. | +-+ | id | fname | lname | +-+ | 16 | Frank | White | +-+ 1 row in set (0.00 sec)

Thus, it is found that the data of partition p0 of table e is moved to table e2.

The above example is to swap a partition with a normal empty table, which is equivalent to moving a partition's data out, which is usually used in data archiving.

Similarly, swap partitions can also be exchanged in a partition and a non-empty normal table, so that the data in the regular table is moved to the specified partition, and the data in that specified partition is moved to the regular table. Do the following:

(6) add a piece of data to the p0 of partition table e

# add a data item in the range of partition p0 in table e mysql > INSERT INTO e VALUES (41, "Michael", "Green"); Query OK, 1 row affected (0.05sec) # confirm that the inserted data is indeed stored in 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) perform swap partition

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

(8) View the results

# previously exchanged the p0 of table e with a piece of data of table e2, but now it goes back to 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)

From this, we can see that the data of table e p0 and table e2 have been exchanged! This scenario can be used when plain table data is moved to a partition of a partitioned table.

However, there is one thing to note:

If the data of the ordinary table is not within the scope of the partition to be exchanged, the syntax of the above exchange partition cannot be executed successfully! Please see the operation below.

(9) insert a piece of data beyond the p0 range of table e into 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 5.6, this issue cannot be resolved; in MySQL 5.7.5, an option WITHOUT VALIDATION has been added to resolve the above error. Do the following:

Mysql > ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION; Query OK, 0 rows affected (0.02 sec)

View the results:

# data with an id of 51 is also stored in table e p0 mysql > select * from e partition (p0) +-+ | id | fname | lname | +-+ | 41 | Michael | Green | | 51 | Ellen | McDonald | +-+ mysql > SELECT * FROM e2 +-+ | id | fname | lname | +-+ | 16 | Frank | White | +-+ 1 row in set (0.00 sec)

Summary

Through the above practice, it is found that there are two applicable scenarios for the swap partition function of MySQL:

A partition of a partitioned table is swapped with an empty regular table, so that the data of the partitioned table is moved out.

A partition of a partitioned 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 partitioned table.

About how to exchange partitions in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.