mysql partitioning 예제

월별로 쪼개기 예제

CREATE TABLE `test1` (

  `date` datetime DEFAULT NULL,

  `ip` varchar(15) DEFAULT NULL,

  KEY `date` (`date`),

  KEY `ip` (`ip`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (to_days(date))

(

  PARTITION p01 VALUES LESS THAN (to_days('2012-01-31')),

  PARTITION p02 VALUES LESS THAN (to_days('2012-02-31')),

  PARTITION p03 VALUES LESS THAN (to_days('2012-03-31')),

  PARTITION p04 VALUES LESS THAN (to_days('2012-04-31')),

  PARTITION p05 VALUES LESS THAN (to_days('2012-05-31')),

  PARTITION p06 VALUES LESS THAN (to_days('2012-06-31')),

  PARTITION p07 VALUES LESS THAN (to_days('2012-07-31')),

  PARTITION p08 VALUES LESS THAN (to_days('2012-08-31')),

  PARTITION p09 VALUES LESS THAN (to_days('2012-09-31')),

  PARTITION p10 VALUES LESS THAN (to_days('2012-10-31')),

  PARTITION p11 VALUES LESS THAN (to_days('2012-11-31')),

  PARTITION p12 VALUES LESS THAN (to_days('2012-12-31')),

  PARTITION p13 VALUES LESS THAN (MAXVALUE)

);

CREATE TABLE `test1` (

  `date` datetime DEFAULT NULL,

  `ip` varchar(15) DEFAULT NULL,

  KEY `date` (`date`),

  KEY `ip` (`ip`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (month(date))

(

  PARTITION p01 VALUES LESS THAN (01),

  PARTITION p02 VALUES LESS THAN (02),

  PARTITION p03 VALUES LESS THAN (03),

  PARTITION p04 VALUES LESS THAN (04),

  PARTITION p05 VALUES LESS THAN (05),

  PARTITION p06 VALUES LESS THAN (06),

  PARTITION p07 VALUES LESS THAN (07),

  PARTITION p08 VALUES LESS THAN (08),

  PARTITION p09 VALUES LESS THAN (09),

  PARTITION p10 VALUES LESS THAN (10),

  PARTITION p11 VALUES LESS THAN (11),

  PARTITION p12 VALUES LESS THAN (12),

  PARTITION p13 VALUES LESS THAN (13)

);

CREATE TABLE `test` (

  `date` datetime DEFAULT NULL,

  `ip` varchar(15) DEFAULT NULL,

  KEY `date` (`date`),

  KEY `ip` (`ip`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (year(date))

(

  PARTITION p01 VALUES LESS THAN ('2012'),

  PARTITION p02 VALUES LESS THAN ('2013'),

  PARTITION p03 VALUES LESS THAN (MAXVALUE)

);