最后更新： 2021/8/10

分区键要求

every unique key on the table must use every column in the table‘s partitioning expression.This also includes the table‘s primary key, since it is by definition a unique key.

参考：https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html

分区表

Range分区表

CREATE TABLE `trade_hislog` (
        `logid` INT(30) NOT NULL AUTO_INCREMENT COMMENT ‘日志id，主键‘,
        `sys_date` DATE NULL DEFAULT NULL,
        `create_date` VARCHAR(30) NULL DEFAULT NULL,
        UNIQUE INDEX `PK_TRADE_HISLOG` (`logid`,sys_date) USING BTREE,
        INDEX `INDEX_TRADE_HISLOG_CLIENTID` (`clientid`) USING BTREE,
        INDEX `INDEX_TRADE_HISLOG_FUNCNO` (`funcno`) USING BTREE
)
COMMENT=‘日志历史表‘
COLLATE=‘utf8_general_ci‘
ENGINE=InnoDB
partition by range (YEAR(sys_date))
(
partition p2019 values less than(2020),
partition p2020 values less than(2021),
partition p2021 values less than(2022),
partition p_all values less than maxvalue
);

参考

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-functions.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

Mysql5.7分区表使用