在创建分区表或者将普通表转换为分区表时,报ERROR 1503错误,会导致分区表创建不成功或者是普通表无法转换为分区表。本文介绍遇到这些情况该如何解决。
一、问题描述
在对表进行分区时,如果分区字段没有包含在主键字段内,如表A的主键为ID,分区字段为createtime ,按时间范围分区,代码如下:
CREATE TABLE TEST (
id int(8) NOT NULL AUTO_INCREMENT,
createtime datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p19 VALUES LESS ThAN MAXVALUE);
在创建该表时就会报如下错误:
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
这个错误通常是因为没有在分区函数中设置主键,可以通过设置一个新的主键来解决。
MySQL主键的限制,每一个分区表中的分区字段的列,必须在主键/unique key 中包括。
二、问题分析
在MYSQL的官方文档里是这么说明的
18.5.1. Partitioning Keys, Primary Keys, and Unique Keys
This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
In other words,every unique key on the table must use every columnin the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:
在MySQL中,分区键必须是表中的主键或唯一键或主键的一部分或唯一键的一部分。如果在新建分区时使用了非主键或非唯一键作为分区键或非主键的一部分或非唯一键的一部分,就会导致报错。因此,在新建分区时需要确保使用正确的分区键。
三、问题处理
顺应MYSQL的要求,就把分区字段加入到主键中,组成复合主键
CREATE TABLE TEST (
id int(8) NOT NULL AUTO_INCREMENT,
createtime datetime NOT NULL,
PRIMARY KEY (id,createtime)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p19 VALUES LESS ThAN MAXVALUE);
如果是普通表转为分区报该错误,需先做主键的调整,调整语句如下:
ALTER TABLE tableName DROP PRIMARY KEY , ADD PRIMARY KEY (partition_column,PK_column);
其中,partition_column是用于分区的列,PK_column是之前的主键列。
完成后再对表进行分区;