Indeksowanie baz danych, funkcje mieszające

Występowanie złożonych baz danych jest coraz bardziej popularne, a komercyjne rozwiązania praktykują składowanie informacji nie tylko na pojedynczych bazach, przestrzeniach, dyskach, czy nawet serwerach. Szybki dostęp do danych to podstawa, dlatego pochylimy się nad czysto teoretycznym problemem dostępu do informacji, które wprawdzie są rozwiązane i zaszyte w mechanizmach poruszania się po większości baz, natomiast ich znajomość pozwoli dodatkowo zoptymalizować struktury, które projektujemy. Z góry podkreślam, że artykuł jest bynajmniej wyczerpujący, specjalistycznej i bardziej szczegółowo zarysowanej teorii baz danych należy szukać w publikacjach i tu zachęcam do odwiedzenia politechnicznych bibliotek.

Baza danych jako zbiór informacji powinna oferować trzy podstawowe operacje:

  • Szukanie jako dostęp do pojedynczego, unikatowego „obiektu” (zazwyczaj rekordu) w bazie danych.
  • Wyszukiwanie jako dostęp do elementów spełniających dane kryteria.
  • Modyfikacja danych.

Pojęcie klucza

Kluczem w bazie danych jest atrybut każdego elementu/rekordu jakiejś klasy (np. pojedynczej tabli danych), który pomoże go zidentyfikować przy szukaniu lub wyszukiwaniu w sposób jednoznaczny (wtedy mówi się o kluczu podstawowym Primary Key) lub niejednoznaczny (wtedy mówi się o indeksie Index). Za pomocą klucza jesteśmy w stanie dostać się do rekordu przeszukując tylko strukturę indeksów, zamiast samą bazę.

Skrócenie drogi dostępu

Przy szukaniu konkretnego elementu, którego unikalny atrybut jest z góry znany używamy właśnie kluczy podstawowych. Jest to gigantyczne przyspieszenie procesu wyszukania elementu. Jak wiemy, niektóre klucze zachowują pewną prawidłowość, na przykład stale rosną. Najprostszym przykładem jest identyfikator rekordu, którego wartość zazwyczaj się inkrementuje. Klucze to nic innego jak para informacji: wartość klucza oraz adres komórki pamięci, do której klucz należy.

Wykorzystanie klucza podstawowego to wskazanie miejsca ulokowania rekordu w dowolnej pamięci (na przykład adresu pamięci, adresu na dysku, offset w pliku, itd.)

Uporządkowany zbiór kluczy podstawowych

Co nam daje uporządkowany zbiór kluczy? Żeby dowiedzieć się, gdzie w pamięci jest ulokowany nasz rekord, najpierw trzeba dostać się do wartości klucza. W przypadku uporządkowanego zbioru danych wartości kluczy możemy w łatwy sposób go odnaleźć, na przykład metodą połowienia zbioru lub dostępu do wcześniej ułożonego drzewa. Najoptymalniejsze do odszukiwania jest drzewo ważone, ponieważ w przypadku nieważonego przy stale wzrastającej wartości klucza przy prawidłowości, że prawe gałęzie drzewa mają wartości większe, drzewo rosłoby tylko w jedną stronę, a w rezultacie otrzymalibyśmy listę, w której odszukiwanie nie jest optymalnym rozwiązaniem. Ważenie drzewa nie jest jednak rozsądnym rozwiązaniem w bazach danych, w których jest więcej żądań (a właściwie czasu propagacji) do zapisu danych, niż odczytu.

Suma sumarum, w zależności od typu bazy (relacyjna, obiektowej, strumieniowej, itd.) oraz jej złożoności, należy wybrać odpowiedni mechanizm układania indeksów.

Funkcja mieszająca

Rodzi się pytanie. Gdy mamy taką strukturę składowania indeksów niejednoznacznych, tj. kilka rekordów może mieć dokładnie taką samą wartość klucza. Prostym przykładem jest indeksowanie dłuższych ciągów znaków, do których chcemy mieć natychmiastowy dostęp bez wyszukiwania ich w bazie danych w sposób bezpośredni. Do identyfikacji takich struktur służą funkcje mieszające. Przykład. Wyobraźmy sobie, że podczas zapisu danych podając dane jako argument funkcji mieszającej, zamieniamy każdy znak ciągu znaków na odpowiadający mu kod ASCII, następnie sumujemy liczby i dzielimy modulo 90, nasz wynik to wartość indeksu. Tę samą operację wykonujemy dla kryterium późniejszego wyszukiwania podając go jako argument funkcji mieszającej. Wystarczy porównać nasze kryterium z kluczami. Mamy 90 możliwości otrzymanych wyników.

Działanie funkcji mieszającej:

Im większe modulo, tym bardziej rozległy indeks i bardziej unikatowy indeks. Niestety, pod jednym kluczem może znajdować się wiele rekordów, przykładowo: ABC, CAB, AAD, AE, F, itd… wówczas występuje tzw. kolizja. Podstawową wadą funkcji mieszającej może być złożoność obliczeniowa dla zwracanej wartości. Ponadto obecne systemy baz danych zapewniają ciągłość z góry zadeklarowanej pamięci, zatem przeszukiwanie takich komórek może być znacznie szybsze, od przeszukiwania kluczy. Funkcja mieszająca plus indeksowanie adresów jest zdecydowanie dobrym rozwiązaniem, gdy przeszukiwanie indeksów jest korzystniejsze pod względem czasu dostępu do informacji (np. czas propagacji dysku, odszukanie fragmentu pliku, etc.).

Występowanie kolizji

Metod obsługi kolizji jest bardzo wiele. Podstawową jest stworzenie listy elementów, które są przypisane do danego klucza. Może ich być wiele, natomiast to i tak bardzo dobra optymalizacja przeszukiwania bazy danych.

Częstotliwość występowania kolizji w grubym przybliżeniu obrazuje wykres. Zauważmy, że jeżeli wyczerpiemy ~60% możliwości wystąpienia tych samych kluczy, kolizyjność wzrasta wykładniczo, a używanie funkcji mieszących przy wstawianiu rekordu bazy danych staje się nieoptymalne, w zależności od implementacji korekcji kolizji (powtórzenia rozwiązania kolizji). Przy dołączaniu elementu do listy jednokierunkowej (wcześniejszy obraz) nie stanowi to jednak większego problemu. Gdy < 60% możliwości kluczy jest niewykorzystanych, występowanie kolizji jest znikome.

Idealna funkcja mieszająca

Mówiąc o idealnej funkcji mieszającej mamy na myśli skonstruowanie takiej funkcji, która przyporządkuje mniej więcej po tej samej liczbie swoich zwracanych wartości, tj. dla naszego przykładu modulo 90, każdy klucz będzie miał porównywalną liczbę rekordów przypisanych do danego indeksu. Intuicyjnie: można to wykonać tylko wtedy, kiedy z góry znamy dziedzinę tej funkcji bądź w przybliżeniu spodziewamy się znanych danych wejściowych. Budowanie idealnych funkcji mieszających jest skomplikowaną operacją matematyczną. Jednym ze sposobów do naszego przykładu, przy znanej dziedzinie funkcji mieszającej jest przypisywanie kolejnym literom wag, które po zsumowaniu i podzieleniu przez modulo, jest wygenerowanie takiej kombinacji wag, żeby zwracane wartości były równie często obliczane dla całej dziedziny funkcji (każda liczba modulo jest wykorzystywana po mniej więcej N razy).

Zakończenie

Temat teoretycznych rozważań budowy baz danych na pewno będę kontynuował. Tak, jak zaznaczyłem we stępie, artykuł bynajmniej wyczerpuje tematykę, a zainteresowanych zapraszam do przekroczenia progów politechnicznych bibliotek.

 

Optymalizacja zapytań MySQL dla koniunkcji wielu danych

Nie raz, nie dwa mieliśmy sytuację, która wymagała od nas koniunkcji warunków większej ilości danych lub dane te były tekstowe, ale niedługie. Niby nic, klucze załatwiają sprawę, ale sięgając do kodu gry bukmacherskiej, musiałem ją nieco zoptymalizować pod względem częstego wyciągania danych. Baza rozrosła się dość szybko, dlatego niezbędna była lekka modyfikacja jej struktury.

Moim zadaniem było bardzo częste wyciągnięcie ID meczu, który musiał na raz (AND) być zgodny z żądaną datą, nazwą drużyny pierwszej oraz drugiej. Informacji do warunków dostarczał system. Oprócz daty, są to dane tekstowe, więc połączyłem je ze sobą CONCAT i stworzyłem z nich sumę md5. Indeks, po którym baza szukała, był już krótszy od warunków, bo zawierał zawsze 32 znaki. Pierwszym warunkiem koniunkcji zawsze była suma md5 wymienionych wcześniej pól rekordu, nazwałem to suma kontrolna rekordu, potem faktyczna wartość pól, aby w razie zdublowania sumy kontrolnej (czego się nie spodziewamy, bo zakres wariacji jest ogromny, ale dla idei) wybrać prawidłowy rekord. Do tej pory wystarczało…

Gdy baza rozrasta się, problemem staje się wyszukiwanie. O ile suma kontrolna to już krok w stronę optymalizacji, dla >100k rekordów, baza danych potrzebowała co najmniej 0.05 sekundy na zwrócenie wyniku. Postanowiłem dodać odcisk palca sumy kontrolnej. Najlepszym rozwiązaniem okazało się dodanie jednego bajtu, który zrobił magię w bazie danych. Jedno pole TINYINT – 8 bitów, zakres 0-255 bez znaku. Założenia odcisku palca:

  • jest wartością liczbową oraz zajmuje tylko jeden bajt, aby oszczędzić miejsca w rekordach oraz indeksach bazy danych,
  • nie musi być uniwersalny (unikalny), a jedynie grupować odciski palców w mniejsze, a liczniejsze zbiory.

Rozwiązanie, które zastosowałem przy generowanu odcisku palca sumy kontrolnej, również nie jest skomplikowane:

  1. Odcisk palca to suma kolejnych znaków sumy kontrolnej rekordu, gdzie 0 – 9 zachowują swoje wartości, a litery [a-f] przyjmują kolejno [10-15], dokładnie jak w przeliczaniu pojedynczych wyrazów systemu liczbowego o podstawie 16 (HEX) na dziesiętny.
  2. Skoro jest to suma, to wartość minimalna jest dla samych zer, zatem MIN = 0.
  3. Wartość maksymalną można stworzyć podając same maksymalne wartości F, zatem MAX = 480.
  4. 480 mieści się na 9 bitach (min. 2 bajty, zakres 0-65535 bez znaku, tracimy 65055 wartości), dzieląc liczbę przez 2 tracimy unikalność odcisku dwukrotnie, ale zmieścimy się na ośmiu bitach, czyli jednym bajcie – możemy użyć typu TINYINT (zakres 0-255 bez znaku, nasza to 0-240), zatem tracimy tylko 15 niewykorzystanych wartości.

Przeprowadzamy testy naszego rozwiązania.

Stwórzmy przykładową tabelę danych test_md5_index, która będzie przechowywała wartości tekstowe w polach data_content, data_content2, data_content3. Tabela może zawierać pole dodatkowe, ale te trzy będziemy wykorzystywać w naszym wyszukiwaniu. Ważnym jest to, że warunkiem jest koniunkcja (AND), dlatego możemy stworzyć sumę (analogicznie do sumy logicznej) md5 jako odcisk palca tych pól, który zapiszemy w data_sum varchar(32). Dodatkowo stworzymy odcisk palca odcisku palca – jednobajtowe pole data_sum_index TINYINT.

Od razu zakładamy klucz podstawowy na data_id oraz klucz dla zapytania, który będzie go wykorzystywał, czyli szukanie wspólnie po data_sum_index oraz data_sum.

CREATE TABLE test_md5_index (
  data_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  data_sum_index tinyint(1) UNSIGNED NOT NULL,
  data_sum VARCHAR(32) NOT NULL,
  data_contents text NOT NULL,
  data_contents2 text NOT NULL,
  data_contents3 text NOT NULL,
  PRIMARY KEY (data_id),
  KEY data_index (data_sum_index, data_sum)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Pora stworzyć funkcję, która przeliczy nam nowy, krótszy odcisk palca na podstawie poprzedniego:

CREATE FUNCTION TestIndexChecksum(sSum VARCHAR(32)) RETURNS TINYINT
BEGIN
 
  DECLARE sSumPart VARCHAR(1);
  DECLARE iSumPart TINYINT;
  DECLARE iSum SMALLINT DEFAULT 0;
  DECLARE i INT;
 
  IF (SELECT sSum NOT REGEXP '^([a-z0-9]){32}$') THEN RETURN 0; END IF;
 
  SET i = 1;
 
  WHILE i &lt;= LENGTH(sSum) DO
    SET sSumPart = SUBSTR(sSum, i, 1);
    SET iSumPart = (SELECT (CASE WHEN sSumPart = 'a' THEN 10 WHEN sSumPart = 'b' THEN 11 WHEN sSumPart = 'c' THEN 12 WHEN sSumPart = 'd' THEN 13 WHEN sSumPart = 'e' THEN 14 WHEN sSumPart = 'f' THEN 15 ELSE 0 END));
 
    IF iSumPart = 0 THEN
      SET iSumPart = sSumPart;
    END IF;
 
    SET iSum = iSum + iSumPart;
    SET i = i + 1;
  END WHILE;
 
  RETURN iSum / 2;
END;

Aby przeprowadzać testy, stwórzmy sobie procedurę, która wstawi nam N losowo, jakkolwiek wypełnionych rekordów do bazy danych:

CREATE PROCEDURE TestIndexesPrepareTest(IN i INT)
BEGIN
  TRUNCATE TABLE test_md5_index;
 
  WHILE i &gt; 0 DO
 
    INSERT INTO test_md5_index SET
      data_contents  = (SELECT REPLACE(CONCAT(RAND() * 32), ".", "")),
      data_contents2 = (SELECT REPLACE(CONCAT(RAND() * 32), ".", "")),
      data_contents3 = (SELECT REPLACE(CONCAT(RAND() * 32), ".", "")),
      data_sum = CONCAT(data_contents, data_contents2, data_contents3),
      data_sum_index = TestIndexChecksum(data_sum);
 
    SET i = i - 1;
  END WHILE;
END;

Po wykonaniu CALL TestIndexesPrepareTest(100000) mamy przygotowane małe środowisko testowe.

Przygotujmy kilka zapytań do bazy danych, wybieramy losowy rekord, na którym będziemy testowali wyniki. Wykonujemy zapytanie z ręcznie wpisaną wartością warunku wybranego rekordu, sprawdźmy, jak szybko zostanie odnaleziony:

SELECT * FROM test_md5_index WHERE data_sum = "24045771412594250684228176888212";

Average: ~0.0506 sec

SELECT * FROM test_md5_index WHERE data_sum_index = 68 AND data_sum = "24045771412594250684228176888212";

Average: ~0.0004 sec (UWAGA! Specjalnie w warunku nie użyłem zwróconej warości funkcji, tylko dałem ją na sztywno, ręcznie wpisaną – funkcja by była wykonywana dla każdego porównania rekordu z osobna!).

Nasze zapytanie działa znacznie szybciej (~120 razy dla 100k rekordów) kosztem niewielkiej pamięci – po 1 bajcie do rekordu oraz po 1 bajcie do jego indeksu.

Zapewne istnieją szybkie silniki indeksowania danych, natomiast, gdy jesteśmy skazani np. na InnoDB z założeń technicznych – nie oznacza, że się nie da.

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

 

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.

 

MySQL: remove duplicate entries/rows

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:

  1. 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.
  2. 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.
  3. 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ą.

 

MySQL UPDATE JOIN

Ostatnimi czasy potrzebowałem danych z sąsiedniej tabeli przy UPDATE jedngo z pól w bazie danych. Danych do przetworzenia było sporo, więc zwracałem uwagę na wydajność zapytania. Aby zebrać potrzebne informacje, można użyć jednego ze sposobów:

  1. Zebrać potrzebne dane za pomocą SELECT‘a, co sprawiłoby, że zajęta zostanie niepotrzebna pamięć w środowisku PHP podczas przypisania rezultatu do zmiennej.
  2. Wykonać SET z podzapytaniem, ale potrzebnych mi było kilka kolumn z sąsiedniej tabeli, podzapytanie może zwrócić tylko jedną określoną wartość.
  3. Wykonać JOIN przy update, czego niestety wówczas nie potrafiłem zrobić.

Kartkując manual nie natrafiłem się w standardowej dokumentacji na nic konkretnego, aż nie spojrzałem na bardzo przydatne komentarze użytkowników. Okazało się, że przy UPDATE można wykonywać dowolne JOIN‘y, schemat jest następujący:

UPDATE TABLE JOIN another_table SET ...

W tym momencie mamy do dyspozycji wszystkie pola z dołączonej tabeli. Bardzo przydatne.

Przykład z życia.

Miałem za zadanie odznaczyć typy bukmacherskie na trafione, nietrafione, odwołane z przyczyn odwołania całego meczu piłkarskiego oraz te, które jeszcze nie mogą zostać oznaczone jako trafione lub nie, gdyż mecz się jeszcze nie odbył.

UPDATE typer_tickets_items
LEFT JOIN typer_events ON(event_id = item_event)
SET item_status = (
CASE
WHEN event_status IS NULL THEN NULL # mecz nie zostal rozegrany
WHEN event_status = -1 THEN -1 # mecz anulowany
WHEN event_status = item_bet THEN 1 # typ trafiony
ELSE 0 END # typ nietrafiony
) WHERE item_status IS NULL

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

 

Przypadki w MySQL – CASE WHEN THEN ELSE END

Podobnie jak w PHP, baza danych MySQL ma odpowiednik if, czyli przypadków (inaczej serii warunków, instrukcji warunkowych). Różnicą między implementacją CASE‘a w MySQL i ifa PHP jest to, że baza danych zwraca konkretną wartość z case’a, a nie wykonuje dowolnej ilości dowolnych akcji.

CASE Syntax:

Najprostsza struktura CASE’aprzedstawia się nastepująco:

CASE WHEN [conditions] THEN ... ELSE ... END

Składnia powinna rozpocząć się słowem kluczowym CASE, a zakończyć END. Pomiędzy znajdują się warunki WHEN oraz operacja zwrócenia odpowiedniej wartości, która po nich następuje THEN (mamy możliwość uwzględnić nieskończenie wiele warunków). Jeżeli żaden warunek nie zostanie spełniony możemy użyć opcjonalnie ELSE.

Przykłady z życia.

Wyobraźmy sobie, że mamy posortować listę aukcji przedmiotów na Allegro od najtańszych, do najdroższych. Należy założyć, że są 2 typy aukcji: kup teraz i licytacja. Pole licytacji w bazie danych zawiera największą zaproponowaną kwotę przez użytkowników w procesie licytacji, a cena kup teraz ustalana jest przez sprzedającego. Są to dwa różne pola w bazie danych, a jedno kryterium sortowania, dlatego trzeba scalić cenę w jedną, wybierając odpowiednią. Musimy przewidzieć sytuację, w której aukcja jest typu kup teraz oraz licytacji, wówczas jeżeli najwyższa oferta jest większa od ceny kup teraz, wówczas wybieramy pole z największa propozycją:

SELECT (
  CASE
    WHEN (auction_type = 'bidding' OR auction_price_bid > auction_price_buynow)
      THEN auction_price_bid
    ELSE auction_price_buynow
  END) AS auction_price

Stworzyliśmy pole auction_price, po którym można sortować aukcje od najtańszej do najdroższej i na odwrót.

Mam nadzieję, że krótki wpis przyda się początkującym. Nic więcej nie trzeba opisywać, temat wydaje się co najmniej trywialny.