创建分区

//给一个表创建分区
 CREATE TABLE IF NOT EXISTS `test1` (
  id int(5) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  sex int(5) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
partition by range(id)( 
  partition p0 values less than(2),
  partition p1 values less than(4),
  partition p2 values less than(6),
  partition p3 values less than maxvalue
);



//修改一个表的分区
alter table tbl_main_book
partition by range(id)( 
  partition p1 values less than(100000),
  partition p2 values less than(200000),
  partition p3 values less than(300000),
  partition p4 values less than(400000),
  partition p5 values less than(500000),
  partition p6 values less than(600000),
  partition p7 values less than(700000),
  partition p8 values less than(800000),
  partition p9 values less than(900000),
  partition pmax values less than maxvalue
);


删除表的所有分区:
//mysql5.6可用
Alter table tbl_main_book remove partitioning;--不会丢失数据



查看该表的分区信息:
Select partition_name part, partition_expression expr, partition_description descr,table_rows from information_schema.partitions where table_name='tbl_main_book';

//从mysql8.0开始 remove partitioning不再可用 
使用会出现这样的报错:
Partition management on a not partitioned table is not possible
//只能使用一个取巧的方法,新建一个表,结构一样,分区写好
//然后将旧表数据导入新表
insert into tbl_comic_version_part_page2 select * from tbl_comic_version_part_page where comic_id >= 0 and comic_id <10000;
//数据导入完成之后, 将旧表名改一下,改成别的, 将新表改成旧表名
rename table  tbl_comic_version_part_page to `tbl_comic_version_part_page_bak`
rename table  tbl_comic_version_part_page2 to `tbl_comic_version_part_page`