Учебники

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

Май 28, 2019

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

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

Вы можете использовать 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 . Если запись не дублирует существующую запись, MySQLi вставляет ее как обычно. Если запись является дубликатом, ключевое слово IGNORE сообщает MySQLi о том, что он должен отбросить ее без предупреждения.

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

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;

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

40
Share: