向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

MYSQL数据库从5.1开始支持分区，本人下载了5.1最后的版本5.1.14（最好的版本应该最稳定）。
当尝试分区时，发现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 );

成功，但不是我所想要的，因为没有了主键和外键。上网找了些资料，了解到确实主键要去掉，但是外键问题没有找到。
牺牲主键我可以接受，但是外键要去掉我很不理解也不能接受。
请～～～

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/2630/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！