Forum PHP.pl > deduplikacja rekordów bez kopiowania tabeli

Pomoc - Szukaj - Użytkownicy - Kalendarz

Pełna wersja: deduplikacja rekordów bez kopiowania tabeli

furious_knight

12.10.2012, 08:46:11

Witam wszystkich,
Potrzebuję porady kogoś kto siedzi w SQL i ma pomysł jak rozwiązać mój problem....
Mam w pracy bazę danych która ma 2mln rekordów!! bazę wgrywałem kilka dni partiami z różnych źródeł i muszę przeprowadzić deduplikację przed oddaniem jej do szefa.
Baza składa się z około 35 kolumn i mogą zdarzyć się przypadki że rekordy mają taką samą nazwę, adres, nip, itd. różnią się tylko np. telefonem albo mailem. Jedynym co na pewno ich różni to numer id w bazie... jedynym (moim)zdaniem sposobem jest usunięcie rekordów które są identyczne w całości wykluczając pole ID czy zna ktoś przepis na takie zapytanie?? coś w stylu -- usuń rekord jeżeli rekord minus [pole id] istnieje w bazie.

ps. wykonanie kopii bazy i porównanie odpada bo zajęło by to co najmniej 2 dni!

sowiq

12.10.2012, 08:57:14

Mała podpowiedź, która Cię nakieruje:

[SQL] pobierz, plaintext 
SELECT *, COUNT(1) AS count
FROM tabela
GROUP BY pole1, pole2, pole3...
HAVING count > 1
[SQL] pobierz, plaintext

To Ci zwróci po jednym rekordzie dla każdego z duplikatów w kolumnach, po których grupujesz + jako count ilość duplikatów. Dalej już łatwo.

Może i istnieje łatwiejszy sposób, ale ja go nie znam

furious_knight

12.10.2012, 09:35:03

dzięki sowiq świetny sposób żeby wyświetlić listę rekordów identycznych, wyskoczyło mi 800 rekordów a praca na takiej ilości jest już o wiele prostsza:) kilka prostych operacji i dubli nie ma, dzięki za dobrą poradę

pozdrawiam

redeemer

12.10.2012, 09:53:55

Można by jeszcze pokombinować z indeksem UNIQUE.

[SQL] pobierz, plaintext 
ALTER IGNORE TABLE `tabela` ADD UNIQUE INDEX (pole1, pole2, ...)
[SQL] pobierz, plaintext

To powinno założyć indeks i usunąć duplikaty.

sowiq

12.10.2012, 10:03:19

Założenie indeksu nie usuwa duplikatów. Dostaniesz jedynie błąd, że nie można założyć indeksu, bo istnieją duplikujące się wartości:

[SQL] pobierz, plaintext 
#1062 - Duplicate entry 'xxx' for key 'yyy'
[SQL] pobierz, plaintext

redeemer

12.10.2012, 10:12:48

Cytat(sowiq @ 12.10.2012, 11:03:19 )

Założenie indeksu nie usuwa duplikatów. Dostaniesz jedynie błąd, że nie można założyć indeksu, bo istnieją duplikujące się wartości:

[SQL] pobierz, plaintext 
#1062 - Duplicate entry 'xxx' for key 'yyy'
[SQL] pobierz, plaintext

Dlatego jest tam IGNORE

sowiq

12.10.2012, 12:36:40

To teraz gdzie jest haczyk?

Próbowałem dla testu założyć taki indeks na swojej tabelce i wyrzuciło błąd zduplikowanych danych. Rozumiem, że nie jest warunkiem ilość kolumn większa niż 1. Jeśli ma to jakieś znaczenie, to typ tabelki to InnoDB

redeemer

12.10.2012, 12:49:28

Jaką masz wersję MySQL? Na 5.1+ powinno działać:

[SQL] pobierz, plaintext 
CREATE TABLE test (
 a INT,
 b INT,
 c INT
) TYPE=innodb;
 
INSERT INTO `test` VALUES (1,2,3), (1,2,4), (2,3,3), (2,4,4), (2,4,5);
[SQL] pobierz, plaintext

[SQL] pobierz, plaintext 
SELECT * FROM `test`;
[SQL] pobierz, plaintext

Kod

a           b           c
1           2           3
1           2           4
2           3           3
2           4           4
2           4           5

[SQL] pobierz, plaintext 
ALTER IGNORE TABLE `test` ADD UNIQUE INDEX (a, b);
[SQL] pobierz, plaintext

[SQL] pobierz, plaintext 
SELECT * FROM `test`;
[SQL] pobierz, plaintext

Kod

a           b           c
1           2           3
2           3           3
2           4           4

sowiq

12.10.2012, 13:06:57

Wiem, że powinno działać, dlatego pytam

Kod

mysql> SHOW VARIABLES LIKE "version";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.5.16 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> ) ENGINE=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO `test` VALUES (1,2,3), (1,2,4), (2,3,3), (2,4,4), (2,4,5);
Query OK, 5 rows affected (0.11 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `test`;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
|    1 |    2 |    4 |
|    2 |    3 |    3 |
|    2 |    4 |    4 |
|    2 |    4 |    5 |
+------+------+------+
5 rows in set (0.00 sec)

mysql> ALTER IGNORE TABLE `test` ADD UNIQUE INDEX (a, b);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'a'

redeemer

12.10.2012, 13:18:34

http://mysqlolyk.wordpress.com/2012/02/18/...ys-give-errors/ (oraz http://bugs.mysql.com/bug.php?id=40344 )

Ciężko mi to jednak teraz sprawdzić, bo w tej chwili nie mam dostępu do 5.5.

sowiq

12.10.2012, 13:40:12

Heh, człowiek uczy się całe życie. A ja jeszcze się nie nauczyłem, że jak coś działa pod 5.1 to nie znaczy, że będzie działało równie dobrze pod 5.5

Dzięki za przekazanie trochę wiedzy

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.