之前有个表分区添加时s201607添加成s201617，所以在查询7月份数据时报错

错误的

alter table statistics_ticket add partition (partition s201617 values less than (201607));

正确的

alter table statistics_ticket add partition (partition s201607 values less than (201607));

因为range的分区没法直接修改，只能先删除最后分区至要修改的分区，在重新添加分区，再添加数据。

具体操作步骤：

1）完整表备份

mysqldump -u root -p'***' -t databasename tablename > /home/statistics_ticket_20160819_1800.sql &

2）7、8月表数据备份不导出结构

mysqldump -u root -p'***' -t databasename tablename --where="FLIGHT_DATE between '2016-07-01' and '2016-08-31'" > /home/statistics_ticket_20160819_0708_data_1800.sql &

在备机上进行完整备份

/home/backup/script/basebackup.bash

3）先统计表有多少行

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-07-31';

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-08-31';

4）删除分区

测试

alter table statistics_ticket drop partition s201608;

alter table statistics_ticket drop partition s201609;

alter table statistics_ticket drop partition s201610;

alter table statistics_ticket drop partition s201611;

alter table statistics_ticket drop partition s201612;

alter table statistics_ticket drop partition s201701;

alter table statistics_ticket drop partition s201702;

alter table statistics_ticket drop partition s201703;

alter table statistics_ticket drop partition s201704;

alter table statistics_ticket drop partition s201705;

alter table statistics_ticket drop partition s201706;

在查看分区添加是否成功

show create table statistics_ticket\G;

5）添加分区

alter table statistics_ticket add partition (partition s201607 values less than (201607));

alter table statistics_ticket add partition (partition s201608 values less than (201608));

alter table statistics_ticket add partition (partition s201609 values less than (201609));

alter table statistics_ticket add partition (partition s201610 values less than (201610));

alter table statistics_ticket add partition (partition s201611 values less than (201611));

alter table statistics_ticket add partition (partition s201612 values less than (201612));

alter table statistics_ticket add partition (partition s201701 values less than (201701));

alter table statistics_ticket add partition (partition s201702 values less than (201702));

alter table statistics_ticket add partition (partition s201703 values less than (201703));

alter table statistics_ticket add partition (partition s201704 values less than (201704));

alter table statistics_ticket add partition (partition s201705 values less than (201705));

alter table statistics_ticket add partition (partition s201706 values less than (201706));

在查看分区添加是否成功

show create table statistics_ticket\G;

导入数据

source /home/bak.sql

看统计表有多少行是否和之前的一致

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-07-31';

select count(*) from statistics_ticket where FLIGHT_DATE between '2016-07-01' and '2016-08-31';