Partition Table

Check if partition is supported

> show plugins;

Create partition table

create table mm_tx_new (
  start_time    timestamp(6) not null default '0000-00-00 00:00:00.000000',
  end_time      timestamp(6),
  hostname      varchar(20),
  servername    varchar(20) not null default 'noservername',
  tx_id         varchar(15),
  user_opi      varchar(20),
  tx_resp       integer,
  primary key(start_time,servername)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE  ( FLOOR(UNIX_TIMESTAMP(start_time)) )
(PARTITION p202105 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-06-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-07-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-08-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-09-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-10-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-11-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2021-12-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2022-01-01 00:00:00.000000')) ) ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2022-02-01 00:00:00.000000')) ) ENGINE = InnoDB)
;
 

Check partition table

> show create table mm_tx_new;

Add new partition for specified table

ALTER TABLE `mm_tx_new` REORGANIZE PARTITION pMax INTO (
    PARTITION p202501 VALUES LESS THAN ( FLOOR(UNIX_TIMESTAMP('2025-02-01 00:00:00.000000')) ) ENGINE = InnoDB,
    PARTITION pMax VALUES LESS THAN MAXVALUE
);

選用: pMax 是用來儲存最後一個 partition 以後的資料，通常 pMax 不會有資料。目的用途可用來捕捉不預期的未來資料。

Delete the partition

Purge the data in the specified partition

ALTER TABLE <schema.table> TRUNCATE PARTITION <partition-name>