Mysql定期把新数据备份到备份表内

背景

一个备份表，一个新表，需要定期的增量把新表产生的数据备份到备份表内，再清空新表。

创建新表及数据

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称',
  `age` int(11) NOT NULL DEFAULT '18' COMMENT '年龄',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='测试表';

INSERT INTO `t_student` VALUES ('1', '张三', '张傻', '11', null);
INSERT INTO `t_student` VALUES ('2', '李四', '李屎', '25', null);
INSERT INTO `t_student` VALUES ('3', '王五', '网五', '26', null);

创建备份表及数据

SET FOREIGN_KEY_CHECKS=0; 
DROP TABLE IF EXISTS `t_student_bak`;
CREATE TABLE `t_student_bak` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称',
  `age` int(11) NOT NULL DEFAULT '18' COMMENT '年龄',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_nick_name` (`nick_name`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='innodb 引擎测试表';

INSERT INTO `t_student_bak` VALUES ('2', '李四', '李屎', '25', null);
INSERT INTO `t_student_bak` VALUES ('3', '王五', '网五', '26', null);
INSERT INTO `t_student_bak` VALUES ('8', '王老五', '王老虎', '55', null);
INSERT INTO `t_student_bak` VALUES ('9', '李大大', '李打打', '18', '备注');

查询新表

mysql> SELECT * from t_student ;
+----+------+-----------+-----+--------+
| id | name | nick_name | age | remark |
+----+------+-----------+-----+--------+
|  1 | 张三 | 张傻      |  11 | NULL   |
|  2 | 李四 | 李屎      |  25 | NULL   |
|  3 | 王五 | 网五      |  26 | NULL   |
+----+------+-----------+-----+--------+
3 rows in set

查询备份表

SELECT * from t_student_bak  ;
mysql> SELECT * from t_student_bak  ; 
+----+--------+-----------+-----+--------+
| id | name   | nick_name | age | remark |
+----+--------+-----------+-----+--------+
|  2 | 李四   | 李屎      |  25 | NULL   |
|  3 | 王五   | 网五      |  26 | NULL   |
|  8 | 王老五 | 王老虎    |  55 | NULL   |
|  9 | 李大大 | 李打打    |  18 | 备注   |
+----+--------+-----------+-----+--------+

4 rows in set

执行批量更新

INSERT INTO t_student_bak 
SELECT * from t_student WHERE NOT EXISTS (
 SELECT 1 FROM t_student_bak WHERE  
  t_student_bak.id = t_student.id  
)

查看结果

mysql> SELECT * from t_student_bak  ;

+----+--------+-----------+-----+--------+
| id | name   | nick_name | age | remark |
+----+--------+-----------+-----+--------+
|  1 | 张三   | 张傻      |  11 | NULL   |
|  2 | 李四   | 李屎      |  25 | NULL   |
|  3 | 王五   | 网五      |  26 | NULL   |
|  8 | 王老五 | 王老虎    |  55 | NULL   |
|  9 | 李大大 | 李打打    |  18 | 备注   |
+----+--------+-----------+-----+--------+
5 rows in set

此时id为"1"的第一行数据，被增量添加进备份表。