MySQL子分区一例

这是现实中的一个例子，但是我不推荐使用MySQL分区用于生产

CREATE TABLE t_log (
ID int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
VERSON int(11) NOT NULL DEFAULT 0 COMMENT ’ 版本号’,
ACTIONS varchar(100) DEFAULT NULL COMMENT ’ 操作记录’,
ADMINID int(11) DEFAULT 1 COMMENT ‘管理员ID’,
IP varchar(20) DEFAULT ‘1’ COMMENT ’ 登录Ip’,
CDATE datetime NOT NULL COMMENT ‘添加时间’,
UDATE datetime NOT NULL COMMENT ‘修改时间’,
PRIMARY KEY (ID,CDATE),
KEY ADMINID (ADMINID) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘日志表’
Time: 0.024s

ALTER TABLE blog.t_log PARTITION BY RANGE (year(CDATE))
PARTITIONS 1
SUBPARTITION BY HASH (to_days(CDATE))
SUBPARTITIONS 4
(PARTITION p0 VALUES LESS THAN (2020)
(SUBPARTITION s200 ,
SUBPARTITION s201 ,
SUBPARTITION s202 ,
SUBPARTITION s203 ))
Time: 0.057s

alter table t_log
ADD PARTITION (PARTITION p1 VALUES LESS THAN (2021)(
SUBPARTITION s210 ENGINE = InnoDB,
SUBPARTITION s211 ENGINE = InnoDB,
SUBPARTITION s212 ENGINE = InnoDB,
SUBPARTITION s213 ENGINE = InnoDB
))
Time: 0.054s