mysql 非分区表转化成分区表

1.创建分区表

CREATE TABLE `app_visitor_logger2` (
`item_id` INT(11) NOT NULL AUTO_INCREMENT,
`page_title` VARCHAR(50) NOT NULL COMMENT '页面名称',
`page_code` VARCHAR(50) NOT NULL COMMENT '页面标识',
`user_id` VARCHAR(15) NULL DEFAULT NULL COMMENT '用户ID',
`name` VARCHAR(20) NULL DEFAULT NULL COMMENT '用户名',
`additional` VARCHAR(20) NULL DEFAULT NULL COMMENT '附加信息ID',
`additional_name` VARCHAR(20) NULL DEFAULT NULL COMMENT '附加信息',
`browser` VARCHAR(20) NULL DEFAULT NULL COMMENT '客户端',
`ip` VARCHAR(15) NULL DEFAULT NULL COMMENT 'IP',
`session_id` VARCHAR(32) NULL DEFAULT NULL COMMENT 'Session ID',
`source_item_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '源记录ID',
`usertype` TINYINT(1) NULL DEFAULT '0' COMMENT '用户类型',
`scene_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '来源ID',
`scene_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '来源名称',
`subscribe_at` DATETIME NULL DEFAULT NULL COMMENT '关注时间',
`created_at` DATETIME NULL DEFAULT NULL COMMENT '记录时间',
PRIMARY KEY (`item_id`),
INDEX `page_code` (`page_code`),
INDEX `INX_SESSION_ID` (`session_id`)
)
COMMENT='App打点统计日志'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
PARTITION BY RANGE(item_id)
(
PARTITION p0 VALUES LESS THAN (2000000),
PARTITION p1 VALUES LESS THAN (4000000),
PARTITION p2 VALUES LESS THAN (6000000),
PARTITION p3 VALUES LESS THAN (8000000),
PARTITION p4 VALUES LESS THAN MAXVALUE
)

2. 右键app_visitor_logger表创建触发器app_visitor_logger_after_insert,让有新增数据时自动添加到app_visitor_logger2中

app_visitor_logger_after_insert

BEGIN
insert into app_visitor_logger2 select * from app_visitor_logger order by item_id desc limit 1;
END

注意: 如果表涉及到更新,删除时一定要创建多个触发器,如app_visitor_logger_after_update,

app_visitor_logger_after_delete

BEGIN
delete from app_visitor_logger2 where app_visitor_logger2.item_id=OLD.item_id;
END

app_visitor_logger_after_update,

BEGIN
replace into app_visitor_logger2 select * from app_visitor_logger where item_id=NEW.item_id;
END

3.

a.查看app_visitor_logger2中第一次触发器进入数据的item_id为344011

b.导入原有app_visitor_logger的数据到app_visitor_logger2中

insert into app_visitor_logger2 select * from app_visitor_logger where item_id > 0 and item_id <= 100000 order by item_id asc;


insert into app_visitor_logger2 select * from app_visitor_logger where item_id > 100000 and item_id <= 200000 order by item_id asc;


insert into app_visitor_logger2 select * from app_visitor_logger where item_id > 200000 and item_id <= 300000 order by item_id asc;

insert into app_visitor_logger2 select * from app_visitor_logger where item_id > 300000 and item_id <=344010 order by item_id asc;

4.修改表名并删除触发器

RENAME TABLE `app_visitor_logger` TO `app_visitor_logger3`;
RENAME TABLE `app_visitor_logger2` TO `app_visitor_logger`;


DROP TRIGGER `app_visitor_logger_after_insert`;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;