Mysql分区表相信大家都有一些了解,下面就为您介绍Mysql分区表对于唯一约束的局限性,如果您对此方面感兴趣的话源码天空,不妨一看。
Mysql分区表关于Partitioning Keys, Primary Keys, and Unique Keys的限制
在5.1中Mysql分区表对唯一约束有明确的规定,每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。
这句话也许不好理解,我们做几个实验:
CREATE TABLE t1
( id INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN(10) ENGINE = INNODB);
CREATE TABLE t1
( id INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp',
PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY='/tmp' INDEX DIRECTORY='/tmp');
mysql> CREATE TABLE t1
-> ( id INT NOT NULL,
-> uid INT NOT NULL,
-> PRIMARY KEY (id),
-> UNIQUE KEY (uid)
-> )
-> PARTITION BY RANGE (id)
-> (PARTITION p0 VALUES LESS THAN(5),
-> PARTITION p1 VALUES LESS THAN(10)
-> );ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
--转自