//给表添加按时间分区功能(添加之前必须给分区的字段加上主键)
ALTER TABLE `records`
PARTITION BY RANGE((YEAR(`visit_time`)*100+MONTH(`visit_time`))*100+DAY(`visit_time`))
(
PARTITION p20161114 VALUES LESS THAN (20161115),
PARTITION p20161115 VALUES LESS THAN (20161116)
)
//给表添加分区
ALTER TABLE `records` ADD PARTITION (PARTITION p20161116 VALUES LESS THAN (20161117));
ALTER TABLE `records` ADD PARTITION (PARTITION p20161117 VALUES LESS THAN (20161118));
//查看分区表的数据总数
SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
//删除表里面的分区,不能删除所有的分区
ALTER TABLE `records` DROP PARTITION p20161118;
//查看表某分区的数据
SELECT * FROM `records` PARTITION (p20161117);
搞了一天,终于搞好了!