MySQL / 14. Обработка повторяющихся записей

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

Задача
Вы хотите не допустить появления в таблице дубликатов, чтобы впоследствии не пришлось устранять их.

Решение
Используйте индекс PRIMARY KEY или UNIQUE.

Обсуждение
Чтобы обеспечить уникальность записей в таблице, необходимо потребовать, чтобы какой-то столбец или столбцы содержали уникальные значения в каждой строке. Если это условие выполнено, вы можете ссылаться на любую запись таблицы по ее уникальному идентификатору. Чтобы снабдить таблицу таким свойством, добавьте при создании таблицы в ее структуру индекс PRIMARY KEY или UNIQUE. Следующая таблица не содержит такого индекса, поэтому вставка дубликатов разрешена:

CREATE TABLE person
(
last_name CHAR(20),
first_name CHAR(20),
address CHAR(40)
);

Чтобы предотвратить создание в этой таблице записей с одинаковыми значениями имени и фамилии, добавьте в ее определение PRIMARY KEY. После этого необходимо еще объявить индексированные столбцы как NOT NULL, так как PRIMARY KEY не разрешает использовать значения NULL:

CREATE TABLE person
(
last_name CHAR(20) NOT NULL,
first_name CHAR(20) NOT NULL,
address CHAR(40),
PRIMARY KEY (last_name, first_name)
);

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

Для обеспечения уникальности можно добавить в таблицу индекс UNIQUE, а не PRIMARY KEY. Эти два типа индекса идентичны во всем, кроме одного: UNIQUE может создаваться для столбцов, допускающих использование значений NULL.

В таблице person вы, вероятно, будете требовать заполнения столбцов как имени, так и фамилии. Тогда столбцы можно объявить как NOT NULL, и следующее объявление таблицы будет эквивалентно предыдущему:
CREATE TABLE person
(
last_name CHAR(20) NOT NULL,
first_name CHAR(20) NOT NULL,
address CHAR(40),
UNIQUE (last_name, first_name)
);

Если индекс UNIQUE разрешает значения NULL, то NULL отличается от всех остальных значений тем, что может встречаться множество раз. Основная причина этого в том, что невозможно определить, совпадает одно неизвестное с другим или нет, поэтому разрешается использовать несколько таких значений.

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

В MySQL в таких случаях обычно используется столбец AUTO_INCREMENT:

CREATE TABLE person
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
last_name CHAR(20),
first_name CHAR(20),
address CHAR(40),
PRIMARY KEY (id)
);

При создании записи, id которой содержит NULL, MySQL автоматически присваивает этому столбцу уникальный идентификатор. Есть и другая возможность – присваивать идентификаторы внешним образом и использовать эти идентификаторы как уникальные ключи. Например, граждане какой-то страны могут иметь идентификационные номера налогоплательщиков. Тогда можно применять такие номера как уникальные идентификаторы:

CREATE TABLE person
(
tax_id INT UNSIGNED NOT NULL,
last_name CHAR(20),
first_name CHAR(20),
address CHAR(40),
PRIMARY KEY (tax_id)
);

Статьи по MySQL на эту тему:

Обработка дубликатов на этапе создания записи
Подсчет и выявление дубликатов
Удаление дубликатов из таблицы
Устранение дубликатов из результата запроса
Устранение дубликатов из результата самообъединения