• 售前

  • 售后

热门帖子
入门百科

MySQL互换分区的实例详解

[复制链接]
梦的衣裳323 显示全部楼层 发表于 2021-10-25 19:41:42 |阅读模式 打印 上一主题 下一主题
MySQL交换分区的实例详解
前言
在介绍交换分区之前,我们先相识一下 mysql 分区。
数据库的分区有两种:程度分区和垂直分区。而MySQL暂时不支持垂直分区,因此接下来说的都是程度分区。程度分区即:以行为单位对表举行分区。比如:按照时间分区,每一年一个分区等。
在MySQL中,分区是可以交换的,可以将一个分区表中的一个分区和一个平凡表中的数据互换。
交换分区的实现
1、交换分区的语法
  1. alter table pt exchange partition p with table nt;
复制代码
解释:
将 分区表pt 的 分区p 和 一个平凡表nt 中的数据 举行互换。
交换的条件条件:
      
  • 平凡表nt 不为暂时表,且不是分区表。  
  • 表结构和分区表pt的结构一致。  
  • 平凡表nt 没有外键引用。  
  • 普用表nt 若为非空。在MySQL5.6以及之前的版本,必须在分区的范围内;在MySQL5.7之后则可以不再其分区范围内,也依然会成功存入该分区中。
2、实验 交换分区
(1) 创建一个分区表,并插入数据
  1. # 创建一个 分区表e
  2. CREATE TABLE e (
  3.   id INT NOT NULL,
  4.   fname VARCHAR(30),
  5.   lname VARCHAR(30)
  6. )
  7.   PARTITION BY RANGE (id) (
  8.     PARTITION p0 VALUES LESS THAN (50),
  9.     PARTITION p1 VALUES LESS THAN (100),
  10.     PARTITION p2 VALUES LESS THAN (150),
  11.     PARTITION p3 VALUES LESS THAN (MAXVALUE)
  12. );
  13. # 向分区表e中插入几条数据
  14. INSERT INTO e VALUES
  15.   (1669, "Jim", "Smith"),
  16.   (337, "Mary", "Jones"),
  17.   (16, "Frank", "White"),
  18.   (2005, "Linda", "Black");
复制代码
(2) 创建一个和分区表e结构一致的平凡表e2
  1. # 创建一个表e2
  2. mysql> create table e2 like e;
  3. # 删除表e2的分区,使之成为一个普通表
  4. mysql> alter table e2 remove partitioning;
复制代码
(3) 查看表e的分区中各有多少行
  1. mysql> select PARTITION_NAME, TABLE_ROWS
  2.   ->   FROM INFORMATION_SCHEMA.PARTITIONS
  3.   ->   WHERE TABLE_NAME = 'e';
  4. +----------------+------------+
  5. | PARTITION_NAME | TABLE_ROWS |
  6. +----------------+------------+
  7. | p0       |     1 |
  8. | p1       |     0 |
  9. | p2       |     0 |
  10. | p3       |     3 |
  11. +----------------+------------+
  12. 4 rows in set (0.00 sec)
复制代码
(4) 将表e的分区p0 交换到 平凡表e2中
  1. mysql> alter table e exchange partition p0 with table e2;
复制代码
(5) 查看效果
  1. # 表e中 分区P0的数据已经没有了
  2. mysql> SELECT PARTITION_NAME, TABLE_ROWS
  3.   ->   FROM INFORMATION_SCHEMA.PARTITIONS
  4.   ->   WHERE TABLE_NAME = 'e';
  5. +----------------+------------+
  6. | PARTITION_NAME | TABLE_ROWS |
  7. +----------------+------------+
  8. | p0       |     0 |
  9. | p1       |     0 |
  10. | p2       |     0 |
  11. | p3       |     3 |
  12. +----------------+------------+
  13. 4 rows in set (0.00 sec)
  14. # 在表e2中有了 一条来自表e的分区p0 的数据
  15. mysql> SELECT * FROM e2;
  16. +----+-------+-------+
  17. | id | fname | lname |
  18. +----+-------+-------+
  19. | 16 | Frank | White |
  20. +----+-------+-------+
  21. 1 row in set (0.00 sec)
复制代码
由此发现,表e的分区p0的数据 搬移到了 表e2中。
上面的例子,是将一个分区 和 一个平凡的空表 交换,如许相称于将一个分区的数据搬移了出去,通常使用在数据的归档中。
同样,交换分区也可以 在一个分区和一个非空平凡表中 发生交换,如许平凡表中的数据会搬迁到指定分区,该指定分区的数据会搬迁到平凡表中。如下操纵:
(6) 再向分区表e的p0中添加一条数据
  1. # 在表e中添加一条在分区p0范围内的数据
  2. mysql> INSERT INTO e VALUES (41, "Michael", "Green");      
  3. Query OK, 1 row affected (0.05 sec)               
  4. # 确认插入的数据确实以存放在分区p0
  5. mysql> SELECT PARTITION_NAME, TABLE_ROWS
  6.   ->   FROM INFORMATION_SCHEMA.PARTITIONS
  7.   ->   WHERE TABLE_NAME = 'e';      
  8. +----------------+------------+      
  9. | PARTITION_NAME | TABLE_ROWS |      
  10. +----------------+------------+      
  11. | p0       |     1 |      
  12. | p1       |     0 |      
  13. | p2       |     0 |      
  14. | p3       |     3 |      
  15. +----------------+------------+      
  16. 4 rows in set (0.00 sec)
复制代码
(7) 执行 交换分区
  1. ​mysql> alter table e exchange partition p0 with table e2;Query OK, 0 rows affected (0.28 sec)
复制代码
(8) 查看效果
  1. # 之前把表e的p0交换到表e2的一条数据,现在又回到了表e
  2. mysql> SELECT * FROM e;
  3. +------+-------+-------+
  4. | id  | fname | lname |
  5. +------+-------+-------+
  6. |  16 | Frank | White |   
  7. | 1669 | Jim  | Smith |
  8. | 337 | Mary | Jones |
  9. | 2005 | Linda | Black |
  10. +------+-------+-------+
  11. 4 rows in set (0.00 sec)
  12. mysql> SELECT * FROM e2;
  13. +----+---------+-------+
  14. | id | fname  | lname |
  15. +----+---------+-------+
  16. | 41 | Michael | Green |
  17. +----+---------+-------+
  18. 1 row in set (0.00 sec)
复制代码
由此看出,表e的p0 和 表e2 的数据做了互换!这种场景,可以在平凡表数据搬移到分区表的某个分区中使用
但是,有一点需要留意:
若平凡表的数据 不在 要交换的分区范围内,上面交换分区的语法,不可以执行成功!请看下面的操纵。
(9) 在平凡表e2中插入一条超出表e的p0范围的数据,执行上面的交换语句
  1. 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
复制代码
在MySQL 5.6 版本中,该题目无法解决;而在MySQL 5.7.5版本中,增长了一个选项 WITHOUT VALIDATION ,可以解决上面的报错。操纵如下:
  1. mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
  2. Query OK, 0 rows affected (0.02 sec)
复制代码
查看效果:
  1. # id为51的数据也存放在表e的p0中
  2. mysql> select * from e partition(p0);
  3. +------+---------+----------+
  4. | id  | fname  | lname  |
  5. +------+---------+----------+
  6. |  41 | Michael | Green  |
  7. |  51 | Ellen  | McDonald |
  8. +------+---------+----------+
  9. mysql> SELECT * FROM e2;
  10. +----+---------+-------+
  11. | id | fname  | lname |
  12. +----+---------+-------+
  13. | 16 | Frank  | White |
  14. +----+---------+-------+
  15. 1 row in set (0.00 sec)
复制代码
总结
通过上述实践操纵发现,MySQL 的交换分区的功能,有两种实用场景:
分区表的某个分区 和 一个空的平凡表 交换,使得 分区表的该分区的数据搬移出去。
分区表的某个分区 和 一个非空平凡表 交换,使得 平凡表的数据搬移到分区表的指定分区中。
感谢阅读,盼望能资助到各人,谢谢各人对本站的支持!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作