• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MySQL交换分区的实例详解

    MySQL交换分区的实例详解

    前言

    在介绍交换分区之前,我们先了解一下 mysql 分区。

    数据库的分区有两种:水平分区和垂直分区。而MySQL暂时不支持垂直分区,因此接下来说的都是水平分区。水平分区即:以行为单位对表进行分区。比如:按照时间分区,每一年一个分区等。

    在MySQL中,分区是可以交换的,可以将一个分区表中的一个分区和一个普通表中的数据互换。

    交换分区的实现

    1、交换分区的语法

    alter table pt exchange partition p with table nt;

    解释:

    将 分区表pt 的 分区p 和 一个普通表nt 中的数据 进行互换。

    交换的前提条件:

    2、实验 交换分区

    (1) 创建一个分区表,并插入数据

    # 创建一个 分区表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)
    );
    
    # 向分区表e中插入几条数据
    INSERT INTO e VALUES
      (1669, "Jim", "Smith"),
      (337, "Mary", "Jones"),
      (16, "Frank", "White"),
      (2005, "Linda", "Black");
    
    

     (2) 创建一个和分区表e结构一致的普通表e2

    # 创建一个表e2
    mysql> create table e2 like e;
    
    # 删除表e2的分区,使之成为一个普通表
    mysql> alter table e2 remove partitioning;
    
    

    (3) 查看表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) 将表e的分区p0 交换到 普通表e2中

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

    (5) 查看结果

    # 表e中 分区P0的数据已经没有了
    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)
    
    # 在表e2中有了 一条来自表e的分区p0 的数据
    mysql> SELECT * FROM e2;
    +----+-------+-------+
    | id | fname | lname |
    +----+-------+-------+
    | 16 | Frank | White |
    +----+-------+-------+
    1 row in set (0.00 sec)
    
    

    由此发现,表e的分区p0的数据 搬移到了 表e2中。

    上面的例子,是将一个分区 和 一个普通的空表 交换,这样相当于将一个分区的数据搬移了出去,通常使用在数据的归档中。

    同样,交换分区也可以 在一个分区和一个非空普通表中 发生交换,这样普通表中的数据会搬迁到指定分区,该指定分区的数据会搬迁到普通表中。如下操作:

    (6) 再向分区表e的p0中添加一条数据

    # 在表e中添加一条在分区p0范围内的数据
    mysql> INSERT INTO e VALUES (41, "Michael", "Green");      
    Query OK, 1 row affected (0.05 sec)               
    
    # 确认插入的数据确实以存放在分区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) 执行 交换分区

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

    (8) 查看结果

    # 之前把表e的p0交换到表e2的一条数据,现在又回到了表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)
    
    

    由此看出,表e的p0 和 表e2 的数据做了互换!这种场景,可以在普通表数据搬移到分区表的某个分区中使用

    但是,有一点需要注意:

    若普通表的数据 不在 要交换的分区范围内,上面交换分区的语法,不可以执行成功!请看下面的操作。

    (9) 在普通表e2中插入一条超出表e的p0范围的数据,执行上面的交换语句

    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 ,可以解决上面的报错。操作如下:

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

    查看结果:

    # id为51的数据也存放在表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)
    
    

     总结

    通过上述实践操作发现,MySQL 的交换分区的功能,有两种适用场景:

    分区表的某个分区 和 一个空的普通表 交换,使得 分区表的该分区的数据搬移出去。
    分区表的某个分区 和 一个非空普通表 交换,使得 普通表的数据搬移到分区表的指定分区中。

    感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

    您可能感兴趣的文章:
    • Linux下刚安装完mysql修改密码的简单方法
    • CentOS 6.6 源码编译安装MySQL 5.7.18教程详解
    • MySql Error 1698(28000)问题的解决方法
    • CentOS 7中MySQL连接数被限制为214个的解决方法
    • 通过案例分析MySQL中令人头疼的Aborted告警
    • 解决MySQL中IN子查询会导致无法使用索引问题
    上一篇:CentOS 6.6 源码编译安装MySQL 5.7.18教程详解
    下一篇:MySQL去重的方法整理
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MySQL交换分区的实例详解 MySQL,交换,分区,的,实例,