Обработка дублируемых данных MySQL

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

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

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

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

Давайте попробуем пример: в таблице нет индексов и ключей PRIMARY KEY, поэтому таблица позволяет出现多条 повторяющихся записей.

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

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

Другой способ установки уникальности данных - это добавление 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 (индекс) и PRIMAY KEY (первичный ключ) в таблицу данных простым способом для удаления дублируемых записей в таблице. Метод следующий:

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