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

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

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

Вы можете использовать ПЕРВИЧНЫЙ КЛЮЧ или УНИКАЛЬНЫЙ индекс в таблице с соответствующими полями, чтобы предотвратить дублирование записей.

Давайте возьмем пример. Следующая таблица не содержит такого индекса или первичного ключа, поэтому она допускает дублирование записей для 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 . Если запись не дублирует существующую запись, 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 сохраняет последний набор дубликатов и удаляет все более ранние.

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

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;

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

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);
Выберите поля, которые будут показаны. Прочие будут скрыты. Перктаскивайте мышкой для изменения порядка полей.
  • Изображение
  • SKU
  • Рейтинг
  • Цена
  • Запасы
  • Доступность
  • В корзину
  • Описание
  • Содержимое
  • Вес
  • Размеры
  • Дополнительная информация
  • Атрибуты
  • Custom attributes
  • Настраиваемые поля
Сравнить
Список желаний 0
Открыть страницу желаний Продолжить покупки