Preface

In data migration , We often need to compare two tables , In order to identify records in one table that do not have corresponding records in another table .

for example , We have a new database , Its architecture is different from the old database . Our task is to migrate all data from the old database to the new database , And verify that the data is migrated correctly . To check the data , We have to compare the two tables , One in the new database , One in the old database , And identify mismatched records .

So let's use union all Link two tables , After that, use the temporary table or derived table to compare the data ~

One 、 preparation

Create table ：t1

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`  (
  `tid` tinyint(4) NOT NULL AUTO_INCREMENT COMMENT ' Primary key id',
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ' nickname ',
  `age` int(11) NULL DEFAULT NULL COMMENT ' Age ',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

surface ：t1 Insert a piece of data

INSERT INTO `t1` VALUES (1, ' Zhang San ', 18);

Create table ：t2

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`  (
  `tid` tinyint(4) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ' nickname ',
  `age` int(11) NULL DEFAULT NULL COMMENT ' Age ',
  `gender` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ' Gender ',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

surface ：t2 Insert a piece of data

INSERT INTO `t2` VALUES (1, ' Zhang San ', 18, ' male ');

Be careful ： You can see t1、t2 Table attribute values correspond to the same

Two 、 Use tests

SQL：

SELECT
	tid,
	nickname,
	age 
FROM
	( SELECT tid, nickname, age FROM t1 UNION ALL SELECT tid, nickname, age FROM t2 ) tbl 
GROUP BY
	tid,
	nickname,
	age 
HAVING
	count(*) = 1 
ORDER BY
	tid;

Of course, there is no data returned after running , Because they are no different . Don't try so hard, , Let's do it again t2 Insert a row of data in the table ：

INSERT INTO t2 ( nickname, age, gender )
VALUES
	( ' Li Si ', 18, ' male ' );

Re execution SQL（ because ‘ Li Si ’ This data , surface t1、t2 If there is no duplication between the two tables of related attribute values, it will be queried ）：