想好再说

/*

交换分区原理：

将指定分区的数据剪切到非分区表，再将非分区表原有的数据剪切到分区表，从而达到数据交换的目的。

*/

-- 1.创建分区表

CREATE TABLE `employee_clock_time` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',

  `emp_no` varchar(10) NOT NULL COMMENT '编号',

  `emp_name` varchar(10) DEFAULT NULL COMMENT '姓名',

  `clock_time` datetime NOT NULL COMMENT '打卡时间',

  PRIMARY KEY (`id`,`clock_time`)

) ENGINE=MyISAM AUTO_INCREMENT=48289611 DEFAULT CHARSET=utf8 COMMENT='打卡汇总数据'

/*!50100 PARTITION BY RANGE (TO_DAYS(`clock_time`))

(PARTITION part201704 VALUES LESS THAN (736815) ENGINE = MyISAM,

-- PARTITION part201704 VALUES LESS THAN (TO_DAYS ('2017-04-01')) ENGINE = MyISAM,

 PARTITION part201705 VALUES LESS THAN (736846) ENGINE = MyISAM,

 PARTITION part201706 VALUES LESS THAN (736876) ENGINE = MyISAM,

 PARTITION part201707 VALUES LESS THAN (736907) ENGINE = MyISAM,

 PARTITION part201708 VALUES LESS THAN (736938) ENGINE = MyISAM,

 PARTITION part201709 VALUES LESS THAN (736968) ENGINE = MyISAM,

 PARTITION part201710 VALUES LESS THAN (736999) ENGINE = MyISAM,

 PARTITION part201711 VALUES LESS THAN (737029) ENGINE = MyISAM,

 PARTITION part201812 VALUES LESS THAN (737060) ENGINE = MyISAM,

 PARTITION part201801 VALUES LESS THAN (737091) ENGINE = MyISAM,

 PARTITION part201802 VALUES LESS THAN (737119) ENGINE = MyISAM,

 PARTITION part201803 VALUES LESS THAN (737150) ENGINE = MyISAM,

 PARTITION part201804 VALUES LESS THAN (737180) ENGINE = MyISAM,

 PARTITION part201805 VALUES LESS THAN (737211) ENGINE = MyISAM,

 PARTITION part201806 VALUES LESS THAN (737241) ENGINE = MyISAM,

 PARTITION part201807 VALUES LESS THAN (737272) ENGINE = MyISAM,

 PARTITION part201808 VALUES LESS THAN (737303) ENGINE = MyISAM,

 PARTITION part201809 VALUES LESS THAN (737333) ENGINE = MyISAM,

 PARTITION part201810 VALUES LESS THAN (737364) ENGINE = MyISAM,

 PARTITION part201811 VALUES LESS THAN (737394) ENGINE = MyISAM,

 PARTITION part201912 VALUES LESS THAN (737425) ENGINE = MyISAM,

 PARTITION part201901 VALUES LESS THAN (737456) ENGINE = MyISAM,

 PARTITION part201902 VALUES LESS THAN (737484) ENGINE = MyISAM,

 PARTITION part201903 VALUES LESS THAN (737515) ENGINE = MyISAM,

 PARTITION part201904 VALUES LESS THAN (737545) ENGINE = MyISAM,

 PARTITION part201905 VALUES LESS THAN (737576) ENGINE = MyISAM,

 PARTITION part201906 VALUES LESS THAN (737606) ENGINE = MyISAM,

 PARTITION part201907 VALUES LESS THAN (737637) ENGINE = MyISAM,

 PARTITION part201908 VALUES LESS THAN (737668) ENGINE = MyISAM,

 PARTITION part201909 VALUES LESS THAN (737698) ENGINE = MyISAM,

 PARTITION part201910 VALUES LESS THAN (737729) ENGINE = MyISAM,

 PARTITION part201911 VALUES LESS THAN (737759) ENGINE = MyISAM,

 PARTITION part202012 VALUES LESS THAN (737790) ENGINE = MyISAM,

 PARTITION part202001 VALUES LESS THAN (737821) ENGINE = MyISAM,

 PARTITION part202002 VALUES LESS THAN (737850) ENGINE = MyISAM,

 PARTITION part202003 VALUES LESS THAN (737881) ENGINE = MyISAM,

 PARTITION part202004 VALUES LESS THAN (737911) ENGINE = MyISAM,

 PARTITION part202005 VALUES LESS THAN (737942) ENGINE = MyISAM,

 PARTITION part202006 VALUES LESS THAN (737972) ENGINE = MyISAM,

 PARTITION part202007 VALUES LESS THAN (738003) ENGINE = MyISAM,

 PARTITION part202008 VALUES LESS THAN (738034) ENGINE = MyISAM,

 PARTITION part202009 VALUES LESS THAN (738064) ENGINE = MyISAM,

 PARTITION part202010 VALUES LESS THAN (738095) ENGINE = MyISAM,

 PARTITION part202011 VALUES LESS THAN (738125) ENGINE = MyISAM,

 PARTITION partMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

-- 2.创建分区表对应的历史表。历史表结构要和分区表一致，但不能有分区。

CREATE TABLE `employee_clock_time_hisitory` like employee_clock_time;

-- 历史表去掉分区

alter TABLE `employee_clock_time_hisitory` remove partitioning;

-- 3.分区表初始化数据

insert employee_clock_time ( emp_no, emp_name, clock_time)

values

( '1','1','2018-3-1 9:00:00'),

( '2','2','2018-3-2 9:00:00'),

( '3','3','2018-3-3 9:00:00'),

( '4','4','2018-4-1 9:00:00'),

( '5','5','2018-5-1 9:00:00'),

( '6','6','2018-6-1 9:00:00'),

( '7','7','2018-7-1 9:00:00'),

( '8','8','2018-8-1 9:00:00'),

( '9','9','2018-9-1 9:00:00'),

( '10','10','2018-10-1 9:00:00'),

( '11','11','2018-10-2 9:00:00');

select * from employee_clock_time;

-- 4.将2018年10月的数据放入历史表。 如果历史表有分区范围之外的数据，就得加上without VALIDATION，不然会报错。

alter table employee_clock_time exchange partition part201810 with table employee_clock_time_hisitory ;

-- alter table employee_clock_time exchange partition part201810 with table employee_clock_time_hisitory without VALIDATION;

select * from employee_clock_time;

select * from employee_clock_time_hisitory;

-- 5.分区表插入新数据。注意新增的两笔记录，有一笔是已经切换过的2018年10月范围的数据，自增ID是以表中现有的最大ID为基数自增长的。

insert employee_clock_time ( emp_no, emp_name, clock_time)

values

( '12','12','2018-10-3 9:00:00'),

( '13','13','2019-2-1 9:00:00');

select * from employee_clock_time;

select * from employee_clock_time_hisitory;

-- 6.将2018年4月历史数据交换到历史表。 

alter table employee_clock_time exchange partition part201804 with table employee_clock_time_hisitory without VALIDATION;

select * from employee_clock_time;

select * from employee_clock_time_hisitory;

-- 可以看出，发生了数据交换，历史表原来的两笔数据进入了分区表。

-- 7.分区表插入新数据

insert employee_clock_time ( emp_no, emp_name, clock_time)

values

('14','14','2019-2-2 9:00:00');

select * from employee_clock_time;

select * from employee_clock_time_hisitory;