http://www.jackxiang.com/index.php zh-cn http://www.jackxiang.com/post// jack <xdy108@126.com> Sun, 24 Jan 2010 06:34:58 +0000 http://www.jackxiang.com/post// 当尝试分区时，发现MYSQL分区的约束不少。
我打算使用RANGE分区类型，通过日期'BOOK_DATE'每年一个分区。

CREATE TABLE `in_book_main` (
  `BOOK_MAIN_ID` varchar(32) NOT NULL,
  `BOOK_DATE` date NOT NULL,
  `PROVIDER_ID` varchar(32) default NULL,
  `STATUS` varchar(4) NOT NULL,
  `RMK` varchar(400) default NULL,
  PRIMARY KEY  (`BOOK_MAIN_ID`),
  KEY `FK_Reference_7` (`PROVIDER_ID`),
  CONSTRAINT `FK_Reference_7` FOREIGN KEY (`PROVIDER_ID`) REFERENCES `base_provider` (`PROVIDER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range (to_days(BOOK_DATE))
(PARTITION p0 VALUES LESS THAN (to_days('2007-01-01')),
PARTITION p1 VALUES LESS THAN (to_days('2008-01-01')) ,
PARTITION p2 VALUES LESS THAN (to_days('2009-01-01')) ,
PARTITION p3 VALUES LESS THAN (to_days('2010-01-01')) ,
PARTITION p4 VALUES LESS THAN (to_days('2011-01-01')) ,
PARTITION p5 VALUES LESS THAN (to_days('2012-01-01')) ,
PARTITION p6 VALUES LESS THAN (to_days('2013-01-01')) ,
PARTITION p7 VALUES LESS THAN (to_days('2014-01-01')) ,
PARTITION p8 VALUES LESS THAN (to_days('2015-01-01')) ,
PARTITION p9 VALUES LESS THAN (to_days('2016-01-01')) ,
PARTITION p10 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p11 VALUES LESS THAN MAXVALUE );

注意：表的PRIMARY KEY是BOOK_MAIN_ID，有一个FOREIGN KEY引用另外一个表的ID("PROVIDER_ID"),
另外被引用的表结构如下：
CREATE TABLE `base_provider` (
  `PROVIDER_ID` varchar(32) NOT NULL,
  `PROVIDER_NAME` varchar(100) NOT NULL,
  `ADDRESS` varchar(200) default NULL,
  `PHONE` varchar(20) default NULL,
  `CONTRACT_MAN` varchar(20) default NULL,
  `Email` varchar(100) default NULL,
  `RMK` varchar(400) default NULL,
  PRIMARY KEY  (`PROVIDER_ID`),
  UNIQUE KEY `INDEX_PROVIDER_NAME` (`PROVIDER_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

结果令我 意外的是报错
ERROR 1215 : Cannot add foreign key constraint

外键出错，把添加外键的语句删除
CREATE TABLE `in_book_main` (
  `BOOK_MAIN_ID` varchar(32) NOT NULL,
  `BOOK_DATE` date NOT NULL,
  `PROVIDER_ID` varchar(32) default NULL,
  `STATUS` varchar(4) NOT NULL,
  `RMK` varchar(400) default NULL,
  PRIMARY KEY  (`BOOK_MAIN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range (to_days(BOOK_DATE))
(PARTITION p0 VALUES LESS THAN (to_days('2007-01-01')),
PARTITION p1 VALUES LESS THAN (to_days('2008-01-01')) ,
PARTITION p2 VALUES LESS THAN (to_days('2009-01-01')) ,
PARTITION p3 VALUES LESS THAN (to_days('2010-01-01')) ,
PARTITION p4 VALUES LESS THAN (to_days('2011-01-01')) ,
PARTITION p5 VALUES LESS THAN (to_days('2012-01-01')) ,
PARTITION p6 VALUES LESS THAN (to_days('2013-01-01')) ,
PARTITION p7 VALUES LESS THAN (to_days('2014-01-01')) ,
PARTITION p8 VALUES LESS THAN (to_days('2015-01-01')) ,
PARTITION p9 VALUES LESS THAN (to_days('2016-01-01')) ,
PARTITION p10 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p11 VALUES LESS THAN MAXVALUE );

又报错，这次轮到主键了。
ERROR 1490 : A PRIMARY KEY must include all columns in the table's partitioning function

再把添加主键的语句删除
CREATE TABLE `in_book_main` (
  `BOOK_MAIN_ID` varchar(32) NOT NULL,
  `BOOK_DATE` date NOT NULL,
  `PROVIDER_ID` varchar(32) default NULL,
  `STATUS` varchar(4) NOT NULL,
  `RMK` varchar(400) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range (to_days(BOOK_DATE))
(PARTITION p0 VALUES LESS THAN (to_days('2007-01-01')),
PARTITION p1 VALUES LESS THAN (to_days('2008-01-01')) ,
PARTITION p2 VALUES LESS THAN (to_days('2009-01-01')) ,
PARTITION p3 VALUES LESS THAN (to_days('2010-01-01')) ,
PARTITION p4 VALUES LESS THAN (to_days('2011-01-01')) ,
PARTITION p5 VALUES LESS THAN (to_days('2012-01-01')) ,
PARTITION p6 VALUES LESS THAN (to_days('2013-01-01')) ,
PARTITION p7 VALUES LESS THAN (to_days('2014-01-01')) ,
PARTITION p8 VALUES LESS THAN (to_days('2015-01-01')) ,
PARTITION p9 VALUES LESS THAN (to_days('2016-01-01')) ,
PARTITION p10 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p11 VALUES LESS THAN MAXVALUE );

成功，但不是我所想要的，因为没有了主键和外键。上网找了些资料，了解到确实主键要去掉，但是外键问题没有找到。
牺牲主键我可以接受，但是外键要去掉我很不理解也不能接受。
请～～～ ]]> http://www.jackxiang.com/post//#blogcomment <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 http://www.jackxiang.com/post//#blogcomment