MySQL / 8. Изменение таблицы с помощью предложения ALTER TABLE

Удаление дубликатов путем добавления индекса

Задача
В таблице есть повторяющиеся записи, а вы хотели бы от них избавиться.

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

Обсуждение
Если при создании индекса PRIMARY KEY или UNIQUE MySQL обнаруживает повторяющиеся значения ключа, операция ALTER TABLE прерывается. Чтобы игнорировать дубликаты и продолжать работу, используйте ALTER IGNORE TABLE вместо ALTER TABLE. Ключевое слово IGNORE указывает MySQL, что следует сохранить первое из повторяющихся значений ключа и отбросить все остальные. На самом деле это удобный способ удаления дубликатов из столбца или множества столбцов. Просто создайте индекс с уникальными значениями и
позвольте MySQL «выбросить» все дубликаты.

Чтобы посмотреть, как работает IGNORE, удаляя повторения, используем таблицу mytbl, в которой больше нет индексов (если вы выполнили все рассмотренные ранее преобразования). Начнем со вставки в таблицу нескольких одинаковых значений:

mysql> INSERT INTO mytbl (i,c) VALUES(1,'a'),(1,'a'),(1,NULL),(1,NULL),
-> (2,'a'),(2,'a'),(2,'b'),(2,'b');
mysql> SELECT * FROM mytbl;

+---+------+
| i | c         |
+---+------+
| 1 | a       |
| 1 | a       |
| 1 | NULL |
| 1 | NULL |
| 2 | a       |
| 2 | a       |
| 2 | b       |
| 2 | b       |
+---+------+

Теперь предположим, что вы хотите создать уникальный индекс, содержащий столбцы i и c. Индекс PRIMARY KEY использовать нельзя, так как c содержит значения NULL. Вы можете создать индекс UNIQUE, но если попытаетесь сделать это без IGNORE, то получите ошибку:

mysql> ALTER TABLE mytbl ADD UNIQUE (i,c);
ERROR 1062 at line 1: Duplicate entry '1-a' for key 1

Добавьте в предложение ключевое слово IGNORE, затем используйте SELECT, чтобы посмотреть на содержимое таблицы после удаления дубликатов:

mysql> ALTER IGNORE TABLE mytbl ADD UNIQUE (i,c);
mysql> SELECT * FROM mytbl;

+---+------+
| i | c         |
+---+------+
| 1 | NULL |
| 1 | NULL |
| 1 | a       |
| 2 | a       |
| 2 | b       |
+---+------+

Повторяющиеся записи были удалены, за исключением тех, что содержат значения NULL в ключевых столбцах. Дело в том, что индексы UNIQUE допускают множественные значения NULL.

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

Использование предложения ALTER TABLE для нормализации таблицы