MySQL 파티션

 

 

1. range

range 파티션은 datetime, timestamp기준으로 YEAR 또는 to_days, unix_timestamp를 사용하여 파티션 진행

 

1) unix_timestamp기준 파티션

CREATE TABLE quarterly_report

(

    report_id int(11) NOT NULL,

    report_status varchar(20) NOT NULL,

    report_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    primary key (report_id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) )

(   

        PARTITION p202001 VALUES LESS THAN (UNIX_TIMESTAMP('2020-02-01 00:00:00'),

        PARTITION p202002 VALUES LESS THAN (UNIX_TIMESTAMP('2020-03-01 00:00:00'),

        PARTITION p202003 VALUES LESS THAN (UNIX_TIMESTAMP('2020-04-01 00:00:00'),

        PARTITION pmax VALUES LESS THAN (MAXVALUE)

)

 

2. to_days 기준 파티션

CREATE TABLE quarterly_report

(

    report_id int(11) NOT NULL,

    report_status varchar(20) NOT NULL,

    report_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    primary key (report_id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE ( to_days(report_updated) )

(   

        PARTITION p202001 VALUES LESS THAN (to_days('2020-02-01 00:00:00'),

        PARTITION p202002 VALUES LESS THAN (to_days('2020-03-01 00:00:00'),

        PARTITION p202003 VALUES LESS THAN (to_days('2020-04-01 00:00:00'),

        PARTITION pmax VALUES LESS THAN (MAXVALUE)

)

 

3. 일반컬럼 파티션

 

CREATE TABLE quarterly_report

(

    report_id int(11) NOT NULL,

    report_status varchar(20) NOT NULL,

    report_updated varchar(10 not null,

    primary key (report_id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE columns ( report_updated )

(   

        PARTITION p202001 VALUES LESS THAN ('2020-02-01'),

        PARTITION p202002 VALUES LESS THAN ('2020-03-01'),

        PARTITION p202003 VALUES LESS THAN ('2020-04-01'),

        PARTITION pmax VALUES LESS THAN (MAXVALUE)

)

 

2. list 

 

CREATE TABLE quarterly_report

(

    report_id int(11) NOT NULL,

    report_status varchar(20) NOT NULL,

    report_updated varchar(10 not null,

    primary key (report_id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY list ( report_status )

(   

        PARTITION STAT_A VALUES IN ('A1', 'A2'),

        PARTITION STAT_B VALUES IN ('B1', 'B2'),

        PARTITION STAT_C VALUES IN ('C1', 'C2'),

        PARTITION pmax VALUES LESS THAN (MAXVALUE)

)