下面执行过后:
test_202001 是全量数据
test 是 登录时间 > 2020-01-01 后的数据
-- 登录日志表 保存 登录时间 > 2020-01-01
USE 库名;
CREATE TABLE `test_tmp` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ' ID',
`login_time` datetime NOT NULL COMMENT '登录时间',
`uid` int(11) NOT NULL COMMENT '用户ID',
`ucid` varchar(255) NOT NULL COMMENT '用户子ID',
`login_ip` varchar(50) NOT NULL COMMENT '登录IP',
`user_identity` varchar(50) NOT NULL DEFAULT '' COMMENT '用户身份:VIP,TRIAL',
`user_type` varchar(10) NOT NULL DEFAULT '' COMMENT '登录用户类型:P-账号,S-子账号',
`website` tinyint(2) NOT NULL COMMENT '登录的站点ID',
`city` varchar(255) DEFAULT '' COMMENT '登录IP转城市地区',
`user_agent` varchar(255) DEFAULT '' COMMENT '登录的浏览器信息',
`login_mac` varchar(255) NOT NULL COMMENT '客户端登录mac地址',
`previous_url` varchar(255) DEFAULT '' COMMENT '请求来源链接',
`client_id` varchar(255) DEFAULT '' COMMENT '生成的客户标识ID',
`login_way` tinyint(2) DEFAULT '0' COMMENT '登录方式(1=web普通,2=客户端登录,3=刷新换令牌)',
`client_number` varchar(255) DEFAULT '' COMMENT '浏览器指纹',
`col1` varchar(255) DEFAULT NULL COMMENT '备用字段1',
`col2` varchar(255) DEFAULT NULL COMMENT '备用字段2',
`col3` varchar(255) DEFAULT NULL COMMENT '备用字段3',
`col4` varchar(255) DEFAULT NULL COMMENT '备用字段4',
PRIMARY KEY (`id`),
KEY `idx_login_time` (`login_time`),
KEY `idx_uid` (`uid`),
KEY `idx_ucid` (`ucid`),
KEY `idx_login_ip` (`login_ip`),
KEY `idx_user_identity` (`user_identity`),
KEY `idx_user_type` (`user_type`),
KEY `idx_website` (`website`),
KEY `idx_login_mac` (`login_mac`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户登录日志';
DROP PROCEDURE IF EXISTS copyTable;
TRUNCATE TABLE test_tmp;
DELIMITER //
CREATE PROCEDURE copyTable()
BEGIN
SET @last_id=1;
SET @x = 1;
WHILE @x DO
SET @x = 0;
INSERT INTO test_tmp SELECT *,'','','','','' FROM `库名`.`test` WHERE id>=@last_id AND login_time > '2020-01-01 00:00:00' ORDER BY id ASC LIMIT 10000;
SELECT id INTO @x FROM test WHERE id>=@last_id AND login_time > '2020-01-01 00:00:00' ORDER BY id ASC LIMIT 10000,1;
SET @last_id=@x;
END WHILE;
RENAME TABLE test TO test_202001;
RENAME TABLE test_tmp TO test;
END
//
DELIMITER;
CALL copyTable();
DROP PROCEDURE IF EXISTS copyTable;