Учебники

MySQL — обработка дубликатов

Декабрь 28, 2018

Как правило, таблицы или наборы результатов иногда содержат повторяющиеся записи. В большинстве случаев это разрешено, но иногда требуется остановить повторяющиеся записи. Требуется выявить дубликаты записей и удалить их из таблицы. В этой главе будет описано, как предотвратить появление дубликатов записей в таблице и как удалить уже существующие дубликаты записей.

Предотвращение появления дубликатов в таблице

Вы можете использовать PRIMARY KEY или UNIQUE Index для таблицы с соответствующими полями, чтобы остановить повторяющиеся записи.

Давайте рассмотрим пример. В следующей таблице нет такого индекса или первичного ключа, поэтому допускается дублирование записей для first_name и last_name . 

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Чтобы предотвратить создание в этой таблице нескольких записей с одинаковыми значениями имени и фамилии, добавьте PRIMARY KEY к ее определению. Когда вы делаете это, также необходимо объявить индексированные столбцы как NOT NULL , потому что PRIMARY KEY не допускает значения NULL — 

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

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

Используйте команду INSERT IGNORE, а не команду INSERT . Если запись не дублирует существующую запись, MySQL вставляет ее как обычно. Если запись является дубликатом, то ключевое слово IGNORE говорит MySQL, что нужно отбросить ее без предупреждения.

Следующий пример не выдает ошибку и в то же время не будет вставлять дублирующиеся записи. 

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Используйте команду REPLACE, а не команду INSERT. Если запись новая, она вставляется так же, как и INSERT. Если это дубликат, новая запись заменяет старую. 

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

Команды INSERT IGNORE и REPLACE следует выбирать в соответствии с поведением обработки дубликатов, которое вы хотите применить. Команда INSERT IGNORE сохраняет первый набор дублированных записей и отбрасывает остальные. Команда REPLACE сохраняет последний набор дубликатов и удаляет все предыдущие.

Другой способ обеспечить уникальность — добавить в таблицу индекс UNIQUE, а не PRIMARY KEY. 

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Подсчет и идентификация дубликатов

Ниже приведен запрос на подсчет повторяющихся записей с именем first_name и last_name в таблице. 

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

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

  • Определите, какие столбцы содержат значения, которые могут быть дублированы.

  • Перечислите эти столбцы в списке выбора столбцов вместе с COUNT (*) .

  • Перечислите также столбцы в предложении GROUP BY .

  • Добавьте предложение HAVING , которое исключает уникальные значения, так как число групп должно быть больше единицы.

Определите, какие столбцы содержат значения, которые могут быть дублированы.

Перечислите эти столбцы в списке выбора столбцов вместе с COUNT (*) .

Перечислите также столбцы в предложении GROUP BY .

Добавьте предложение HAVING , которое исключает уникальные значения, так как число групп должно быть больше единицы.

Исключение дубликатов из результата запроса

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

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Альтернативой команде DISTINCT является добавление предложения GROUP BY с именами столбцов, которые вы выбираете. Это приводит к удалению дубликатов и выбору только уникальных комбинаций значений в указанных столбцах. 

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Удаление дубликатов с помощью замены таблицы

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

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Простой способ удалить дубликаты записей из таблицы — добавить в эту таблицу ИНДЕКС или ПЕРВИЧНЫЙ КЛЮЧ. Даже если эта таблица уже доступна, вы можете использовать эту технику, чтобы удалить дубликаты записей, и вы будете в безопасности в будущем.

127
Share: