[MySQL] Partition 예제

 

<예제 코드_1>

==> PARTITION 할 때 Column 하나에만 Primary Key 를 걸면 Error 발생.

==> 모든 Column 을 Partition 해야함.

CREATE TABLE sales (
   sales_id INT,
   sales_date DATE,
   amount INT,
   PRIMARY KEY (sales_id, sales_date, amount)
)
PARTITION BY RANGE(YEAR(sales_date))
(
   PARTITION p_2015 VALUES LESS THAN (2016),
   PARTITION p_2016 VALUES LESS THAN (2017),
   PARTITION p_2017 VALUES LESS THAN (2018),
   PARTITION p_others VALUES LESS THAN MAXVALUE
);

 

---

 

<예제 코드_2>

==> Partition 추가

ALTER TABLE sales
  REORGANIZE PARTITION p_2018 INTO (
    PARTITION p_2018 VALUES LESS THAN (2019),
    PARTITION p_others VALUES LESS THAN (MAXVALUE)
  );
  
-- MAXVALUE 재설정 적용
OPTIMIZE TABLE sales

 

 

---

 

<참조 1> https://chat.openai.com/chat

<참조 2> https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=165275

<참조 3> https://jaehoney.tistory.com/62

<참조 4>