大家好，我是讯享网，很高兴认识大家。

参考：https://bugs.mysql.com/bug.php?id=52815

简而言之就是你建的表分区最大值不够用了

讯享网

如果表有主键，那么主键必须包含分区键，分区键是主键的子集。这个应该算是局限了。

这个是正确的

CREATE TABLE IF NOT EXISTS `test_hash_part41` (

`id` int(11) NOT NULL ,

`pid` int(11) NOT NULL ,

`comment` varchar(1000) NOT NULL DEFAULT '' ,

`ip` varchar(25) NOT NULL DEFAULT '' ,

PRIMARY KEY (`id`,pid)

) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY KEY(id)

PARTITIONS 3;

下面这个会ERROR 1503(HY000):A PRIMARY KEY must include all columns inthe table's partitioning function

CREATE TABLE IF NOT EXISTS `test_hash_part42` (

`id` int(11) NOT NULL ,

`pid` int(11) NOT NULL ,

`comment` varchar(1000) NOT NULL DEFAULT '' ,

`ip` varchar(25) NOT NULL DEFAULT '' ,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY KEY(id,pid)

PARTITIONS 3;

分区的sql语法

partition_options:

PARTITION BY

{ [LINEAR] HASH(expr)

| [LINEAR] KEY(column_list)

| RANGE(expr)

| LIST(expr) }

[PARTITIONS num]

[SUBPARTITION BY

{ [LINEAR] HASH(expr)

| [LINEAR] KEY(column_list) }

[SUBPARTITIONS num]

]

[(partition_definition [, partition_definition] ...)]

partition_definition:

PARTITION partition_name

[VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]

[[STORAGE] ENGINE [=] engine_name]

[COMMENT [=] 'comment_text' ]

[DATA DIRECTORY [=] 'data_dir']

[INDEX DIRECTORY [=] 'index_dir']

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

[TABLESPACE [=] tablespace_name]

[NODEGROUP [=] node_group_id]

[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:

SUBPARTITION logical_name

[[STORAGE] ENGINE [=] engine_name]

[COMMENT [=] 'comment_text' ]

[DATA DIRECTORY [=] 'data_dir']

[INDEX DIRECTORY [=] 'index_dir']

[MAX_ROWS [=] max_number_of_rows]

[MIN_ROWS [=] min_number_of_rows]

[TABLESPACE [=] tablespace_name]

[NODEGROUP [=] node_group_id]

例子：

创建range分区

CREATE TABLE `testpartition` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`name` varchar(50) NOT NULL ,

`sex` int(1) NOT NULL DEFAULT '0' ,

PRIMARY KEY (`id`)

) 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),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

插入数据

INSERT INTO testpartition (`name` ,`sex`)VALUES ('jhh', '0')

,('zhang',1),('ying',1),('aaaa',1),('bbbb',0),('test1',1),('jhh2',1)

,('jhh1',1),('test2',1),('test3',1),('test4',1),('test5',1),('jhh3',1)

,('jhh4',1),('jhh5',1),('jhh6',1),('jhh7',1),('jhh8',1),('jhh9',1)

,('jhh10',1),('jhh11',1),('jhh12',1),('jhh13',1),('jhh21',1),('jhh42',1);

可以drop 某个分区alter table drop partition p4 ;

如果是删除了最大的分区p4，导致比较大的数值没有相应的分区，会报如下类似错误的。ERROR1526(HY000):Tablehasnopartitionforvalue.

改变分区，相当于重整分区了。需要生成临时表，并且加锁。

alter table testpartition partition by RANGE(id)

(PARTITION p1 VALUES less than (6),

PARTITION p2 VALUES less than (16),

PARTITION p3 VALUES less than MAXVALUE);