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

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

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

Вы можете использовать PRIMARY KEY или же UNIQUEИндексируйте таблицу с соответствующими полями, чтобы избежать дублирования записей. Возьмем один пример: следующая таблица не содержит такого индекса или первичного ключа, поэтому она допускает дублирование записей для first_name и last_name.

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

Чтобы предотвратить создание в этой таблице нескольких записей с одинаковыми значениями имени и фамилии, добавьте в ее определение ПЕРВИЧНЫЙ КЛЮЧ. Когда вы это делаете, также необходимо объявить индексированные столбцы 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. Если это дубликат, новая запись заменяет старую -

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 сохраняет последние дубликаты и удаляет все более ранние.

Еще один способ обеспечить уникальность - добавить в таблицу УНИКАЛЬНЫЙ индекс, а не ПЕРВИЧНЫЙ КЛЮЧ.

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, которое исключает уникальные значения, требуя, чтобы количество групп было больше единицы.

Удаление дубликатов из результата запроса:

Вы можете использовать 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;

Простой способ удалить повторяющиеся записи из таблицы - добавить в эту таблицу ИНДЕКС или КЛЮЧ PRIMAY. Даже если эта таблица уже доступна, вы можете использовать эту технику для удаления повторяющихся записей, и вы будете в безопасности и в будущем.

mysql> ALTER IGNORE TABLE person_tbl 
   -> ADD PRIMARY KEY (last_name, first_name);