MySQL Видалити всі записи-двійники з таблиці

Іноді при роботі з базами даних постає задача позбутися "двійників". "Двійники" - це записи, які несуть одне і те саме смислове навантаження, являють собою одну і ту саму сутність, але їх більше ніж один.

Одною з причин появи "двійників" є неправильна структура даних. Краще не робити неправильно, щоб потім не довелося те виправляти.

Чим погані "двійники", окрім захаращення бази? Система потребує роботи з однією сутністю, як із чимось єдиним. "Двійник" може призвести до того, що у одному місці система буде звертатися до одного з "двійників", а у іншому - до іншого. Наприклад, записувати зміни до одного з них, а считуватиме з іншого. У результаті користувач буде дивуватися, що він вносить зміни, вони, ніби, зберігаються, але це не змінює роботи механізму.

Або ти захочеш оптимізувати таблицю перепризначивши поле первинного ключа. Звісно, наявність більше ніж одного однакового значення у ключовому полі неприпустима і не дозволить тобі здійснити задумане.

Отже, перед нами задача: позбутися двійників.

Безперечно, задача не нова. Я і не претендую на новизну. Просто я вже вдруге чи втретє стикаюся із подібною задачею, ось і вирішив її виписати, щоб мені було зручно знайти це рішення, якщо знову виникне така потреба. А якщо це ще комусь допоможе, окрім мене - то буде і взагалі добре!

Ситуація:

У таблиці `table` зараз ключове поле `table_id` (число, auto_inctement), а за смислом має бути `table_key`.

Двійників я вирішив позбуватися таким чином:

1. Повторювані записи це записи, для яких count(DISTINCT `table_key`) < count(`table_key`) - отримаємо від них їхні `table_id`.
2. Залишаємо з них ті, у яких найменше значення `table_id` (або інша ознака, за якою можна визначити, який із "двійників" правильніший)
3. Всі інші - видаляємо

А тепер реалізація:

1. Я написав ось такий запит для першого кроку:
   

   SELECT
       min(a.`table_id`) table_id,
       a.`table_key` table_key,
       COUNT(a.`table_key`) cnt,
       COUNT(DISTINCT a.`table_key`) cntd
   FROM 
      `table` a
   GROUP BY
      a.`table_key`
   HAVING
      cntd < cnt

2. Результат цього запиту вважатимемо таблицею-фільтром `table_1`. Залишимо тільки ті з двійників, `table_id` яких потрапив до таблиці-фільтру `table_1`. Отже для видалення треба отримати список всіх двійників, у яких `table_key` міститься у таблиці-фільтрі, а `table_id` - не міститься в ній.
   

   SELECT c.`table_id`
   FROM
   	`table_1` b,
   	`table` c
   WHERE
   	b.`table_key`=c.`table_key`
   	and r.`table_id`!=c.`table_id`

3. Результат попереднього запиту будемо вважати таблицею "приречених" `table_2`. Тепер видаляємо з таблиці `table` всі записи, у яких `table_id` потрапив до `table_2` (таблиці "приречених").
   

   DELETE e
   FROM `table` e, `table_2` d
   WHERE e.`table_id`=d.`table_id`

А тепер - все одним триповерховим запитом:

DELETE e
FROM
	`table` e,
	(
		SELECT c.`table_id`
		FROM
			(
				SELECT
					min(a.`table_id`) table_id,
					a.`table_key` table_key,
					count(a.`table_key`) cnt,
					count(distinct a.`table_key`) cntd
				FROM 
					`table` a
				GROUP BY
					a.`table_key`
				HAVING
					cntd<cnt
			) b,
			`table` c
		WHERE
			b.`table_key`=c.`table_key`
			and r.`table_id`!=c.`table_id`
	) d
WHERE
	e.`table_id`=d.`table_id`

Цей запит видаляє всі записи, у яких `table_key` повторюється. Залишає лише ті, у яких `table_id` - мінімальне. Тепер таблиця готова до того, щоб `table_key` стало ключовим полем, а отже, таблиця стала доступною до оптимізації, а значить, база - наблизилася до краси і досконалості :-)