Funkcje w MySQL; sortowania ciąg dalszy

Przejdąmy teraz dalej. Bardzo możliwe, że kiedyś będziesz chciał posortować wyniki względem długości tekstu w jakimś polu. Tutaj także możesz zrezygnować z innych języków na rzecz SQL'a, gdyż posiada on (jak każdy porządny język) funkcje. Pozwalają nam one na operowanie i przetwarzanie danych jeszcze na poziomie ich pobierania! Stosować je można w każdym wyrażeniu na takich samych zasadach, jak w innych językach. Składnia wywołania jest podobna do tej z języka C - funkcje bez parametrów wywołujemy jako FUNKCJA(), natomiast z parametrami - FUNKCJA(jakis_parametr, jakis_parametr).

Funkcjami możemy posłużyć się przy sortowaniu tekstu. Kiedy chcesz posortować rekordy według długości ciągu w pewnym polu, wykorzystasz funkcję LENGTH(tekst), która zwróci długość podanego tekstu. Oto przykład, tym razem na tabeli length_sort.

CREATE TABLE length_sort (
   id smallint(10) NOT NULL AUTO_INCREMENT,
   string varchar(128),
   PRIMARY KEY (id)
);
 
INSERT INTO length_sort VALUES (1, 'to jest tekst');
INSERT INTO length_sort VALUES (2, 'tekst');
INSERT INTO length_sort VALUES (3, 'Ten fragment dzieła "Pan Tadeusz" poruszył mnie do łez');
INSERT INTO length_sort VALUES (4, 'a');
INSERT INTO length_sort VALUES (5, 'dwa miliony trzysta dwadzieścia trzy tysiące siedemset pięćdziesiąt dziewięć');

Tabela została wypełniona różnymi tekstami. Każdy z nich ma różną długość. Do realizacji naszego zamiaru użyjemy zapytania:

SELECT id FROM length_sort ORDER BY LENGTH(string)

Tutaj także możesz przerzucić funkcję do zbioru wyników, by znalazła się tam i długość tekstu.

MySQL może na tekście przeprowadzać wiele innych ciekawych operacji za pom. funkcji. Ze względów praktycznych ograniczę ich listę do minimum:

LEFT(tekst, d) - Zwraca d pierwszych liter z podanego tekstu.
RIGHT(tekst, d) - Zwraca d liter z podanego tekstu licząc od końca.
LOWER(tekst) - Zwraca tekst, w którym wszystkie duże litery zostały zamienione na małe.
UPPER(tekst) - Zwraca tekst, w którym wszystkie małe litery zostały zamienione na duże.
LTRIM(tekst) - Jeśli w tekście na początku znajdowały się jakieś spacje, zostaną one ucięte.
RTRIM(tekst) - Z funkcji tej możemy skorzystać, gdy chcemy usunąć z podanego tekstu spacje znajdujące się na końcu łańcucha (przydaje się szczególnie przy polach typu CHAR, gdyż tam jeśli tekst nie jest podanej długości, reszta uzupełniana jest spacjami).
MID(tekst, p, d) - Działa tak samo, jak funkcja substr() w PHP. Jeśli nie wiesz jednak, jak ona działa, wyjaśniam. Funkcja zwraca d znaków z podanego tekstu zaczynając od pozycji p.
MD5(tekst) - Generuje 32-znakową sumę kontrolną (hashuje) na podstawie otrzymanego tekstu algorytmem MD5. Jest on używany przez wiele skryptów do "szyfrowania" haseł, gdyż po takim ich potraktowaniu nie da się ich odtworzyć.

Funkcje można także stosować w zapytaniach innych, niż SELECT:

INSERT INTO tabela (pole1, pole2) VALUES(UPPER('tekst tekst'), LOWER('TEKST TEKST'));

Do góry

Aliasy, tworzenie relacji

MySQL jest relacyjną bazą danych. Niestety wielu (zwłaszcza początkujących) jej użytkowników zupełnie nie rozumie tego terminu. Spróbuję wytłumaczyć go na przykładzie. Załóżmy, że katalogujesz dane o rozmaitych albumach muzycznych. Utworzysz dla nich tabelkę albumy, w której chcesz zawrzeć tytuł albumu, wykonawcę, rok wydania oraz jego zawartość. Tu pojawia się jednak poważny problem - stwierdzenie, iż wszystkie albumy zawierają tę samą ilość utworów, jest absurdem. Jak zatem odwzorować ich listę w tabeli? Cóż... można utworzyć 10 pól, a gdyby utworów było więcej - dla jednego albumu tworzyłoby się dwa rekordy. Kolejny problem - gdy albumów będzie nie 10, nie 20, a np. 17? Część miejsca zmarnuje się. Podobna sytuacja wystąpi, gdyby album był tworzony przez kilku wykonawców.

Na szczęście MySQL umożliwia proste tworzenie relacji - nic nie stoi na przeszkodzie, byś utworzył osobną tabelkę dla utworów, do której dodasz pole przechowujące ID albumu, do którego tenże przynależy. Ba - możesz posunąć się jeszcze dalej - utworzyć dwie tabelki: jedną z nazwą utworu, a drugą łączącą utwór z jakimś albumem - wtedy będziesz w stanie obsłużyć sytuację, gdy jeden utwór znajdzie się na kilku albumach. Samo tworzenie tabelek to nie wszystko (wszak takie coś to da radę także na murze kredą napisać :)). MySQL daje nam bowiem możliwość operowania na wielu tabelach w jednym zapytaniu! I to jest właśnie relacja. Bez względu na to, w ilu tabelach przechowujesz dane - w prawie każdej sytuacji będziesz w stanie je pobrać jednym zapytaniem.

Zostańmy przy naszej bazie albumów. Na niej chciałbym zademonstrować działanie relacji w praktyce. Na początek przygotujmy tabelki i wrzućmy do nich jakieś dane:

CREATE TABLE `albumy` (
   `id` int(8) NOT NULL AUTO_INCREMENT,
   `tytul` varchar(64) NOT NULL DEFAULT '',
   `wykonawca` varchar(32) NOT NULL DEFAULT '',
   `rok_wydania` int(6) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
);
 
INSERT INTO `albumy` VALUES (1, 'Nightflight to Venus', 'Boney M', 1978);
INSERT INTO `albumy` VALUES (2, 'Love for sale', 'Boney M', 1977);
INSERT INTO `albumy` VALUES (3, 'Spirits Having Flown', 'Bee Gees', 1979);
 
CREATE TABLE `utwory` (
   `id` int(8) NOT NULL AUTO_INCREMENT,
   `tytul` varchar(64) NOT NULL DEFAULT '',
   `wykonawca` varchar(32) NOT NULL DEFAULT '',
   `rok_wydania` int(6) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
);
 
INSERT INTO `utwory` VALUES (1, 'Rasputin', 'Boney M', 1978);
INSERT INTO `utwory` VALUES (2, 'Nightflight to Venus', 'Boney M', 1978);
INSERT INTO `utwory` VALUES (3, 'Daddy Cool', 'Boney M', 1976);
INSERT INTO `utwory` VALUES (4, 'Tragedy', 'Bee Gees', 1979);
INSERT INTO `utwory` VALUES (5, 'Too much heaven', 'Bee Gees', 1979);
 
CREATE TABLE `album_utwor` (
   `album_id` int(8) NOT NULL DEFAULT '0',
   `utwor_id` int(8) NOT NULL DEFAULT '0',
   KEY `album_id` (`album_id`,`utwor_id`)
) TYPE=MyISAM;
 
INSERT INTO `album_utwor` VALUES (1, 1);
INSERT INTO `album_utwor` VALUES (1, 2);
INSERT INTO `album_utwor` VALUES (2, 3);
INSERT INTO `album_utwor` VALUES (3, 4);
INSERT INTO `album_utwor` VALUES (3, 5);
# Male info dla fanow Bee Gees'ow - gdyby byl jakis blad
# w dacie wydania albumu/utworow, prosze mnie zawiadomic;

A więc mamy strukturę tabelek będącą odpowiednikiem tego, co pisałem powyżej. Aby dodatkowo nie gmatwać wszystkiego, uprościłem gromadzenie danych o wykonawcy.

Spróbujmy teraz pobrać jakieś dane. Załóżmy, że chcemy sprawdzić, w jakim albumie ukazał się utwór Rasputin. Możemy zrealizować to takim zapytaniem:

SELECT a.tytul, a.wykonawca FROM albumy a, utwory u, album_utwor au WHERE a.id = au.album_id AND au.utwor_id = u.id AND u.tytul = 'Rasputin';

Wynik:

TYTUL                  WYKONAWCA
Nightflight to Venus   Boney M

W zapytaniu pojawiło nam się sporo nowych elementów. Najbardziej rzuca się w oczy rozbudowanie klazuli FROM, więc to od niej zaczniemy. Wymieniłem tam listę tabel, którymi będę się posiłkować w moich zmaganiach. Ale - po nazwie każdej w tabel podałem alias, czyli krótki identyfikator, którego użyję dalej do określania, że to właśnie o tę, a nie o inną tabelkę mi chodzi. W spisie danych do pobrania zaznaczyłem przy polu, że dane mają być pobrane z tabeli albumy, nie z utwory (tam bowiem występują takie same pola). Najpierw podałem nazwę tabelki, z tym że pod postacią aliasu, po czym oddzieliłem ją kropką od nazwy pola.

OK, baza wie, co ma pobrać i skąd. Jednak aby wiedziała, JAKIE są zależności między poszczególnymi tabelami, potrzebna jest nam klazula WHERE. Ta także uległa rozbudowaniu. Zobacz, jak połączone są tabele. Przy pomocy zwykłego operatora porównania = określiłem, iż w zbiorze wyników znajdą się rekordy albumu skojarzonego poprzez tabelę album_utwor (a.id = au.album_id AND au.utwor_id = u.id) z piosenką o tytule Rasputin.

Teraz coś trudniejszego - pragniemy pobrać tytuły wszystkich piosenek wraz z nazwami albumów, w jakich się ukazały, posortowane według daty ich premiery. Tabele albumy oraz utwory zostaną skojarzone w taki sam sposób, jak w poprzednim przykładzie.

SELECT u.tytul, u.wykonawca, a.tytul AS `album_tytul` FROM albumy a, utwory u, album_utwor au WHERE a.id = au.album_id AND au.utwor_id = u.id ORDER BY u.rok_wydania

Wykonaj to zapytanie i zobacz, że to działa! Pobraliśmy naraz dane z dwóch tabel. Zwróć uwagę na interesujące AS występujące w spisie danych do pobrania - dzięki niemu określiłem, że w zbiorze wyników dane o albumie, z którego pochodzi utwór, będą widoczne pod tytułem album_tytul.

Mam nadzieję, iż przekonałem Cię o potędze relacji oraz o możliwościach, jakie one stwarzają. Przy ich pomocy znacząco zmniejszysz liczbę zapytań w twoich skryptach. Jako ciekawostkę podam, iż relacje można stosować NIE TYLKO w klauzulach SELECT! Oto przykłady wprost z moich własnych zasobów :)

UPDATE users, sessions SET user_lastvisit = sessions.session_time WHERE sessions.session_time < (UNIX_TIMESTAMP() - 3600);
DELETE sessions FROM users, sessions WHERE users.user_id = sessions.session_user_id;

Do góry

LEFT JOIN

Relacje są potężnym narzędziem. Staną się jednak jeszcze potężniejsze, gdy nauczysz się dynamicznie ustalać, czy jakieś dane znajdą się w końcowych wynikach, czy nie. Co mam na myśli pod słowem "dynamiczne"? Otóż... w poprzednim rozdziale pokazałem zapytania z mojego własnego mechanizmu sesji służącego do autoryzacji. Każda sesja przechowuje ID zalogowanego aktualnie użytkownika, jednak ma także możliwość obsługi zwyczajnych gości (wtedy pole z ww. ID jest ustawione na 0). Gdyby zastosować do czegoś takiego zapytanie w tym stylu:

SELECT s.*, u.* FROM users u, sessions s WHERE s.session_user_id = u.user_id

to mielibyśmy małe kłopoty, bowiem nie zostałyby załadowane żadne sesje należące do gości (zapytanie ma konkretny warunek: dodaj dane tylko, gdy pole session_user_id wskazuje na istniejącego użytkownika i vice versa). Tu wkracza ta nasza dynamiczność - zapytanie powinno tworzyć rekord z obu tabel tylko wtedy, gdy istnieje powiązanie z konkretnym użytkownikiem. W przeciwnym wypadku dane z users nie powinny być dołączone do wyniku, w którym zostaną tylko informacje o sesji. Tylko jak to zrobić? MySQL ma receptę i na to...

Klauzule JOIN pozwalają na definiowanie warunków, kiedy informacje z takiej a takiej tabelki mają być dodawane do zbioru wyników, a kiedy nie, bez wpływania na to, czy dany rekord trafi tam. Istnieje dość dużo typów JOIN'ów, my zajmiemy się tu tylko jednym - LEFT JOIN. Umieszczamy go zawsze w klauzuli FROM. Jego składnia jest następująca:

tabelka1 LEFT JOIN tabelka2 ON warunek

tabelka1 będzie obecna w każdym rekordzie, natomiast tabelka2 tylko wtedy, gdy zostanie spełniony jakiś warunek.

W naszych sesjach wyglądałoby to tak:

SELECT s.*, u.* FROM users u LEFT JOIN sessions s ON s.session_user_id = u.user_id

Oczywiście warunek kojarzący obie tabele został przeniesiony do LEFT JOIN'a, ponieważ inaczej raczej nic ciekawego nie dałoby nam to zapytanie :).

UWAGA: zapytania używające LEFT JOIN nie powinny być stosowane dla dużej ilości danych naraz, gdyż spowodują wtedy zbyt duże obciążenie bazy, co odbije się niekorzystnie na wydajności aplikacji!

Do góry

Właściwy typ danych

To zagadnienie można potraktować jako dodatek, aczkolwiek jest ono również niezwykle ważne. Dotyczy bowiem wyboru właściwego typu dla przechowywanych informacji, co wiąże się bezpośrednio z przyspieszeniem wykonywania rozmaitych operacji na tabeli oraz samą wygodą użytkowania i analizowania danych. W kursach niestety jest traktowane (tradycyjnie) po łebkach, a tymczasem te są czytane niejednokrotnie przez osoby, które jeszcze nie znają się na programowaniu, a co za tym idzie, nie wiedzą, jak efektywnie swe dane przechowywać!

Spotkałem się raz z sytuacją, gdy pewien początkujący programista PHP-MySQL zapisywał dosłownie wszystkie liczby do bazy jako zwyczajne teksty (varchar) i po prostu nie rozumiał, po co w takim razie udostępniono tyle innych typów. Tymczasem sprawa tutaj ma znaczenie zasadnicze, ponieważ liczba zapisana jako liczba zajmuje o wiele mniej pamięci - komputer koduje ją sobie po prostu w postaci zbioru jedynek i zer, a dziesiętna reprezentacja go (krótko mówiąc) nie interesuje. Porównajmy - w jednym bajcie możemy zapisać jedną z 256 wartości. Nadaje się to idealnie do reprezentowania liczb z zakresu 0-255 i, jak widać, bez względu na ilość cyfr, ciągle kodujemy to na tej samej powierzchni. Gdyby każdą cyfrę zapisywać jako znak tekstowy, dla liczb powyżej setki będą to już trzy bajty, plus dodatkowy bajt przechowujący długość tekstu. Dla np. 1000 rekordów daje to istotną stratę ponad 3 kb! Weźmy teraz większą liczbę. 4 bajty to 32 bity. 2 do potęgi 32 daje zakres 0 - 4294967296, czyli ponad 4 miliardy kombinacji! W miliardzie mamy 10 cyfr, więc w przypadku zapisu tekstowego zajmiemy 11 bajtów (10 na liczbę + 1 na długość tekstu). 11 - 4 = 7 bajtów różnicy na korzyść zapisu binarnego! Przemnóżmy to przez 1000 rekordów, a otrzymamy 4 kb i 11 kb! Różnica jest chyba widoczna gołym okiem?

Warto wspomnieć również, iż komputerowi o wiele łatwiej jest porównywać cyfrowo zapisane numery - w końcu całą sprawa polega tu na odpowiednim porównaniu bitów na kilku bajtach. Tymczasem w przypadku tekstu po prostu nie ma takiej możliwości - dla komputera jest to tylko zbiór liczb oznaczających konkretne znaki graficzne, czyli pojedyncze cyfry. Nie potrafi on z tym zrobić nic, a gdyby spróbować to porównać, zrobi to tak, jak porównuje się ciągi, tj. bajt po bajcie. Co z tego wynika? Ograniczymy sobie w ten sposób tylko możliwości działania (wyszukiwanie względem tego, czy rekordy mają np. mniejszą wartość - odpada), a to, co zostaje, zajmuje nieporównywalnie więcej czasu.

Daty także warto zapisywać w postaci liczb. Komputer nawet ułatwia nam tu robotę, ponieważ bez problemu potrafi on określić liczbę sekund, które upłynęły od 1.1.1970, właśnie w postaci liczby jako liczby :). Porównywanie tak zaprezentowanego czasu to czysta przyjemność; nawet wbudowany w MySQL'a typ date nie ma przy tym szans.

Do góry

Zakończenie

Artykuł powoli dobiega końca. Mam nadzieję, iż w znaczący sposób poszerzyłem Twą wiedzę o języku SQL dla bazy MySQL, a to zaowocuje znacznie lepszymi zapytaniami w Twoich projektach. W końcu wydajność jest najważniejsza...

Autor: Tomasz "Zyx" Jędrzejewski, www.zyxist.com.

Do góry

Waszym zdaniem:

Uwaga: To jest jedynie część artykułu. Komentarze dotyczą całości tekstu.

Nikt jeszcze nie dodał swojego komentarza. Możesz być pierwszy!

Twoim zdaniem:

Pola wymagane: podpis, adres e-mail, treść. Znaczniki HTML nie są dozwolone.

MySQL w praktyce | « Wstecz - Sortowanie | Aliasy, tworzenie relacji - Dalej »

WebCity.pl

Projektowanie, tworzenie i promocja stron WWW w teorii i praktyce.

Funkcje w MySQL; sortowania ciąg dalszy

Aliasy, tworzenie relacji

LEFT JOIN

Właściwy typ danych

Zakończenie

Waszym zdaniem:

Twoim zdaniem:

Forum - dołącz!

Reklama

Partnerzy