要求: 表article每周一个分区, Record_MD5_ID做唯一索引, Time自动赋为当前时间
限制: 以时间Time来做分区字段, 字段类型不能为timestamp, 所以不能给当前默认时间, 表也不能有唯一索引及主键, 否则要作为分区键
实现: 用触发器来实现当前时间赋值, 将Record_MD5_ID插入到另外一个唯一表
用YearWeek()来作为分区函数, 会有问题, 导致查询会全表扫描.
CREATE TABLE `table1` ( `Article_Detail_ID` int(11) NOT NULL AUTO_INCREMENT, … `Record_MD5_ID` varchar(40) NOT NULL, `Time` datetime DEFAULT NULL, KEY `PRIMARYKeyID` (`Article_Detail_ID`), KEY `Inx_Record_Md5_ID` (`Record_MD5_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (YEARWEEK(Time)) (PARTITION P2011_39 VALUES LESS THAN (201139) ENGINE = InnoDB, PARTITION P2011_40 VALUES LESS THAN (201140) ENGINE = InnoDB, PARTITION P2011_41 VALUES LESS THAN (201141) ENGINE = InnoDB, PARTITION P2011_42 VALUES LESS THAN (201142) ENGINE = InnoDB, PARTITION P2011_43 VALUES LESS THAN (201143) ENGINE = InnoDB, PARTITION P2011_44 VALUES LESS THAN (201144) ENGINE = InnoDB, PARTITION P2011_45 VALUES LESS THAN (201145) ENGINE = InnoDB, PARTITION P2011_46 VALUES LESS THAN (201146) ENGINE = InnoDB, PARTITION P2011_47 VALUES LESS THAN (201147) ENGINE = InnoDB, PARTITION P2011_48 VALUES LESS THAN (201148) ENGINE = InnoDB, PARTITION P2011_49 VALUES LESS THAN (201149) ENGINE = InnoDB, PARTITION P2011_50 VALUES LESS THAN (201150) ENGINE = InnoDB, PARTITION P2011_51 VALUES LESS THAN (201151) ENGINE = InnoDB, PARTITION P2011_52 VALUES LESS THAN (201152) ENGINE = InnoDB, PARTITION P2011_53 VALUES LESS THAN (201153) ENGINE = InnoDB, PARTITION P2012_01 VALUES LESS THAN (201201) ENGINE = InnoDB, PARTITION P2012_02 VALUES LESS THAN (201202) ENGINE = InnoDB, ... PARTITION PMaxValue VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */; CREATE TRIGGER `trg_article_record_md5` BEFORE INSERT ON `article` FOR EACH ROW BEGIN INSERT INTO Record_MD5_ID_Unique SET Record_MD5_ID = NEW.Record_MD5_ID; if NEW.Extracted_Time is null or NEW.Extracted_Time='' then SET NEW.Extracted_Time=NOW(); end if; END;
父分区为时间分区, 子分区为HASH MOD分区:
CREATE TABLE `table1` ( `Stat_Article_ID` int(11) NOT NULL AUTO_INCREMENT, `Article_ID` int(11) NOT NULL, `Client_ID` int(11) NOT NULL, `Article_Extracted_Time` datetime NOT NULL, ... `Created_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY `PRIMARYKeyID` (`Stat_Article_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 /*!50500 PARTITION BY RANGE COLUMNS(Article_Extracted_Time) SUBPARTITION BY HASH (Mod(Client_ID, 10)) SUBPARTITIONS 10 (PARTITION P2011_10 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB, PARTITION P2011_11 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB, PARTITION P2011_12 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB, PARTITION P2012_01 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB, PARTITION P2012_02 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB, PARTITION P2012_03 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB, PARTITION P2012_04 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB, PARTITION P2012_05 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB, PARTITION P2012_06 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB, PARTITION P2012_07 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB, PARTITION P2012_08 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB, PARTITION P2012_09 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB, PARTITION P2012_10 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB, PARTITION P2012_11 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB, PARTITION P2012_12 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB, PARTITION P2013_01 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB, PARTITION P2013_02 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB, PARTITION P2013_03 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB, PARTITION PMaxValue VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
时间分区:
CREATE TABLE `table1` ( .... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50500 PARTITION BY RANGE COLUMNS(Create_Date) (PARTITION P2011_10 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB, PARTITION P2011_11 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB, PARTITION P2011_12 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB, PARTITION P2012_01 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB, PARTITION P2012_02 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB, PARTITION P2012_03 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB, PARTITION P2012_04 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB, PARTITION P2012_05 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB, PARTITION P2012_06 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB, PARTITION P2012_07 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB, PARTITION P2012_08 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB, PARTITION P2012_09 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB, PARTITION P2012_10 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB, PARTITION P2012_11 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB, PARTITION P2012_12 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB, PARTITION P2013_01 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB, PARTITION P2013_02 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB, PARTITION P2013_03 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB, PARTITION P2013_04 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB, PARTITION P2013_05 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB, PARTITION PMaxValue VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;