从MySQL 5.6开始,支持分区交换。就是将一个分区表中的一个分区和一个普通表中的数据互换。一、实现交换分区的基本语法:ALTER TABLEptEXCHANGE PARTITIONpWITH TABLEnt二、实现分区交换需满足以下前提条件:1. 表nt不能为分区表,也不能是临时表2. 表nt的表结构必须于分区表pt一致3. 表nt不包含外键引用,其他表也没有任何外键引用表nt4. 表nt中所有行都必须在p分区范围内,否则交换分区失败。MySQL 5.7.5开始,可使用WITHOUT VALIDATION选项,强制交换。5. 对于InnoDB表,pt和nt两个表必须使用相同的行格式。三、使用交换分区的注意事项:1. 使用该语句时,不会触发交换表和被交换表上的触发器2. auto_increment列将被重置3. IGNORE关键字在alter table ... exchange partition中不起作用四、案例模拟:根据官网提供的案例,在自己的机器上简单的实现下:1. 新建分区表e,并插入4行记录
2. 创建与表e结构一致的表e2,并移除分区。
remove partitioning这个语法真的第一次见,神奇。。简单测了一下,不管表中有没有数据都是可以remove的。
3. 查看表e的分区中各有多少行
4.将表e的分区p0 交换到表e2中
5.再观察下表e和表2的数据
数据交换成功~当然,要与分区表交换的普通表,不一定必须是空的。继续实验,在表e的p0分区插入一行数据
将表e的分区p0 交换到表e2中,然后检查数据
数据也交换成功~如果表e2中的数据不在表e的p0分区范围内,那还能交换吗?
前面使用交换分区必须满足的前提条件中的第4点,其实已经讲到了这个问题。可以通过使用WITHOUT VALIDATION选项,不需要逐行验证,能够强制交换。当表e2中有很多数据需要交换时,加上这个参数可以避免耗时的验证,节省大量时间。
现在 (51, "Ellen", "McDonald") 这行记录落在p0分区了。
可以通过REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION来修复分区。
以上就是简单的交换分区的方法,MySQL还提供了自分区的交换功能,我就不再细说了,有需要可以去官方文档上看下语法。总结:1. 可以将分区表的某个分区和一个空表进行分区交换,已达到将指定分区的数据迁移出去的目的。MySQL中应该没有单独对分区备份的功能,通过交换分区的方法,能达到快速清理分区及备份的效果。2. 可以将分区表的某个分区和一个非空表进行数据交换,可以把数据快速迁移到指定分区中。每天学习一点点点点,大家周末愉快~