Сравнить две таблицы в MySQL

В этой статье вы узнаете, как сравнивать две таблицы, чтобы найти несопоставимые записи.

При переносе данных нам часто приходится сравнивать две таблицы, чтобы определить запись в одной таблице, у которой нет соответствующей записи в другой таблице.

Например, у нас есть новая база данных, схема которой отличается от устаревшей базы данных. Наша задача – перенести все данные из устаревшей базы данных в новую и убедиться, что данные были перенесены правильно.

Чтобы проверить данные, нам нужно сравнить две таблицы, одну в новой базе данных и одну в устаревшей базе данных, и идентифицировать несопоставленные записи.

Предположим, у нас есть две таблицы: my_table  и you_table. Следующие шаги сравнивают две таблицы и идентифицируют несопоставленные записи:

Во-первых, используйте оператор UNION для объединения строк в обеих таблицах; включать только столбцы, которые нужно сравнить. Возвращенный набор результатов используется для сравнения.

SELECT my_table.pk, my_table.c1
FROM my_table
UNION ALL
SELECT you_table.pk, you_table.c1
FROM you_table

Во-вторых, сгруппируйте записи на основе первичного ключа и столбцов, которые необходимо сравнить. Если значения в столбцах, которые необходимо сравнить, идентичны, COUNT(*) возвращается 2, в противном случае COUNT(*) возвращается 1.

Смотрите следующий запрос:

SELECT pk, c1
FROM
 (
   SELECT my_table.pk, my_table.c1
   FROM my_table
   UNION ALL
   SELECT you_table.pk, you_table.c1
   FROM you_table
)  t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pk

Если значения в столбцах, участвующих в сравнении, идентичны, строка не возвращается.

Пример сравнения двух таблиц в MySQL

Давайте посмотрим на пример, который имитирует шаги выше.

Сначала создайте 2 таблицы с похожей структурой:

CREATE TABLE my_table(
	id int auto_increment primary key,
    title varchar(255)	
);

CREATE TABLE you_table(
	id int auto_increment primary key,
    title varchar(255),
    note varchar(255)
);

Во-вторых, вставьте некоторые данные в таблицы my_table и you_table:

INSERT INTO my_table(title)
VALUES('row 1'),('row 2'),('row 3');

INSERT INTO you_table(title,note)
SELECT title, 'data migration'
FROM my_table;

В-третьих, сравните значения id и столбца title обеих таблиц:

SELECT id,title
FROM (
SELECT id, title FROM my_table
UNION ALL
SELECT id,title FROM you_table
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;

Возвращенных строк не будет, потому что нет несоответствующих записей.

В-четвертых, вставьте новую строку в таблицу you_table:

INSERT INTO you_table(title,note)
VALUES('new row 4','new');

В-пятых, выполните запрос, чтобы снова сравнить значения столбца заголовка в обеих таблицах. Новая строка, которая является несопоставленной строкой, должна вернуться.

В этой статье вы узнали, как сравнивать две таблицы на основе определенных столбцов, чтобы найти несопоставленные записи.

Читайте также

Учебник. MySQL и Perl

5 лет назад

Масштабирование баз данных WordPress | Удаленные серверы, Sharding и репликация с Ludicrous DB и AWS

5 лет назад

Какой тип данных MySQL использовать для хранения IP-адреса?

3 года назад

Как показать всех пользователей в MySQL

8 лет назад