Kanał ATOM
Autor wpisu: Athlan, dodany: 05.07.2009 10:02, tagi: sql
Usuwając coś permanentnie z bazy danych musimy być bardzo ostrożni, bowiem przywrócenie danych jest bardzo trudne, czasem niemożliwe. Podstawową strukturę bazy danych powinno się budować na samym początku tworzenia aplikacji, z biegiem czasu rozbudowywać ją, ale unikać przebudowywania. Niestety są przypadki, gdzie trzeba przebudować jedną rzecz, co powoduje zmianę w wielu warstwach nie tyle aplikacji, co strukturze bazodanowej.
Dziś postaram się opisać, jakie kroki trzeba wykonać, aby bezpiecznie usunąć zdublowane rekordy z bazy danych nie tracąc żadnych danych:
- Tworzymy dwie kopie bazy danych lub tabel, na których będziemy pracowali. Najlepiej, aby pracować na drugiej kopii, nigdy na oryginale, a potem wdrożyć zmiany z drugiej kopii na oryginał. Przezorny zawsze ubezpieczony.
- Analiza danych w tabeli. Musimy dokładnie wiedzieć jakie są relacje między tabelami, kiedy występują JOIN’y itp. Jeżeli rekordy są zdublowane, a posiadają ustalony ID, do których odwołuje się inny rekord z sąsiedniej tabeli, trzeba będzie w niej zmienić ID rekord zdublowanego na ID “substytuta”, bądź takiego rekordu, który nie spowoduje zmian w serwisie.
- Wykonanie operacji usunięcia zdublowanych rekordów.
Po wykonaniu kroku pierwszego zabieramy się za kolejny. Jest to najważniejszy moment naszych operacji. Aby ułatwić zrozumienie problemu, podam przykład z życia. Aplikacja posiadała poważny błąd, który umożliwiał zdublowanie użytkowników, ściślej: można było zdublować username. Za każdym razem, gdy użytkownik się logował i pisał komentarze, był ich właścicielem, ale comment_author posiadały różne ID tego samego użytkownika. Zaraz po skopiowaniu bazy danych spróbowałem przepisać ID autorów komentarzy na pierwszy rekord identyfikujący użytkownika, jaki istnieje w tabeli użytkowników. Skonstruowałem zapytanie:
UPDATE cms_comments
JOIN cms_members AS user_original ON(user_original.user_id = comment_author)
SET comment_author = (SELECT user_first.user_id FROM cms_members AS user_first WHERE user_first.user_name = user_original.user_name ORDER BY user_first.user_id ASC LIMIT 0, 1)
Usunięcie zdublowanych użytkowników było już tylko formalnością. Teraz się okaże, dlaczego zależało mi na wyciągnięciu dokładnie pierwszego rekordu reprezentującego “unikalnego” użytkownika: poniższe zapytanie (ALTER IGNORE TABLE ADD UNIQUE) usunie wszystkie kolejne rekordy oznaczone jako duplicated:
ALTER IGNORE TABLE cms_members ADD UNIQUE INDEX(user_name);
Krótki komentarz z manuala do ALTER TABLE:
IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
Pisząc ostatnie posty związane z bazami danych, mam nadzieję, że komuś się przydadzą.
