Обработка дублирующихся данных в MySQL

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

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


Предотвращение появления дублирующихся данных в таблицах

Вы можете задать указанные поля в таблице данных MySQL какPRIMARY KEYилиUNIQUEиндекс, чтобы обеспечить уникальность данных.

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

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

Если вы хотите задать поля first_name и last_name в таблице так, чтобы данные не могли дублироваться, можно использовать режим двойного первичного ключа для обеспечения уникальности данных. Если вы задаёте двойные первичные ключи, значение по умолчанию для этого ключа не может быть NULL; его можно задать как NOT 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)
);

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

Разница между INSERT IGNORE INTO и INSERT INTO заключается в том, что INSERT IGNORE INTO игнорирует данные, которые уже существуют в базе данных. Если в базе данных таких данных нет, он вставляет новые данные; если они есть, он пропускает эту запись. Это сохраняет существующие данные в базе данных и позволяет вставлять данные в промежутки.

В следующем примере используется INSERT IGNORE INTO. После выполнения ошибка не возникает, и в таблицу данных не вставляются дублирующиеся данные:

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)

INSERT IGNORE INTO: при вставке данных, после того как уникальность записей задана, если вставляются дублирующиеся данные, ошибка не возвращается; возвращается только предупреждение. REPLACE INTO: если запись с таким же первичным ключом или уникальным ключом существует, она сначала удаляется, а затем вставляется новая запись.

Ещё один способ задать уникальность данных — добавить индекс UNIQUE, как показано ниже:

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, чтобы задать количество дубликатов больше 1.

Фильтрация дублирующихся данных

Если вам нужно прочитать данные без дубликатов, вы можете использовать ключевое слово DISTINCT в операторе SELECT для фильтрации дублирующихся данных.

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

Вы также можете использовать GROUP BY, чтобы читать данные без дубликатов из таблицы данных:

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

Удаление дублирующихся данных

Если вы хотите удалить дублирующиеся данные из таблицы данных, вы можете использовать следующий оператор SQL:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Конечно, вы также можете использовать простой метод добавления INDEX (индекс) и PRIMARY KEY (первичный ключ) в таблицу данных, чтобы удалить дублирующиеся записи в таблице. Метод следующий:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);
Другие расширения