1.RANGE分区:
CREATE TABLE apps(
id int PRIMARY KEY AUTO_INCREMENT,
trackName varchar(30) NOT NULL,
seoName varchar(30) NOT NULL,
des varchar(100) NOT NULL
)engine=innodb default charset=utf8
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN(3),
PARTITION p1 VALUES LESS THAN(6),
PARTITION p2 VALUES LESS THAN(9),
PARTITION p3 VALUES LESS THAN(12)
);
2.LIST分区
CREATE TABLE apps(
id int PRIMARY KEY AUTO_INCREMENT,
trackName varchar(30) NOT NULL,
seoName varchar(30) NOT NULL,
des varchar(100) NOT NULL
)engine=innodb default charset=utf8
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN(3),
PARTITION p1 VALUES LESS THAN(6),
PARTITION p2 VALUES LESS THAN(9),
PARTITION p3 VALUES LESS THAN(12)
);
3.HASH分区
CREATE TABLE apps(
id int PRIMARY KEY AUTO_INCREMENT,
trackName varchar(30) NOT NULL,
seoName varchar(30) NOT NULL,
buyDate DATE NOT NULL,
des varchar(100) NOT NULL
)engine=innodb AUTO_INCREMENT=1 default charset=utf8
PARTITION BY HASH(YEAR(buyDate))
PARTITIONS 10;
hash分区会报错“1503:A PRIMARY KEY must include all columns in the table's partitioning function”,意思是分区字段必须包含在主键字段内;
解决方案:创建表的时候先不指定主键,创建表之后再把分区字段和id指定成复合主键:
1.创建没有主键的表:
CREATE TABLE apps(
id int NOT NULL ,
trackName varchar(30) NOT NULL,
seoName varchar(30) NOT NULL,
buyDate DATE NOT NULL,
des varchar(100) NOT NULL
)engine=innodb AUTO_INCREMENT=1 default charset=utf8
PARTITION BY HASH(YEAR(buyDate))
PARTITIONS 10;
2.添加上符合主键:
alert table apps add PAIMARY KEY(id,buyDate);