mysql 有2个表tb1和tb2
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb1`
-- ----------------------------
DROP TABLE IF EXISTS `tb1`;
CREATE TABLE `tb1` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb1
-- ----------------------------
INSERT INTO `tb1` VALUES ('1', 'AAA');
INSERT INTO `tb1` VALUES ('2', 'BBB');
INSERT INTO `tb1` VALUES ('3', 'CCC');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb2`
-- ----------------------------
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb2
-- ----------------------------
INSERT INTO `tb2` VALUES ('2', 'BBB');
INSERT INTO `tb2` VALUES ('3', 'CCB');
INSERT INTO `tb2` VALUES ('4', 'DDD');假设2个表都有50多万数据。
如何有效率的对比表tb1和tb2,将tb2中id等于tb1中id的值更新到tb1中,如果在tb1中找不到,则新增,否则更新
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb1`
-- ----------------------------
DROP TABLE IF EXISTS `tb1`;
CREATE TABLE `tb1` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb1
-- ----------------------------
INSERT INTO `tb1` VALUES ('1', 'AAA');
INSERT INTO `tb1` VALUES ('2', 'BBB');
INSERT INTO `tb1` VALUES ('3', 'CCC');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb2`
-- ----------------------------
DROP TABLE IF EXISTS `tb2`;
CREATE TABLE `tb2` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb2
-- ----------------------------
INSERT INTO `tb2` VALUES ('2', 'BBB');
INSERT INTO `tb2` VALUES ('3', 'CCB');
INSERT INTO `tb2` VALUES ('4', 'DDD');假设2个表都有50多万数据。
如何有效率的对比表tb1和tb2,将tb2中id等于tb1中id的值更新到tb1中,如果在tb1中找不到,则新增,否则更新
用REPLAC INTO
OR
INSERT INTO ON ...DUPLICATE KEY UPDATE
insert into tb1 (id,name)
select id,name from tb2
ON DUPLICATE KEY UPDATE name=VALUES(name);
2. update tb1,tb2 set tb1.name=tb2.name where tb1.id=tb2.id and tb1.name!=tb2.name
3. insert into tb1 select * from tb2 where not exists(select 1 from tb1 where id=tb2.id)