C, PHP, VB, .NET

Дневникът на Филип Петров


* Изтриване на дублирани записи

Публикувано на 18 август 2014 в раздел Бази от Данни.

Дадена е таблица, в която има дублиращи се записи. Вашата задача е да изтриете всички повторения (т.е. да остане по един запис от всеки дубликат). Задачата звучи привидно тривиална. Нека създадем примерна таблица и да я напълним с информация:

CREATE TABLE people(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
)ENGINE = InnoDB;

INSERT INTO people (name)
VALUES ("georgi"), ("ivan"), ("teodor"),
       ("georgi"), ("petar"), ("ivan"), ("philip"),
       ("ivan"), ("maria"), ("petar"), ("georgi");

Стандартно бихме подходили по следния начин - DELETE заявка, в чиято WHERE клауза изреждаме id-тата на редовете, които трябва да бъдат изтрити. Тях, естествено, намираме с вложен SELECT:

DELETE FROM people
WHERE id NOT IN (
        SELECT MIN(id)
        FROM people
        GROUP BY name );

Това, за съжаление, няма да работи. Грешката, която ще се върне, ще е следната:

ERROR 1093 (HY000): You can't specify target table 'people' for update in FROM clause

Грешката говори еднозначно - не можем да използваме таблицата по която трием записи във вложената заявка.

Единият начин да се справим със ситуацията е да "излъжем" MySQL по следния начин (това е стандартна тактика при "грешки" от горния тип):

DELETE FROM people
WHERE id NOT IN (
        SELECT * FROM (
            SELECT MIN(id)
            FROM people
            GROUP BY name ) AS t1
        );

Вторият популярен начин е да се използва SELF JOIN в DELETE:

DELETE t1 
FROM people AS t1 JOIN people AS t2 
                  ON t1.name=t2.name 
WHERE t1.id > t2.id;

Съществуват (и често се дават за пример!) и други методи, като например:

  • Създаване на временна таблица, в която да се вмъкнат редовете, които са нужни, след това изпразване (TRUNCATE) на оригиналната таблица и връщане на записите от временната таблица в нея;
  • С ALTER TABLE (с IGNORE клауза) да се създаде ограничение UNIQUE по колоните, в които има дублирани записи, след което ограничението да се махне.

И двата цитирани са крайно неефективни. В първият метод ще се отнема много памет/дисково пространство и ще се прави голямо количество заявки INSERT, а във втория метод използваме DDL, за да извършваме DML задачи, което никога не е препоръчително (и винаги е бавно).

Задачата от по-горе е класическа при интервюта по фирмите. С нея много лесно се вижда кой има практически опит в решаването на подобни казуси и кой няма :)

П.П. А в крайна сметка ако не желаем да има дублиращи се записи, не трябва да го позволяваме изначало, нали?

 



6 коментара


  1. Наскоро ми се наложи да правя точно това и се оказа, че първия начин не проработи при мен. Като потърсих причината поради, която не работеше попаднах на това, че понякога mysql прави оптимизации и реално затрива излишната заявка SELECT *.
    А може ли някакъв пример с втория начин, защото не мога да си обясня как работи ?

  2. DELETE t1
    FROM people AS t1 JOIN people AS t2
    ON t1.name=t2.name
    WHERE t1.id > t2.id;

    Не мога да разбера този начин.

  3. SELECT *
    FROM people AS t1 JOIN people AS t2
    ON t1.name=t2.name
    WHERE t1.id > t2.id;

    Това разбираш ли го? Имаме два пъти таблицата people - веднъж се казва t1 и веднъж t2. Между тях правим SELF JOIN - избираме такива редове от t1 и t2, които да удовлетворяват t1.name=t2.name и t1.id > t2.id. Второто условие е важно, защото то ще гарантира, че най-малкото id в t1 ще остане незасегнато. Двойките редове, които удовлетворяват това, формират резултатната таблица. SELECT * казва "извади всичко". Изпробвай го.

    Сега сменяме SELECT * с DELETE t1. И там казваме - изтрий редовете САМО ОТ t1, които бяха намерени. И те ще бъдат изтрити.

  4. Вземи да направиш постовете за бази данни на едно ръководство (учебник). Няма по-добри материали на български за бази данни. Други хора преподават, но са само на слайдове. Няма от къде да си хванеш да си четеш. 4 пъти съм чел статиите. Първия път учих. Другите пъти преговарях. Това го казвам като комплимент (съвет).

Добави коментар

Адресът на електронната поща няма да се публикува


*