mysql> CREATE TABLE test1 (
-> id VARCHAR(20) NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> submit_time DATETIME NOT NULL,
-> index time_index (submit_time),
-> primary key (id)
-> )ENGINE=MyISAM
-> PARTITION BY RANGE COLUMNS(submit_time)
-> (
-> PARTITION p1 VALUES LESS THAN ('2010-02-01'),
-> PARTITION p2 VALUES LESS THAN ('2010-03-01'),
-> PARTITION p3 VALUES LESS THAN ('2010-04-01'),
-> PARTITION p4 VALUES LESS THAN ('2010-05-01'),
-> PARTITION p5 VALUES LESS THAN ('2010-06-01'),
-> PARTITION p6 VALUES LESS THAN ('2010-07-01'),
-> PARTITION p7 VALUES LESS THAN ('2010-08-01'),
-> PARTITION p8 VALUES LESS THAN ('2010-09-01'),
-> PARTITION p9 VALUES LESS THAN ('2010-10-01'),
-> PARTITION p10 VALUES LESS THAN ('2010-11-01'),
-> PARTITION p11 VALUES LESS THAN ('2010-12-01')
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
我不解的是,为什么主键或唯一索引非要在分区的选项中...
能具体说明下。。是为什么原因吗?有相关的文章也可以。。
-> id VARCHAR(20) NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> submit_time DATETIME NOT NULL,
-> index time_index (submit_time),
-> primary key (id)
-> )ENGINE=MyISAM
-> PARTITION BY RANGE COLUMNS(submit_time)
-> (
-> PARTITION p1 VALUES LESS THAN ('2010-02-01'),
-> PARTITION p2 VALUES LESS THAN ('2010-03-01'),
-> PARTITION p3 VALUES LESS THAN ('2010-04-01'),
-> PARTITION p4 VALUES LESS THAN ('2010-05-01'),
-> PARTITION p5 VALUES LESS THAN ('2010-06-01'),
-> PARTITION p6 VALUES LESS THAN ('2010-07-01'),
-> PARTITION p7 VALUES LESS THAN ('2010-08-01'),
-> PARTITION p8 VALUES LESS THAN ('2010-09-01'),
-> PARTITION p9 VALUES LESS THAN ('2010-10-01'),
-> PARTITION p10 VALUES LESS THAN ('2010-11-01'),
-> PARTITION p11 VALUES LESS THAN ('2010-12-01')
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
我不解的是,为什么主键或唯一索引非要在分区的选项中...
能具体说明下。。是为什么原因吗?有相关的文章也可以。。
在5.1中分区表对唯一约束有明确的规定,每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。
1、看MYSQL源码;
2、官方说明http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
id VARCHAR(20) NOT NULL,
name VARCHAR(20) NOT NULL,
submit_time DATETIME NOT NULL,
index time_index (submit_time),
key (id)
)ENGINE=MyISAM
PARTITION BY RANGE COLUMNS(submit_time)
(
PARTITION p1 VALUES LESS THAN ('2010-02-01'),
PARTITION p2 VALUES LESS THAN ('2010-03-01'),
PARTITION p3 VALUES LESS THAN ('2010-04-01'),
PARTITION p4 VALUES LESS THAN ('2010-05-01'),
PARTITION p5 VALUES LESS THAN ('2010-06-01'),
PARTITION p6 VALUES LESS THAN ('2010-07-01'),
PARTITION p7 VALUES LESS THAN ('2010-08-01'),
PARTITION p8 VALUES LESS THAN ('2010-09-01'),
PARTITION p9 VALUES LESS THAN ('2010-10-01'),
PARTITION p10 VALUES LESS THAN ('2010-11-01'),
PARTITION p11 VALUES LESS THAN ('2010-12-01')
);
id VARCHAR(20) NOT NULL,
name VARCHAR(20) NOT NULL,
submit_time DATETIME NOT NULL,
index time_index (submit_time),
key (id),
primary key (id,submit_time)
)ENGINE=MyISAM
PARTITION BY RANGE (TO_DAYS(submit_time))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-02-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-03-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-04-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p5 VALUES LESS THAN (TO_DAYS('2010-06-01')),
PARTITION p6 VALUES LESS THAN (TO_DAYS('2010-07-01')),
PARTITION p7 VALUES LESS THAN (TO_DAYS('2010-08-01')),
PARTITION p8 VALUES LESS THAN (TO_DAYS('2010-09-01')),
PARTITION p9 VALUES LESS THAN (TO_DAYS('2010-10-01')),
PARTITION p10 VALUES LESS THAN (TO_DAYS('2010-11-01')),
PARTITION p11 VALUES LESS THAN (TO_DAYS('2010-12-01'))
);
mysql> CREATE TABLE test1 (
-> id VARCHAR(20) NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> submit_time DATETIME NOT NULL,
-> index time_index (submit_time),
-> key (id),
-> primary key (id,submit_time)
-> )ENGINE=MyISAM
-> PARTITION BY RANGE (TO_DAYS(submit_time))
-> (
-> PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-02-01')),
-> PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-03-01')),
-> PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-04-01')),
-> PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-05-01')),
-> PARTITION p5 VALUES LESS THAN (TO_DAYS('2010-06-01')),
-> PARTITION p6 VALUES LESS THAN (TO_DAYS('2010-07-01')),
-> PARTITION p7 VALUES LESS THAN (TO_DAYS('2010-08-01')),
-> PARTITION p8 VALUES LESS THAN (TO_DAYS('2010-09-01')),
-> PARTITION p9 VALUES LESS THAN (TO_DAYS('2010-10-01')),
-> PARTITION p10 VALUES LESS THAN (TO_DAYS('2010-11-01')),
-> PARTITION p11 VALUES LESS THAN (TO_DAYS('2010-12-01'))
-> );
Query OK, 0 rows affected (0.26 sec)mysql>