partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

分区类型

• [LINEAR] HASH(expr) 根据值的哈希分区
• [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
• RANGE{(expr) | COLUMNS(column_list)} 根据值得范围分区
• LIST{(expr) | COLUMNS(column_list)} 根据不同的值分区

COLUMNS 不限于整数

创建分区

PARTITION BY LIST(column) (
    PARTITION a VALUES IN (a1, a2, a3),
    PARTITION b VALUES IN (b1, b2, b3),
    PARTITION c VALUES IN (c1, c2, c3)
)

PARTITION BY RANGE(column) (
    PARTITION 2012q1 VALUES LESS THAN('2012-04-01'),
    PARTITION 2012q2 VALUES LESS THAN('2012-07-01'),
    PARTITION 2012q3 VALUES LESS THAN('2012-10-01'),
    PARTITION 2012q4 VALUES LESS THAN('2013-01-01')
)

PARTITION BY HASH(column) PARTITIONS 128
PARTITION BY HASH(dayofmonth(date)) PARTITIONS 31

查看分区信息

SELECT * FROM `information_schema`.`PARTITIONS`;

子分区

1. PARTITION 关键字换成 SUBPARTITION，PARTITIONS 关键字换成 SUBPARTITIONS，接在分区语句后面。
2. 可以是不同类型。

比如：

PARTITION BY HASH (prod_id) SUBPARTITION BY HASH (cust_id)
PARTITIONS 4 SUBPARTITIONS 4;

脚本

如果是 By Range 分区，一般需要自动创建新的分区，删除久的分区。

比如：

CREATE TABLE `test` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `date` DATE NOT NULL,
    `key` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
    `value` VARCHAR(300) NOT NULL COLLATE 'utf8mb4_general_ci',
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`, `date`) USING BTREE,
    UNIQUE INDEX `key` (`date`, `key`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
/*!50100 PARTITION BY RANGE (to_days(`date`))
(PARTITION p20230123 VALUES LESS THAN (738909) ENGINE = InnoDB,
 PARTITION p20230124 VALUES LESS THAN (738910) ENGINE = InnoDB,
 PARTITION p20230125 VALUES LESS THAN (738911) ENGINE = InnoDB)  */;

然后，通过下面这个 cron 任务自动更新分区：

#!/bin/bash

# 开启调试模式，输出每条执行的命令及其执行结果
set -x

# 检查当前机器 IP 地址中是否包含指定的 VIP（虚拟 IP）
# 确认在主 MySQL 上执行
vip_w="192.168.12.34"
if [ $(/sbin/ip a | grep "${vip_w}" | wc -l) -eq 0 ]; then echo 'WARN: Wrong Machine!!!'; exit 1; fi

# 删除 90 天前的分区
# PS：如果分区不存在，TRUNCATE 不会报错。
delete_date=$(date -d '90 days ago' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test TRUNCATE PARTITION p$delete_date;"  # DROP

# 创建未来分区
create_date=$(date -d '7 days' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test ADD PARTITION (PARTITION p$delete_date VALUES LESS THAN (TO_DAYS("$delete_date")));"