MySQL DELETE JOIN

Składowanie danych w kilku tabelach połączonych relacyjnie to bardzo dobry pomysł. Chyba najprostszym przykładem jest forum dyskusyjne: struktura oraz content postów mogą spokojnie być trzymane w osobnych tabelach. To samo tyczy się danych użytkowników. Rekordy rozbite na kilka tabel stają się mniej rozbudowane, o ile w ogóle występują – istnienie zawartości pola nie jest wtedy wymagane (użytkownik nie podał danych = nie ma rekordu).

Zaznaczając JOIN’ujemy tabele w zależności od potrzeb, co zdarza się bardzo często. O UPDATE JOIN już wspominałem, też bardzo wygodna operacja, natomiast, co w przypadku, gdy musimy usunąć rekord uzależniony od wartości pola w innej tabeli? Sprawa jest banalnie prosta.

Na początek kilka technicznych uwag, na które łatwo można się nadziać:

  • Najczęściej będziemy mieli przypadek, w którym wartość pola musi być znana = rekord w tabeli obok musi istnieć. Nie zapomnijmy o pełnym złączeniu tabel INNER JOIN.
  • Gdy czyścimy śmieci w bazie danych, chcemy, aby wartość pola była konkretna lub niezdefiniowana, tabele możemy złączyć lewostronnie LEFT JOIN.
  • Składnia DELETE FROM jest bardzo podobna do SELECT. Zaznaczamy alias_tabeli.* jako wybór rekordu do usunięcia. Możemy usuwać rekordy z kilku tabel oddzielając zaznaczenia przecinkami. Przy JOIN’ach wymagane jest zdefiniowanie aliasów i sprecyzowanie, co chcemy usunąć alias_tabeli.* (edit: nie jest wymagane definiowanie aliasów, przykłady niżej)

Przykłady.

Dane userów mam składowane w dwóch tabelach – w jednej podstawowe dane (id, name, pass, pass_salt, mail, status_active), w kolejnej dane (data [jako handler user -> user_data], data_* [* – jakieśdane]). Chcę usunąć wszystkich użytkowników, którzy zarejestrowali się przed 48-godzinami i nie aktywowali swoich kont, aby zwolnić unikalne nazwy użytkowników i adresy email. Jednym kryterium jest user_status_active z tabeli users, kolejnym jest data user_data_join z tabeli users_data. Jako, że mam założony kaskadowy foregin key na pole user_data w tabeli users_data, przy usunięciu rekordu z tabeli users pozbędę się również jego danych, o co dbać nie muszę przy wypisywaniu alias_tabeli.*. W przypadku, kiedy nie miałbym założonego foregin key, musiałbym obsłużyć usunięcie rekordu z users_data wypisując po przecinku tabelę. Zatem:

DELETE item.* FROM `cms_members` AS `item`
INNER JOIN `cms_members_data` AS `item_data` ON (item.user_id = item_data.user_data)
WHERE item.user_state_active = 0 AND item_data.user_data_join < NOW()

~Tiraeth przesłał rozwiązanie beż użycia aliasów i słowa kluczowego JOIN, odwołujemy się po nazwie tabeli:

DELETE cms_members.* FROM `cms_members`, `cms_members_data`
WHERE cms_members.user_id = cms_members_data.user_data AND user_state_active = 0 AND user_data_join < NOW()

Podzapytanie oraz INNER JOIN generuje nam iloczyn kartezjański:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

Mam nadzieję, że komuś się przyda.

 

4 thoughts on “MySQL DELETE JOIN

  1. A nie prościej byłoby skorzystać z kluczy obcych? Usuwasz użytkownika, a resztą zajmuje się baza danych.

  2. @batman: Tak też to zostało zrobione (jak opisałem w notce, wystarczy uważniej czytać). Chodzi natomiast o użycie pól spoza tabeli przy DELETE do zdefiniowania, które to rekordy mają zostać wyrzucone. Powiązaniami zajmują się foreign keys.

  3. „Przy JOIN’ach wymagane jest zdefiniowanie aliasów” – czemu miałbym definiować aliasy, gdy mogę użyć pełnej nazwy tabeli? I to tak naprawdę w miejscu definiowania co usuwam (DELETE … FROM) i ewentualnie, gdy powtarzają mi się nazwy kolumn. Oracle nigdzie nie wspomina, że wymagane jest definiowanie aliasów, chyba, że coś przeoczyłem.

    Założmy strukturę user(id, active) oraz details(id, user_id, created_at) z kluczem obcym user_id -> user(id) z ON DELETE CASCADE. Zapytanie „DELETE user.* FROM user INNER JOIN details ON user_id = user.id WHERE active = 0 AND created_at < NOW()" wykona się bez problemu (bo niby czemu nie miałoby się wykonać). Ale więcej, zapytanie:

    DELETE user.* FROM user, details WHERE user.id = user_id AND active = 0 AND created_at < NOW()

    Także zadziała i da ten sam rezultat (domyślnie subquery daje nam iloczyn kartezjański, tj. INNER JOIN) i warto było o tym wspomnieć.

Comments are closed.