mysql getrange_MySQL分区表Range方式

CREATE TABLE `user_login_log` (

`user_id` int(10) unsigned NOT NULL COMMENT '登录⽤用户ID',

`login_time` datetime NOT NULL COMMENT '⽤用户登录时间',

`login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',

`login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功') ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE(YEAR(login_time))(

PARTITION p0 VALUES LESS THAN (2017),

PARTITION p1 VALUES LESS THAN (2018),

PARTITION p2 VALUES LESS THAN (2019),

PARTITION p3 VALUES LESS THAN (2020),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

应⽤场景

CREATE TABLE `user_login_log` (

`user_id` int(10) unsigned NOT NULL COMMENT '登录⽤用户ID',

`login_time` datetime NOT NULL COMMENT '⽤用户登录时间',

`login_ip` int(10) unsigned NOT NULL COMMENT '登录IP',

`login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功') ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE(YEAR(login_time))(

PARTITION p0 VALUES LESS THAN (2017),

PARTITION p1 VALUES LESS THAN (2018),

PARTITION p2 VALUES LESS THAN (2019),

PARTITION p3 VALUES LESS THAN (2020),

PARTITION p3 VALUES LESS THAN (2020)

);

不实用封口的好处是，需要增加分区时执行：

ALTER TABLE user_login_log ADD PARTITION(PARTITION p4 VALUES LESS

THAN(2021))

⽇志数据进⾏备份的时候，在MySQL v5.7版本后加⼊了一个分区交换的概念

表结构要相同

归档的这个表不能是分区表

归档表不能有外键约束

引擎是ARCHIVE

#归档表

CREATE TABLE `arch_user_login_log_2016` (

`user_id` int(10) unsigned NOT NULL COMMENT '登录⽤用户ID', `login_time` datetime NOT NULL COMMENT '⽤用户登录时间', `login_ip` int(10) unsigned NOT NULL COMMENT '登录IP', `login_type` tinyint(4) NOT NULL COMMENT '登录类型:0未成功 1成功'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE user_login_log exchange PARTITION p0 WITH TABLE

arch_user_login_log_2016;

ALTER TABLE arch_user_login_log_2016ENGINE=ARCHIVE;