PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start

PHP6 i MySQL 5. Dynammiczne strony www. Szybki startAutor: Larry UllmanT³umaczenie: Jaromir SenczykISBN: 978-83-246-1723-4Tytu³ orygina³u: PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro GuideFormat: 170x230, stron: 640

Poznaj mo¿liwoœci PHP6 oraz MySQL 5 i twórz dynamiczne strony WWW

• Jak utworzyæ podstawowy skrypt PHP?• Jak korzystaæ z wielowymiarowych tablic?• Jak budowaæ bazy danych?

Ka¿da funkcjonalna i atrakcyjna dla u¿ytkowników strona internetowa musi byæ na bie¿¹co aktualizowana, a umieszczone na niej interesuj¹ce informacje powinny byæ ³atwo dostêpne. Najpopularniejsze narzêdzia typu open source, s³u¿¹ce do tworzenia dynamicznych witryn, to jêzyk PHP i system zarz¹dzania relacyjnymi bazami danych MySQL. Oba te narzêdzia oferuj¹ wysok¹ wydajnoœæ, przenoœnoœæ i niezawodnoœæ. Wœród wielu ogromnych mo¿liwoœci oraz zalet PHP i MySQL maj¹ tak¿e tak¹, ¿e sprawne pos³ugiwanie siê nimi nie jest zbyt skomplikowane nawet dla pocz¹tkuj¹cych.

Ksi¹¿ka „PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start” zawiera precyzyjny opis czynnoœci oraz bogato ilustrowane zrzutami ekranu niezbêdne wskazówki i wyjaœnienia, u³atwiaj¹ce samodzielne zbudowanie dynamicznej strony internetowej. Dziêki temu podrêcznikowi nauczysz siê wyszukiwaæ i usuwaæ b³êdy w skryptach PHP, tworzyæ formularze w jêzyku HTML oraz zapobiegaæ atakom na Twoje witryny. Poznasz tak¿e podstawowe i zaawansowane techniki tworzenia ró¿nych aplikacji (na przyk³ad stron wielojêzycznych lub obs³uguj¹cych fora dyskusyjne).

• PHP i MySQL• Tworzenie formularza w jêzyku HTML• Tablice i ³añcuchy• Tworzenie i wywo³ywanie w³asnych funkcji• Wype³nianie baz danych• Zabezpieczenia• Stosowanie modyfikatorów• Szyfrowanie danych• Tworzenie uniwersalnych witryn• Budowanie strony domowej• Wielojêzyczna strona WWW• Tworzenie kont u¿ytkowników i nadawanie uprawnieñ

Szybko i ³atwo naucz siê tworzyæ funkcjonalne oraz bezpieczne witryny internetowe

http://helion.pl/view/2547w



http://helion.pl/add/2547w/ph6ms5




mailto:[email protected]

http://helion.pl/view/2547w/ph6ms5.htm

http://helion.pl/view/2547w/ph6ms5.htm

5

Spis treści

Wprowadzenie 9Czym są dynamiczne strony WWW? ................................................................... 10Co będzie Ci potrzebne? ....................................................................................... 16O tej książce ........................................................................................................... 17

Rozdział 1. Wprowadzenie do PHP 19Podstawy składni.................................................................................................... 20Przesyłanie danych do przeglądarki internetowej ............................................... 24Wstawianie komentarzy......................................................................................... 28Co to są zmienne? .................................................................................................. 32Łańcuchy ................................................................................................................ 36Łączenie łańcuchów............................................................................................... 39Liczby ..................................................................................................................... 41Stałe ........................................................................................................................ 45Apostrof kontra cudzysłów .................................................................................... 48

Rozdział 2. Programowanie w PHP 51Tworzenie formularza w języku HTML............................................................... 52Obsługa formularza HTML................................................................................... 56Wyrażenia warunkowe i operatory ....................................................................... 60Weryfikacja danych pochodzących z formularza ................................................. 64Co to są tablice? ..................................................................................................... 70Pętle for i while ...................................................................................................... 88

Rozdział 3. Tworzenie dynamicznych stron WWW 91Wykorzystywanie plików zewnętrznych .............................................................. 92Wyświetlanie i obsługa formularza przez jeden skrypt ....................................... 102Tworzenie formularzy z pamięcią ....................................................................... 107Tworzenie i wywoływanie własnych funkcji ...................................................... 110

Rozdział 4. Wprowadzenie do MySQL 125Elementy bazy danych i ich nazwy..................................................................... 126Wybór typu kolumny ........................................................................................... 128Wybór innych właściwości kolumn .................................................................... 132Korzystanie z serwera MySQL-a ........................................................................ 134

Spis treści

Spis treści

6

Spis

treśc

i

Rozdział 5. Wprowadzenie do SQL 141Tworzenie baz danych i tabel.............................................................................. 142Wprowadzanie rekordów..................................................................................... 145Wybieranie danych .............................................................................................. 149Wyrażenia warunkowe ........................................................................................ 151Stosowanie LIKE i NOT LIKE.......................................................................... 154Sortowanie wyników zapytania ........................................................................... 156Ograniczanie wyników zapytania........................................................................ 158Uaktualnianie danych .......................................................................................... 160Usuwanie danych ................................................................................................. 162Funkcje ................................................................................................................. 164

Rozdział 6. Zaawansowany SQL i MySQL 175Projekt bazy danych............................................................................................. 176Złączenia............................................................................................................... 191Grupowanie wyników zapytania ......................................................................... 196Indeksy ................................................................................................................. 198Stosowanie różnych typów tabeli........................................................................ 203Wyszukiwanie FULLTEXT................................................................................ 206Wykonywanie transakcji...................................................................................... 212

Rozdział 7. Obsługa i usuwanie błędów 217Ogólne typy błędów i ich usuwanie.................................................................... 218Wyświetlanie błędów PHP.................................................................................. 224Sterowanie raportowaniem błędów PHP ........................................................... 226Tworzenie własnych funkcji obsługi błędów ..................................................... 229Techniki usuwania błędów z PHP ...................................................................... 234Techniki usuwania błędów SQL i MySQL ........................................................ 238

Rozdział 8. PHP i MySQL 241Modyfikacja szablonu .......................................................................................... 242Łączenie się z MySQL-em i wybieranie bazy.................................................... 244Wykonywanie prostych zapytań.......................................................................... 248Odczytywanie wyników zapytania ...................................................................... 257Bezpieczeństwo zapytań ...................................................................................... 261Zliczanie zwróconych rekordów ......................................................................... 267Uaktualnianie rekordów w PHP ......................................................................... 269

Rozdział 9. Tworzenie aplikacji internetowych 277Przekazywanie wartości do skryptu..................................................................... 278Stosowanie ukrytych pól formularza................................................................... 282Edycja istniejących rekordów ............................................................................. 288

Spis treści

7

Spis treści

Stronicowanie wyników zapytań ......................................................................... 295Wyświetlanie tabel z możliwością sortowania.................................................... 303

Rozdział 10. Tworzenie aplikacji internetowych 309Wysyłanie poczty elektronicznej ........................................................................ 310Funkcje daty i czasu............................................................................................. 316Obsługa przesyłania plików................................................................................. 320Skrypty PHP i JavaScript .................................................................................... 333Nagłówki HTTP ................................................................................................... 340

Rozdział 11. Sesje i „ciasteczka” 345Strona logowania .................................................................................................. 346Funkcje logowania ............................................................................................... 349Posługiwanie się ciasteczkami............................................................................. 354Sesje ...................................................................................................................... 367Zwiększanie bezpieczeństwa sesji ...................................................................... 376

Rozdział 12. Zabezpieczenia 379Zapobieganie spamowi ........................................................................................ 380Walidacja danych według typu ........................................................................... 387Zapobieganie atakom XSS................................................................................... 392Zapobieganie wstrzykiwaniu poleceń SQL........................................................ 395Szyfrowanie i bazy danych .................................................................................. 401

Rozdział 13. Wyrażenie regularne Perl 407Skrypt testujący.................................................................................................... 408Definiowanie prostych wzorców......................................................................... 412Stosowanie kwantyfikatorów ............................................................................... 415Klasy znaków........................................................................................................ 418Wyszukiwanie wszystkich dopasowań................................................................ 421Stosowanie modyfikatorów.................................................................................. 425Dopasowywanie i zastępowanie wzorców.......................................................... 427

Rozdział 14. Tworzenie uniwersalnych witryn 431Zbiory znaków i kodowanie................................................................................. 432Tworzenie wielojęzycznych stron WWW .......................................................... 434Unicode w PHP ................................................................................................... 438Uporządkowanie zbioru znaków w PHP ............................................................ 442Transliteracja w PHP........................................................................................... 445Języki i MySQL.................................................................................................... 448Strefy czasowe i MySQL ..................................................................................... 452Lokalizatory .......................................................................................................... 455

Spis treści

8

Spis

treśc

i

Rozdział 15. Forum dyskusyjne — przykład 459Baza danych.......................................................................................................... 460Szablony................................................................................................................ 469Strona domowa..................................................................................................... 478Strona forum......................................................................................................... 479Strona wątku......................................................................................................... 484Wstawianie wiadomości....................................................................................... 489

Rozdział 16. Rejestracja użytkowników — przykład 501Tworzenie szablonu ............................................................................................. 502Skrypty konfiguracyjne........................................................................................ 508Tworzenie strony domowej ................................................................................. 516Rejestracja ............................................................................................................ 518Aktywacja konta ................................................................................................... 527Logowanie i wylogowywanie się ......................................................................... 531Zarządzanie hasłami............................................................................................. 537

Rozdział 17. Sklep internetowy — przykład 547Tworzenie bazy danych ....................................................................................... 548Część administracyjna aplikacji .......................................................................... 554Tworzenie szablonu części publicznej aplikacji................................................. 571Katalog produktów............................................................................................... 575Koszyk................................................................................................................... 587Rejestrowanie zamówień ..................................................................................... 597

Dodatek A Instalacja 605Instalacja w systemie Windows .......................................................................... 606Definiowanie uprawnień MySQL....................................................................... 609Testowanie instalacji............................................................................................ 613Konfigurowanie PHP........................................................................................... 616

Skorowidz 619

175

Rozdział 6. Zaawansowany SQL i MySQL

Rozdział ten zaczyna się w miejscu, w którym ostatni się kończył. Omówię w nim bardziejzaawansowane zagadnienia dotyczące SQL-a i MySQL-a. Poznałeś już podstawy obu tychtechnologii i z pewnością wystarczą Ci one do realizacji wielu projektów, ale dopiero ichbardziej wyszukane możliwości wyniosą Twe aplikacje internetowe na wyższy poziom.

Zacznę od szczegółowego omówienia procesu projektowania bazy danych, opierając sięna przykładzie systemu zarządzania forum. Doprowadzi nas to oczywiście do tematuzłączeń, będących integralną częścią każdej relacyjnej bazy danych. Następnie będęopisywał kolejną kategorię funkcji wbudowanych MySQL-a używanych do grupowaniawyników zapytań.

Na zakończenie przejdę do bardziej zaawansowanych zagadnień. Powiem o indeksach,nauczę Cię zmieniać strukturę istniejących tabel oraz omówię typy tabel dostępnew MySQL-u. Rozdział zakończę omówieniem dwóch dodatkowych możliwości MySQL-a:przeszukiwania tekstów i transakcji.

Zaawansowany SQL i MySQL 6Zaaw

ansowany SQ

L i MySQ

L

Rozdział 6.

176

Projekt bazy danychPierwsze, co musisz zrobić, gdy pracujesz z systememzarządzania relacyjnymi bazami danych, takim jakMySQL, to utworzyć strukturę bazy (zwaną równieżschematem bazy danych). Projektowanie bazy danychlub inaczej modelowanie danych to niezbędny etapgwarantujący długotrwałe i bezproblemowe zarządzanieTwoimi informacjami. W procesie zwanym normalizacjąeliminuje się niepotrzebne powtórzenia informacjii inne problemy, które zagrażają spójności danych.

Dzięki technikom, które poznasz w tym rozdziale,Twoje bazy będą wydajne i niezawodne. Jedenz przykładów, które zaprezentuję — forum, na którymużytkownicy mogą wymieniać się opiniami — będzieintensywnie wykorzystywany dopiero w rozdziale 15.,„Forum dyskusyjne — przykład”. Jednak omówioneprzeze mnie zasady normalizacji odnoszą siędo wszystkich aplikacji bazodanowych, jakie możeszutworzyć. (Przykład bazy sitename używanyw poprzednich dwóch rozdziałach został poprawniezaprojektowany również z punktu widzenia normalizacji,ale zagadnienie to nie zostało jeszcze omówione.)

Normalizacja

Proces normalizacji został wymyślony na początku latsiedemdziesiątych przez E.F. Codda, naukowca z firmyIBM, który wymyślił też relacyjne bazy danych. Tegorodzaju bazy to nic innego jak tylko zbiór danychułożonych w pewien określony sposób. Istnieje szeregtak zwanych postaci normalnych (NF, z ang. NormalForm), które ułatwiają definiowanie struktury danych.W tym rozdziale omówię pierwsze trzy z nich,ponieważ w większości przypadków są one w pełniwystarczające.

Zanim zaczniesz normalizować swoją bazę danych,musisz określić, jakie funkcje będzie pełniła Twojaaplikacja. Być może będziesz musiał w tym celuporozmawiać z klientem lub samodzielnie zastanowićsię nad tym zagadnieniem. W każdym razie strukturabazy danych zależy od sposobu, w jaki aplikacja będzieodwoływała się do zgromadzonych w niej informacji.Na tym etapie będziesz więc potrzebował raczej kartkii ołówka niż MySQL-a. Oczywiście, w ten sposóbprojektuje się wszystkie relacyjne bazy danych,nie tylko te działające w systemie MySQL.

W moim przykładowym systemie będę chciałstworzyć forum, na którym użytkownicy mogązamieszczać swoje opinie i odpowiadać innyminternautom. Aby korzystać z forum, użytkownikbędzie musiał się zarejestrować, a następnieuwierzytelnić za pomocą kombinacji nazwy i hasła.Przewiduję również możliwość istnienia wieluforów poświęconych różnym tematom. W tabeli6.1 pokazałem, jak wygląda przykładowy rekord.Baza danych będzie nosić nazwę forum.

Wskazówki

Istnieje bardzo dobra metoda na określenie,jakiego rodzaju informacje powinny sięznaleźć w bazie danych. Wystarczy,że zastanowisz się, jakiego rodzaju pytaniao dane będą zadawane przez użytkownikówi jakie dane będą musiały się znaleźćw odpowiedziach.

Nauka normalizacji może sprawić Ci trudności,jeśli niepotrzebnie skoncentrujesz się naszczegółach. Każda z postaci normalnych jestzdefiniowana w dość skomplikowany sposób,a próba przełożenia tych definicji na językniefachowy może być myląca. Dlatego radzęCi, abyś podczas lektury omówienia postacinormalnych skoncentrował się na ogólnymobrazie zmian zachodzących w schemaciebazy danych. Po zakończeniu normalizacjii otrzymaniu końcowej postaci bazy danychcały proces powinien stać się dla Ciebiewystarczająco zrozumiały.Pr

ojek

t ba

zy d

anyc

h

Tabela 6.1. Przykładowy rekord pokazujący, jakiegorodzaju informacje chcę przechowywać w mojejbazie danych

Przykładowe dane forum

Element Przykład

username janekpassword hasloactual name Jan Kowalskiuser email [email protected] MySQLmessage subject Pytanie na temat normalizacjimessage body Nie rozumiem jednej rzeczy. Dla drugiej

postaci normalnej (2NF) podano...message date 2 lutego 2008 12:20

Zaawansowany SQL i MySQL

177

Klucze

W rozdziale 4., „Wprowadzenie do MySQL-a”,wspominałem już, że klucze są integralną częściąznormalizowanych baz danych. Spotkasz sięz dwoma rodzajami kluczy, głównymi i obcymi.Klucz główny to unikatowy identyfikator, którypodlega pewnym ściśle określonym regułom.Musi on:

Zawsze mieć jakąś wartość (inną niż NULL).

Mieć stałą wartość (taką, która nigdy nie ulegazmianie).

Mieć inną wartość dla każdego rekorduw tabeli.

Najlepszym, z życia wziętym przykładem kluczagłównego jest numer ubezpieczenia społecznegoprzydzielany obywatelom USA. Każdy ma tamwłasny, niezmienny, unikatowy numer polisy.Ułatwia to identyfikowanie osób. Wkrótceprzekonasz się, że wielokrotnie sam będziesztworzył we wszystkich tabelach klucze główne.Jest to po prostu dobra praktyka projektowa.

Drugi rodzaj kluczy stanowią klucze obce.Reprezentują one w tabeli B klucze głównetabeli A. Jeżeli masz na przykład bazę danychfilmy, w której występują tabele film i reżyser,to klucz główny tabeli reżyser będzie pełniłrolę klucza obcego w tabeli film. Już niedługozobaczysz, jak to wszystko działa w praktyce.

Baza danych forum składa się w zasadzie tylko z jednejprostej tabeli (tabela 6.1), ale zanim rozpocznę procesnormalizacji, chcę utworzyć w niej przynajmniej jedenklucz główny (klucze obce pojawią się w następnychkrokach).

Aby przypisać klucz główny:

1. Poszukaj pól, które spełniają wszystkie trzy warunkiokreślone dla kluczy głównych.

W naszym przykładzie (tabela 6.1) żadna z kolumnnie spełnia kryteriów klucza głównego. Nazwaużytkownika i adres e-mail są unikalne dla każdegoużytkownika forum, ale nie dla każdego rekordubazy danych (ten sam użytkownik może umieszczaćwiele wiadomości na forum). Również ten samtemat wiadomości może występować wiele razy.Tekst wiadomości będzie prawdopodobnie zawszeunikalny, ale może się zmieniać na skutekpóźniejszych poprawek, tym samym naruszającjedno z kryteriów wyboru klucza głównego.

2. Jeżeli nie istnieje żaden logiczny kandydat na kluczgłówny — wprowadź go! (tabela 6.2).

Sytuacja, w której musisz sam utworzyć kluczgłówny, ponieważ żadne z istniejących pól nienadaje się do pełnienia tej roli, występuje dośćczęsto. W tym konkretnym przykładzie utworzępole message ID.

Wskazówki

Stosuję się do reguły, w myśl której w nazwachkluczy głównych powinna wystąpić przynajmniejczęść nazwy tabeli (np. message) i przyrostek id.Niektórzy projektanci dodają również skrót pk(ang. primary key — klucz główny).

MySQL zezwala na stosowanie w każdej tabelitylko jednego klucza głównego, choć możesz oprzećgo na kilku kolumnach (oznacza to, że kombinacjatych kolumn musi być unikatowa).

Najlepiej byłoby, gdyby Twój klucz głównybył zawsze liczbą całkowitą, ponieważ pozwalato MySQL-owi osiągnąć najwyższą możliwąwydajność.

Projekt bazy danych

Tabela 6.2. Dodałem do tabeli klucz główny, dzięki czemubędę mógł łatwo odwoływać się do rekordów

Baza danych forum

Element Przykład

message ID 1username janekpassword hasloactual name Jan Kowalskiuser email [email protected] MySQLmessage subject Pytanie na temat normalizacjimessage body Nie rozumiem jednej rzeczy. Dla drugiej

postaci normalnej (2NF) podano...message date 2 lutego 2008 12:20

Rozdział 6.

178

Zależności

Mówiąc o zależnościach w bazach danych mamna myśli to, w jaki sposób dane z jednej tabeli sąpowiązane z danymi występującymi w drugiej.Zależności między dwiema tabelami mogąprzybierać postać jeden do jednego, jeden do wielulub wiele do wielu. (Dwie tabele tej samej bazydanych mogą być również wcale niepowiązane).

O zależności „jeden do jednego” mówimy wtedy,gdy jeden i tylko jeden element tabeli A odnosi siędo jednego i tylko jednego elementu tabeli B(np. każdy mieszkaniec USA ma tylko jeden numerubezpieczenia społecznego, a każdy numer polisyjest przyporządkowany tylko do jednego obywatela.Nikt nie może mieć dwóch polis, podobnie jakżaden numer ubezpieczenia nie może odnosić siędo dwóch osób).

Zależność „jeden do wielu” występuje wtedy,gdy jakiś element tabeli A może odnosić się do kilkuróżnych elementów tabeli B. Na przykład,określenia mężczyzna i kobieta mogą być używanew odniesieniu do wielu osób, ale każdy człowiekmoże mieć tylko jedną płeć. Jest to najczęściejwystępująca zależność między tabelamiw znormalizowanych bazach danych.

Istnieje też zależność „wiele do wielu”, w którejkilka elementów tabeli A może odnosić się do kilkuelementów tabeli B. Na przykład rekord płytamoże zawierać piosenki wykonywane przez różnychartystów, a każdy artysta może mieć na swoimkoncie kilka płyt. W swoich projektach powinieneśunikać tego typu zależności, ponieważ prowadzą onedo problemów ze spójnością danych i sprzyjają ichpowielaniu. Zamiast zależności „wiele do wielu”stworzysz tabelę pośrednią, dzięki której zależność„wiele do wielu” zostanie rozbita na dwie zależności„jeden do wielu”.

Temat zależności jest w pewien sposób związanyz zagadnieniem kluczy, ponieważ kluczezdefiniowane w jednej tabeli odwołują się zazwyczajdo pól występujących w innych tabelach.

Wskazówki

Modelowanie baz danych rządzi siępewnymi regułami. Istnieje określonakonwencja reprezentowania strukturybazy (zostanie ona tutaj zachowana).Na rysunku 6.1 pokazałem symbole trzechrodzajów zależności.

Proces projektowania bazy danych prowadzido powstania diagramu zależności międzyencjami (ERD), na którym występująprostokąty reprezentujące tabele orazsymbole z rysunku 6.1.

Istnieje wiele programów służącychdo tworzenia schematów baz danych.Jednym z nich jest MySQL Workbench(www.mysql.com).

Termin „relacyjny” w określeniu „systemzarządzania relacyjnymi bazami danych”odnosi się do tabel, które nazywa sięrelacjami.

Rysunek 6.1. Pokazane tu symbole często spotykasię na diagramach strukturalnych. Opisują onezależności występujące między tabelami

Proj

ekt

bazy

dan

ych


179

Pierwsza postać normalna

Jak już powiedziałem wcześniej, normalizacjabazy danych jest procesem dostosowywaniastruktury bazy danych do kilku postaci.Dostosowywanie to musi być precyzyjnei odbywać się w określonej kolejności.

Mówimy, że baza danych jest pierwszej postacinormalnej (1NF), jeżeli:

Każda kolumna zawiera tylko jedną wartość(czyli jest atomowa lub niepodzielna).

Żadna tabela nie ma powtarzających siękolumn dla danych pozostających w pewnejzależności.

Na przykład tabela zawierająca pole, w którymmożna umieścić kilka numerów telefonów (domowy,komórkowy, numer faksu itd.) nie jest zgodnaz pierwszą postacią normalną, ponieważ w jednejkolumnie może się znaleźć więcej niż jedna wartość.Jeśli chodzi o drugi warunek, to tabela filmzawierająca kolumny aktor1, aktor2, aktor3 i takdalej, nie będzie w pierwszej postaci normalnej,ponieważ powtarzające się kolumny zawierająten sam rodzaj informacji.

Proces normalizacji rozpocznę od sprawdzenia,czy aktualna struktura bazy (tabela 6.2) jest zgodnaz 1NF. Kolumny, które nie są atomowe, zostanąrozbite na wiele kolumn. Jeśli tabela posiadapowtarzające się, podobne kolumny, to zostanąone przekształcone w osobną tabelę.

Aby uczynić bazę zgodną z 1NF:

1. Zidentyfikuj pole, które może zawierać kilkainformacji naraz.

Gdy spojrzysz jeszcze raz na tabelę 6.2,zobaczysz, że jedno z pól nie jest zgodnez 1NF: actual name. Zawiera ono zarównoimię jak i nazwisko użytkownika.

Pole message date (data dodania do bazy)przechowuje, co prawda, dzień, miesiąc i rok,ale raczej nie będzie nas interesować takipoziom szczegółowości i potraktujemyprzechowywaną przez nie wartość jakoniepodzielną. Zresztą pod koniec poprzedniegorozdziału pokazałem, że MySQL potrafidoskonale obsługiwać dane reprezentującedaty i czas za pomocą typu DATETIME.

Gdyby tabela zawierała na przykład jedną,wspólną kolumnę dla imienia i nazwiska zamiastdwóch osobnych albo przechowywała wielenumerów telefonów (stacjonarny, komórkowy,domowy, służbowy) w jednej kolumnie,to kolumny te również należałoby rozbić.

2. Rozbij wszystkie pola odszukane w kroku 1.na kilka mniejszych, niepodzielnych pól(tabela 6.3).

Projekt bazy danychTabela 6.3. Tabela z atomowymi kolumnami

Baza danych forum

Element Przykład

message ID 1username janekpassword haslofirst name Janlast name Kowalskiuser email [email protected] MySQLmessage subject Pytanie na temat normalizacjimessage body Nie rozumiem jednej rzeczy.

Dla drugiej postaci normalnej(2NF) podano...

message date 2 lutego 2008 12:20

Rozdział 6.

180

Aby poradzić sobie z tym problemem, utwórzosobne pola first name i last name, które będązawierać tylko jedną wartość.

3. Przekształć każdą grupę powtarzających siękolumn w osobną tabelę.

Problem ten nie występuje w bazie danychforum. Zademonstruję go zatem na przykładzieprzedstawionym w tabeli 6.4. Powtarzające siękolumny zawierające informacje o aktorachpowodują dwa problemy. Po pierwsze,dla każdego filmu można podać tylkoograniczoną liczbę aktorów. Nawet jeśliwprowadzimy kolumny aktor 1 aż do aktor 100,to ograniczeniem będzie stu aktorów. Po drugie,każdy rekord, który nie zawiera maksymalnejliczby aktorów, będzie mieć wartości NULLw nadmiarowych kolumnach. W schemaciebazy danych powinniśmy unikać kolumnzawierających wartości NULL. Dodatkowymproblemem jest również to, że kolumnyzawierające informacje o reżyserze i aktorachnie są atomowe.

Aby rozwiązać problemy występujące w tabelifilm, utworzę dodatkową tabelę (tabela 6.5).Każdy jej rekord zawiera informacje o jednymautorze, co rozwiązuje problemy opisanepowyżej. Także nazwiska i imiona aktorów sąteraz przechowywane jako wartości atomowe.Zwróć również uwagę, że do nowej tabelidodałem kolumnę indeksu głównego. Zasada,że każda tabela posiada klucz główny, wynikawprost z pierwszej postaci normalnej.

4. Upewnij się, że wszystkie nowe pola utworzonew kroku 2. i 3. są zgodne z 1NF.

Wskazówki

Dostosowanie tabeli do 1NF wymagaanalizy tabeli w poziomie. Wszystkiekolumny jednego rekordu przeglądamypod kątem występowania w nich danych,które nie są atomowe oraz występowaniapowtarzających się, podobnych danych.

Postacie normalne opisane są w różnychźródłach w różny sposób, często z użyciembardziej technicznego żargonu.Najważniejszy jest jednak sens i końcowyrezultat procesu normalizacji, a niesłownictwo zastosowane do jego opisu.

Proj

ekt

bazy

dan

ych

Tabela 6.4. Tabela film nie jest zgodna z 1NFz dwóch powodów. Po pierwsze, zawierapowtarzające się kolumny podobnych danych(aktor1 itd.). Po drugie, kolumny aktor i reżysernie zawierają wartości atomowych

Tabela film

Kolumna Wartość

film ID 976tytuł filmu Casablancarok produkcji 1943reżyser Michael Curtizaktor1 Humphrey Bogartaktor2 Ingrid Bergmanaktor3 Peter Lorre

Tabela 6.5. Aby tabela film (tabela 6.4) była zgodnaz 1NF, powiążę aktorów z filmami za pomocą tabelifilm-aktor

Tabela film-aktor

ID Film Imię aktora Nazwisko aktora

1 Casablanca Humphrey Bogart2 Casablanca Ingrid Bergman3 Casablanca Peter Lorre4 Sokół maltański Humphrey Bogart5 Sokół maltański Peter Lorre


181

Rysunek 6.2. Aby baza danych o filmach była zgodnaz 2NF, potrzebne są cztery tabele. Reżyserzy sąreprezentowani w tabeli film za pomocą kluczareżyser ID; filmy są reprezentowane w tabelifilm-aktor za pomocą klucza film ID; aktorzy sąreprezentowani w tabeli film-aktor za pomocąklucza aktor ID

Druga postać normalna

Każda baza, która ma spełniać drugą postaćnormalną (2NF), musi być najpierw zgodna z 1NF(proces normalizacji trzeba przeprowadzaćwe właściwej kolejności). Następnie wszystkiekolumny, które nie zawierają kluczy (kluczy obcych),muszą być powiązane zależnością z kluczemgłównym. Kolumny, które naruszają ten warunek,łatwo zidentyfikować po tym, że zawierają wartościniebędące kluczami i powtarzające się w różnychrekordach. Wartości te muszą zostać umieszczonew osobnej tabeli i być powiązane z tabelą wyjściowąza pomocą klucza.

Przykładem może być fikcyjna tabela film(tabela 6.4), która zawierałaby ponad dwadzieściarazy nazwisko Martina Scorsese jako reżyseraróżnych filmów. Sytuacja taka jest niezgodna z drugąpostacią normalną, ponieważ kolumna zawierającainformację o reżyserze nie jest kluczem i nie jestpowiązana zależnością z kluczem głównym (film ID).Rozwiązanie polega na utworzeniu osobnej tabelireżyserzy wyposażonej we własny klucz główny,który wystąpiłby jako klucz obcy w tabeli film,tworząc w ten sposób powiązanie obu tabel.

Patrząc na tabelę 6.5, można dostrzec dwa kolejnenaruszenia drugiej 2NF — ani tytuły filmów,ani nazwiska aktorów nie są powiązane z kluczemgłównym tabeli. Zatem baza danych o filmachw najprostszej postaci wymaga czterech tabel(rysunek 6.2). W ten sposób informacje o każdymfilmie, aktorze i reżyserze są przechowywane tylkojeden raz, a każda kolumna niebędąca kluczemjest zależna od klucza głównego danej tabeli.W zasadzie proces normalizacji można by określićjako tworzenie coraz większej liczby tabel, takaby całkowicie wyeliminować możliwość powieleniajakichkolwiek danych.

Projekt bazy danych

Rozdział 6.

182


1. Zidentyfikuj kolumny, które nie są kluczamii które nie są powiązane z kluczem głównym.

Przyglądając się tabeli 6.3 zauważysz, że żadnez pól username, first name, last name, emaili forum nie są kluczami (jedyną kolumną będącąkluczem jest na razie message ID) i żadne z nichnie jest powiązane zależnością z message ID.Natomiast message subject, body i date równieżnie są kluczami, ale ich wartości zależą od kluczamessage ID.

2. Utwórz odpowiednie tabele (patrz rysunek 6.3).

Najlogiczniejsza modyfikacja istniejącej strukturypolega na utworzeniu trzech tabel: users, forumsi messages.

Na diagramie bazy danych każda tabela zostałaprzeze mnie oznaczona prostokątem. Jej nazwapełni rolę nagłówka, pod którym wymienionesą wszystkie kolumny (atrybuty) tabeli.

3. Przypisz lub utwórz nowe klucze główne(rysunek 6.4).

Posługując się technikami opisanymi wcześniejw tym rozdziale, upewnij się, że każda nowatabela ma zdefiniowany klucz główny. W tabeliusers stworzyłem klucz user ID, a w tabeliforums klucz forum ID. Ponieważ pole usernamew tabeli users oraz pole name w tabeli forumsmuszą być unikalne dla każdego rekordu i zawszemieć wartość, to mógłbyś użyć ich jako kluczygłównych. Oznaczałoby to jednak, że wartościtych pól nie mogą się zmieniać (zgodniez jednym z kryteriów wyboru klucza głównego).Użycie kluczy tekstowych zamiast numerycznychpowodowałoby również nieco wolniejszedziałanie bazy danych.

Rysunek 6.3. Aby baza danych forum była zgodnaz 2NF, potrzebne są trzy tabele

Rysunek 6.4. Każda tabela powinna mieć własnyklucz główny

Proj

ekt

bazy

dan

ych


183

Rysunek 6.5. Aby zdefiniować zależność międzytymi trzema tabelami, dodałem do tabeli messagesdwa klucze obce, z których każdy łączy ją z jednąz pozostałych dwóch tabel

4. Utwórz pomocnicze klucze obce, które połączątabele zależnościami (rysunek 6.5).

Ostatni krok na drodze do zgodności z 2NFpolega na dodaniu kluczy obcych, które określąpowiązania między tabelami. Pamiętaj, że to,co w jednej tabeli jest kluczem głównym,w drugiej najprawdopodobniej będzie kluczemobcym.

W naszym przykładzie kolumna user ID z tabeliusers łączy się z kolumną user ID z tabelimessages. Dlatego też tabela users pozostajew zależności „jeden do wielu” z tabelą messages(każdy użytkownik może umieścić na forumwiele wiadomości, ale każda wiadomość możemieć tylko jednego autora).

Połączone zostały również dwie kolumnyforum ID, tworząc w ten sposób zależność „jedendo wielu” pomiędzy tabelami messages i forums(każda wiadomość może należeć tylko do jednegoforum, ale dane forum może zawierać wielewiadomości).

Wskazówki

Inny sposób sprawdzenia, czy tabelespełniają drugą postać normalną, polegana przyjrzeniu się powiązaniom tabel.Idealna sytuacja polega na występowaniusamych zależności „jeden do wielu”.Tabele podlegające zależnościom „wieledo wielu” mogą wymagać restrukturyzacji.

Jeśli przyjrzymy się jeszcze raz rysunkowi6.2, możemy zauważyć, że tabela film-aktorspełnia funkcję tabeli pośredniczącej.Pozwala ona zamienić zależność „wieledo wielu” zachodzącą pomiędzy filmamii aktorami na dwie zależności „jedendo wielu”. Tabele pośredniczące łatworozpoznać po tym, że wszystkie ich kolumnysą kluczami obcymi. W tym przypadkukolumna odgrywająca rolę klucza głównegonie jest potrzebna, ponieważ kluczemgłównym może być kombinacja obukolumn tabeli.

W prawidłowo znormalizowanej bazie danychw jednej tabeli nie mają prawa pojawić siędwa takie same rekordy (dwa lub więcejrekordów, w których wartości występującew poszczególnych kolumnach są identyczne).

Aby łatwiej przyswoić sobie proces normalizacji,zapamiętaj, że pierwsza postać normalnawiąże się z analizą tabeli w poziomie, podczasgdy druga postać normalna powstaje na skutekanalizy w pionie (poszukiwania wartościpowtarzających się w wielu rekordach).

Projekt bazy danych

Rozdział 6.

184

Trzecia postać normalna

Mówimy, że baza danych spełnia trzecią postaćnormalną (3NF), jeżeli jest ona zgodna z 2NF,a każda kolumna, która nie jest kluczem, jest zależnaod klucza głównego. Jeżeli prawidłowo przeszedłeśprzez pierwsze dwa etapy normalizacji,prawdopodobnie dostosowanie bazy danych do 3NFnie będzie już wymagać żadnych zmian. W moimprzykładzie (patrz rysunek 6.5) również nie maproblemów, które należałoby rozwiązać, aby bazabyła zgodna z trzecią postacią normalną. Dlategoteż przedstawię je omawiając hipotetyczną sytuację.

Weźmy na przykład pojedynczą tabelęprzechowującą informacje o zarejestrowanychklientach: imię, nazwisko, e-mail, numer telefonu,adres pocztowy itp. Tabela taka nie jest zgodnaz 3NF, ponieważ wiele kolumn nie będzie zależnychod klucza głównego. Nazwa ulicy będzie zależećod miasta, a miasto od stanu. Również kod pocztowynie będzie zależeć od tego, jaką osobę opisujerekord. Aby znormalizować taką bazę danych,powinno się stworzyć osobną tabelę reprezentującąpaństwa, osobną reprezentującą miasta (połączonąkluczem obcym z tabelą państw) i jeszcze innądla kodów. Wszystkie te tabele byłyby połączonez tabelą opisującą klientów.

Jeśli takie rozwiązanie wydaje Ci się przesadą,to masz rację. Wyższy stopień normalizacji częstonie jest konieczny. Chociaż powinno się dążyćdo jak najpełniejszej normalizacji, to czasami wartoją poświęcić na rzecz prostoty. (patrz ramka„Rezygnacja z normalizacji”). W praktyce stopieńnormalizacji zależy od potrzeb konkretnej aplikacjii szczegółów bazy danych.

Jak już wspomniałem, nasz przykład bazy forumnie wymaga dalszej normalizacji (jest już zgodnyz 3NF) i dlatego proces dostosowywania do trzeciejpostaci normalnej przedstawię właśnie na przykładzieomówionej wyżej bazy danych o klientach.


1. Zidentyfikuj wszystkie pola, które nie sąbezpośrednio powiązane z kluczemgłównym.Jak już wspomniałem, na tym etapieposzukujemy kolumn, które powiązane sąmiędzy sobą zależnościami (tak jak miastoi stan) zamiast z kluczem głównym.

W bazie danych forum nie występowałytakie zależności. Przyjrzyjmy się tabelimessages. Każda wartość pola subjectjest specyficzna dla danego message ID,każda wartość pola body jest specyficznadla wybranego message ID itd.

2. Utwórz odpowiednie tabele.Jeśli w punkcie 1. udało się odnaleźćproblematyczne kolumny, takie jakna przykład miasto i stan, to tworzymydla nich osobne tabele cities i states.

3. Przypisz lub utwórz nowe klucze główne.Każda tabela musi mieć klucz główny,wobec czego do nowych tabel dodajękolumny city ID i state ID.

4. Utwórz pomocnicze klucze obce, definiująctym samym zależności (rysunek 6.6).Na zakończenie dodałem do tabeli Citiesklucz obcy State ID oraz klucz obcy City IDdo tabeli Clients. W efekcie uzyskałempołączenie rekordu klienta nie tylkoz informacją o mieście, w którym jestzameldowany, ale również o stanie.

Rysunek 6.6. Uproszczona wersja hipotetycznejbazy clients będzie zawierać dwie nowe tabeleprzechowujące informacje o miastach i stanach

Proj

ekt

bazy

dan

ych


185

Rezygnacja z normalizacjiChoć spełnienie trzeciej postaci normalnejjest korzystne, nie oznacza to jeszcze,że masz normalizować każdą bazę danych,z którą pracujesz. Jednocześniepowinieneś uzmysłowić sobie, że odejścieod sprawdzonych metod może miećw perspektywie dłuższego czasukatastrofalne skutki.

Z normalizacji rezygnuje się zazwyczajz dwóch powodów — ze względuna wydajność i dla wygody. Dużo łatwiejjest ogarnąć mniejszą liczbę tabel, a pozatym — łatwiej się nimi zarządza. Ze względuna liczbę powiązań między tabelami,uaktualnianie, odczytywanie i modyfikowaniedanych w znormalizowanych bazach danychtrwa z reguły dłużej. Krótko mówiąc,normalizacja jest poświęceniem prostotyi szybkości na rzecz spójności i skalowalności.Należy jednak pamiętać, że istnieje znaczniewięcej sposobów na przyspieszenie bazydanych niż na odzyskanie informacjiutraconych na skutek przechowywania ichw źle zaprojektowanej bazie.

W miarę nabywania doświadczenia będzieszpotrafił coraz lepiej modelować bazy danych,ale mimo wszystko staraj się trzymaćpo stronie normalizacji.

Wskazówka

W praktyce nie normalizowałbym tabeli Clientsaż do takiego stopnia. Gdybym pozostawiłpola opisujące miasto i stan w tabeli Clients,to najgorszą rzeczą, jaka mogłaby się zdarzyć,byłaby zmiana nazwy miasta i związana z tymkonieczność aktualizacji rekordów wszystkichklientów będących jego mieszkańcami.W rzeczywistości jednak sytuacja taka zdarzasię bardzo rzadko.

Mimo istnienia zasad normalizacji baz danychdwóch różnych projektantów może dokonaćnormalizacji tej samej bazy w nieco inny sposób.Projektowanie baz danych pozostawia pewienmargines dla indywidualnych preferencjii interpretacji. Najważniejsze jest,by zaprojektowana baza danych nie naruszałapostaci normalnej, gdyż prędzej czy późniejdoprowadzi to do pojawienia się poważnychproblemów.

Projekt bazy danych

Rozdział 6.

186

2. Utwórz bazę danych forum (rysunek 6.7).

CREATE DATABASE forum;USE forum;

Możliwe, że Twoja konfiguracja nie pozwalana tworzenie nowych baz danych. W takiejsytuacji po prostu wykorzystaj jakąś jużistniejącą bazę i dodawaj do niej kolejnetabele.

Tworzenie bazy danychAby zakończyć projekt bazy danych, musimywykonać trzy ostatnie kroki:

1. Sprawdzić, czy w bazie znajdą się wszystkiepotrzebne informacje.

2. Zidentyfikować typy kolumn.

3. Nazwać wszystkie elementy bazy danych.

Ostateczny projekt bazy danych zostałprzedstawiony w tabeli 6.6. W porównaniudo rysunku 6.5 została dodana jeszcze jednakolumna. Ponieważ wiadomość umieszczanana forum może być odpowiedzią na inną wiadomość,musimy jakoś reprezentować tę zależność w bazie.Rozwiązanie polega na dodaniu kolumny parent_idw tabeli messages. Jeśli wiadomość jest odpowiedzią,to jej pole parent_id będzie zawierać wartość polamessage_id oryginalnej wiadomości (czyli message_idspełnia funkcję klucza obcego w tej samej tabeli).Jeśli pole parent_id ma wartość równą 0, oznaczato, że wiadomość stanowi początek nowego wątku,czyli nie jest odpowiedzią na żadną inną wiadomość.

Jeśli wprowadzasz jakiekolwiek zmiany w strukturzetabel, powinieneś ponownie sprawdzić, czy spełniająone wszystkie postacie normalne, aby mieć pewność,że baza danych jest nadal znormalizowana.

Zagadnienie nazw tabel i kolumn oraz wyboru typówkolumn omówiłem już w rozdziale 4.

Gdy schemat jest gotowy, możesz utworzyćodpowiadającą mu bazę danych MySQL-aza pomocą poleceń omówionych w rozdziale 5.,„Wprowadzenie do SQL”.

Aby utworzyć bazę danych:

1. Użyj wybranego klienta do dostępu do serweraMySQL-a.

Podobnie jak w poprzednim rozdziale,we wszystkich przykładach będziemy posługiwalisię monitorem (klientem) mysqla. Oczywiściemożesz też śmiało korzystać z phpMyAdminai innych narzędzi.

Rysunek 6.7. Najpierw musisz utworzyći wybrać bazę danych

Proj

ekt

bazy

dan

ych

Tabela 6.6. Ostateczny projekt bazy forumwraz z typami kolumn

forum

Nazwa kolumny Tabela Typ kolumny

forum_id forums TINYINT

name forums VARCHAR(60)

message_id messages INT

forum_id messages TINYINT

parent_id messages INT

user_id messages MEDIUMINT

subject messages VARCHAR(100)

body messages LONGTEXT

date_entered messages TIMESTAMP

user_id users MEDIUMINT

username users VARCHAR(30)

pass users CHAR(40)

first_name users VARCHAR(20)

last_name users VARCHAR(40)

email users VARCHAR(80)


187

3. Utwórz tabelę forums (rysunek 6.8).

CREATE TABLE forums ( forum_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(60) NOT NULL, PRIMARY KEY (forum_id));

Kolejność, w jakiej tworzysz tabele,nie ma oczywiście znaczenia. Ja zacznęod forums. Pamiętaj, że zawsze możeszrozpisać polecenie SQL w kilku wierszachna ekranie, jeżeli tylko będzie Ci takwygodniej.

Rysunek 6.8. Utwórz pierwszą tabelę

Rysunek 6.9. Utwórz drugą tabelę

Tabela ta zawiera tylko dwie kolumny(sytuacja taka ma często miejsce w przypadkuznormalizowanych baz danych). Ponieważ niespodziewam się, że tabela ta będzie zawieraćdużą liczbę rekordów, klucz główny otrzymałtyp TINYINT. Jeśli chcesz, by baza zawierałarównież opis każdego forum, możesz dodaćdo tej tabeli kolumnę typu VARCHAR(255).

4. Utwórz tabelę messages (rysunek 6.9).

CREATE TABLE messages ( message_id INT UNSIGNED NOT NULL AUTO_INCREMENT, forum_id TINYINT UNSIGNED NOT NULL, parent_id INT UNSIGNED NOT NULL, user_id MEDIUMINT UNSIGNED NOT NULL, subject VARCHAR(100) NOT NULL, body LONGTEXT NOT NULL, date_entered TIMESTAMP NOT NULL, PRIMARY KEY (message_id));

W tym przypadku klucz główny musi byćzdecydowanie bardziej pojemny, ponieważspodziewam się, że tabela ta będzie zawieraćbardzo dużo rekordów. Trzy kolumny będącekluczami obcymi — forum_id, parent_idi user_id — będą mieć taki sam rozmiar i typjak ich odpowiedniki będące kluczamigłównymi w innych tabelach. Pole subjectzostało ograniczone do 100 znaków, a polebody może zawierać znaczną ilość tekstu. Poledate_entered otrzymało typ TIMESTAMP. Będzieono przechowywać datę i czas dodania rekordu.Jego wartość zostanie automatyczniezaktualizowana (bieżącą datą i czasem)w momencie wstawienia rekordu (właśniew ten sposób zachowuje się typ TIMESTAMP).

Projekt bazy danych

Rozdział 6.

188

5. Utwórz tabelę users (rysunek 6.10).

CREATE TABLE users ( user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(30) NOT NULL, pass CHAR(40) NOT NULL, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(40) NOT NULL, email VARCHAR(80) NOT NULL, PRIMARY KEY (user_id));

Większość kolumn tej tabeli wykorzystujedefinicje znane z tabeli users bazy sitenameużywanej w poprzednich dwóch rozdziałach.Kolumna pass jest typu CHAR(40), ponieważdla haseł będę stosować funkcję SHA1(), którazwraca zawsze łańcuch o długości 40 znaków(patrz rozdział 5.).

6. Jeśli chcesz, możesz upewnić się, że bazadanych ma taką strukturę jak powinna(rysunek 6.11).

SHOW TABLES;SHOW COLUMNS FROM forums;SHOW COLUMNS FROM messages;SHOW COLUMNS FROM users;

Ten krok jest opcjonalny, ponieważ MySQLi tak wyświetla informacje o pomyślnymwykonaniu każdego wprowadzanegopolecenia. Warto jednak przypomniećsobie strukturę bazy danych.

Wskazówka

W przypadku połączenia kluczgłówny-klucz obcy (na przykład forum_idtabeli forum z forum_id tabeli messages),obie kolumny muszą być tego samego typu(w naszym przykładzie: TINYINT UNSIGNEDNOT NULL).

Rysunek 6.10. Trzecia i ostatnia tabela w bazie Rysunek 6.11. Sprawdź strukturę bazy za pomocą poleceniaSHOW

Proj

ekt

bazy

dan

ych


189

Wypełnianie bazy danych

W rozdziale 15. napiszemy w PHP interfejsWWW dla bazy forums. Będzie on dostarczaćstandardowego sposobu wypełniania bazydanymi (poprzez rejestrowanie sięużytkowników i umieszczanie wiadomościna forum). Zanim jednak dojdziemy do tegopunktu, musisz się jeszcze sporo nauczyć.Dlatego na razie wypełnimy bazę danymiza pomocą klienta mysqla. Możesz wykonaćpo kolei poniższe kroki lub skorzystaćz gotowych poleceń SQL-a dołączonych doprzykładów zamieszczonych na serwerze ftp.

Rysunek 6.12. Dodawanie rekordów do tabeli forums

Rysunek 6.13. Dodawanie rekordów do tabeli users

Aby zapełnić bazę danymi:

1. Dodaj kilka nowych rekordów do tabeli forums(rysunek 6.12).

INSERT INTO forums (name) VALUES ('MySQL'),('PHP'), ('Programowanie'), ('HTML'),('CSS'), ('Bazy danych');

Ponieważ tablica messages jest zależna odwartości odczytywanych z tabel forums i users,wypełnię najpierw te ostatnie. W przypadkupowyższego polecenia INSERT wystarczy jedyniepodać wartość kolumny name (MySQLautomatycznie nada wartość kolumnie forum_id).

2. Dodaj nowe rekordy do tabeli users(rysunek 6.13).

INSERT INTO users (username, pass,first_name, last_name, email) VALUES('janek', SHA1('haslo'),'Jan', 'Kowalski', '[email protected]'),('ak', SHA1('haselko'),'Arkadiusz', 'Kaczmarek', '[email protected]'),('GosiaM', SHA1('tajne'), 'Małgorzata','Malinowska', '[email protected]');

Jeśli masz wątpliwości co do składni poleceniaINSERT lub zastosowania funkcji SHA1(), skorzystajz informacji podanych w rozdziale 5.

Projekt bazy danych

Rozdział 6.

190

3. Wstaw nowe rekordy do tabeli messages(patrz rysunek 6.14).

SELECT * FROM forums;SELECT user_id, username FROM users;INSERT INTO messages (forum_id,parent_id, user_id, subject, body)VALUES(1, 0, 1, 'Pytanie na temat normalizacji','Nie rozumiem jednej rzeczy. Dla drugiejpostaci normalnej (2NF) podano...'),

(1, 0, 2, 'Projekt bazy danych', 'Projektujęnową bazę danych i natrafiłem na pewienproblem. Ile tabel powinna mieć mojabaza?...'),

(1, 2, 1, 'Projekt bazy danych', 'Liczbatabel w Twojej bazie danych...'),

(1, 3, 2, 'Projekt bazy danych', 'OK,dziękuję!'),

(2, 0, 3, 'Błędy PHP', 'Próbuję uruchomićskrypty z rozdziału 3. i pierwszy skryptkalkulatora nie działa. Gdy akceptujęformularz...');

Ponieważ dwa pola tabeli messages (forum_idoraz user _id) odwołują się do wartościprzechowywanych w innych tabelach,przed wstawieniem nowych rekordówdokonam wyboru tych wartości. Na przykład,gdy użytkownik janek utworzy nową wiadomośćna forum MySQL-a, musisz użyć forum_idrówny 1 i user_id równy 1.

Sprawę dodatkowo komplikuje kolumnaparent_id. Musi ona zawierać wartośćmessage_id tej wiadomości, na którąodpowiedź stanowi nowa wiadomość.Druga wiadomość umieszczona w baziedanych będzie mieć message_id równy2 i wobec tego każda wiadomość stanowiącaodpowiedź na tę wiadomość będzie musiałamieć wartość parent_id równą 2.

W tworzonych przez Ciebie skryptachPHP, wszystko to będzie wyglądałoznacznie prościej. Muszę teraz jednakwyłożyć Ci całą teorię, posługując sięterminologią języka SQL.

Zwróć również uwagę, że nie wprowadziłemwartości dla pola date_entered. MySQLautomatycznie umieści w nim bieżącądatę i czas, ponieważ jest to kolumnatypu TIMESTAMP.

4. Powtórz kroki od 1. do 3., aby zapełnićbazę danymi.

We wszystkich kolejnych przykładachw tym rozdziale będę wykorzystywał bazę,którą właśnie zapełniłem. Jeśli chcesz,możesz wykonać u siebie te same poleceniaINSERT co ja lub utworzyć swoje własne.

Rysunek 6.14. W przypadku znormalizowanych baz danych bardzo często spotkasz się z sytuacją,w której, aby wstawić jakiś rekord do tabeli, będziesz musiał znać wartości przechowywane w innych tabelach

Proj

ekt

bazy

dan

ych


191

ZłączeniaPonieważ relacyjne bazy danych mają złożonąstrukturę, aby wydobyć te informacje, którenajbardziej nas interesują, musimy czasemwykonać jakieś niestandardowe zapytanie.Na przykład, jeśli chcesz dowiedzieć się, jakiewiadomości zawiera forum MySQL-a, musisznajpierw dowiedzieć się, jaka jest wartośćforum_id dla tego forum, a następnie użyćjej do pobrania wszystkich rekordów tabelimessage, które mają taką wartość forum_id.Jak z tego wynika, to proste zadanie wymagawykonania dwóch zapytań. Jednak stosujączłączenie, możesz poradzić sobie z nimw jednym kroku.

Złączenie jest zapytaniem SQL-a używającymdwóch lub więcej tabel i tworzącym wirtualnątabelę wyników. W specyfikacji SQL-awystępują dwa główne typy złączeń:wewnętrzne i zewnętrzne (oba mają szeregpodtypów).

Złączenie wewnętrzne zwraca wszystkierekordy podanych tabel, dla których zachodzidopasowanie. Na przykład, aby uzyskaćwszystkie wiadomości zamieszczonena forum MySQL, powinieneś użyćnastępującego złączenia wewnętrznego(rysunek 6.15):

SELECT * FROM messages INNER JOIN forums ON messages.forum_id = forums.forum_id WHERE forums.name = 'MySQL'

Złączenie to wybiera wszystkie kolumny obu tabel,jeśli spełnione są dwa warunki. Po pierwsze,kolumna forums.name musi zawierać wartośćMySQL (której odpowiada wartość forum_idrówna 1). Po drugie, wartość forum_id w tabeliforums musi odpowiadać wartości forum_idw tabeli messages. Ze względu na sprawdzenierówności dotyczące pól w różnych tabelach(messages.forum_id = forums.forum_id) złączenietakie nazywa się równościowym.

Złączenia wewnętrzne możesz również zapisywaćbez stosowania klauzuli INNER JOIN:

SELECT * FROM messages, forums WHEREmessages.forum_id = forums.forum_idAND forums.name = 'mySQL'

Jeżeli wybierasz dane z wielu tabel i kolumn,a w kilku tabelach występują kolumny o takiej samejnazwie, musisz zastosować notację z kropką(tabela.kolumna). W przypadku relacyjnych bazdanych robi się to praktycznie zawsze, ponieważklucz główny jednej tabeli ma taką samą nazwęjak obcy klucz drugiej. Jeżeli nie wskażeszjednoznacznie kolumny, do której się odwołujesz,zobaczysz komunikat o błędzie (rysunek 6.16).

Rysunek 6.15. To złączenie zwraca kolumny obu tabel dla rekordów, dla których wartościforum_id są równe MySQL (1)

Złączenia

Rysunek 6.16. Ogólne odwołanie do kolumnywystępującej w kilku tabelach spowodujewystąpienie błędu niejednoznaczności

Rozdział 6.

192

Złączenia zewnętrzne różnią się od złączeńwewnętrznych tym, że potrafią zwracać rekordy,które nie spełniają wyrażenia warunkowego.Istnieją trzy podtypy złączeń zewnętrznych: left(lewe), right (prawe) i full (pełne). Przykładempierwszego podtypu jest następujące złączenie:

SELECT * FROM forumsLEFT JOIN messages ONforums.forum_id = messages.forum_id;

Najważniejsze przy tego rodzaju złączeniachjest to, które tabele należy wymienić jako pierwsze.W powyższym przykładzie w przypadku udanegodopasowania zwrócone zostaną wszystkie rekordytabeli forums wraz ze wszystkimi informacjamiz tabeli messages. Jeśli dla danego rekordu tabeliforums nie można dopasować informacji w tabelimessages, to zamiast nich zostaną zwróconewartości NULL (rysunek 6.17).

Jeżeli w warunku porównania złączeniazewnętrznego bądź wewnętrznego występujekolumna o takiej samej nazwie w obu tabelach,możesz uprościć zapis zapytania, stosującklauzulę USING:

SELECT * FROM messages INNER JOIN forums USING (forum_id) WHERE forums.name = 'mySQL'SELECT * FROM forums LEFT JOIN messages USING (forum_id)

Zanim przejdę do przykładów, jeszcze dwieuwagi. Po pierwsze, ponieważ składniawymagana przy tworzeniu złączeń jest dośćskomplikowana, przy ich pisaniu przydają sięaliasy omówione w rozdziale 5. Po drugie,ponieważ złączenia często zwracają dużodanych, to najlepiej zawsze określić, którekolumny nas interesują, zamiast wybieraćwszystkie.

Rysunek 6.17. To złączenie zewnętrzne zwraca więcej rekordów niż złączenie wewnętrzne, ponieważ zwracawszystkie wiersze pierwszej tabeli. Zapytanie to zwraca również nazwy wszystkich forów, nawet jeśli nie zawierająone jeszcze żadnej wiadomości

Złąc

zeni

a


193

Aby wykorzystać złączenia:

1. Pobierz nazwę forum i temat wiadomościdla każdego rekordu tabeli messages(patrz rysunek 6.18).

SELECT f.name, m.subjectFROM forumsAS f INNER JOIN messages AS mUSING (forum_id) ORDER BY f.name;

Zapytanie to zawiera złączenie wewnętrzne,które spowoduje w efekcie zastąpieniewartości forum_id z tabeli messagesodpowiadającą jej wartością name z tabeliforums dla każdego rekordu tabeli messages.W wyniku otrzymasz zatem temat każdejwiadomości wraz z nazwą forum, do któregonależy ta wiadomość.

Zwróć uwagę, że w połączeniach nadalmożesz używać klauzul ORDER BY.

2. Pobierz temat i datę wprowadzenia każdejwiadomości wysłanej przez użytkownika janek(rysunek 6.19).

SELECT m.subject, DATE_FORMAT(m.date_entered, '%M %D, %Y') AS Date FROM users AS u INNER JOIN messages AS m USING (user_id) WHERE u.username = 'janek';

Również to złączenie używa dwóch tabel,users i messages. Złączenie obu tabel odbywasię za pomocą kolumny user_id i dlategozostała ona umieszczona wewnątrz klauzuliUSING. Wyrażenie warunkowe WHERE pozwalazidentyfikować interesującego nas użytkownika,a funkcja DATE_FORMAT sformatować wartośćpola date_entered.

Rysunek 6.18. Podstawowe złączenie wewnętrznezwraca w tym przykładzie tylko dwie kolumny

Rysunek 6.19. Nieco bardziej skomplikowana wersjazłączenia wewnętrznego tabele users i messages

Złączenia

Rozdział 6.

194

3. Pobierz identyfikator wiadomości, temat oraznazwę forum dla każdej wiadomości, którejautorem jest użytkownik janek (patrzrysunek 6.20).

SELECT m.message_id, m.subject, f.name FROM users AS u INNER JOIN messages AS m USING (user_id) INNER JOIN forums AS f USING (forum_id) WHERE u.username = 'janek';

Powyższe złączenie jest podobnedo zastosowanego w punkcie 2., ale idzie o krokdalej, dołączając trzecią tabelę. Zwróć szczególnąuwagę na sposób konstrukcji złączeniawewnętrznego trzech tabel oraz zastosowaniealiasów w celu łatwiejszego odwoływania siędo tabel i ich kolumn.

4. Dla każdego użytkownika pobierz nazwęużytkownika, temat wiadomości i nazwęforum (patrz rysunek 6.21).

SELECT u.username, m.subject,f.name FROM users AS u LEFT JOINmessages AS m USING (user_id)LEFT JOIN forums AS fUSING (forum_id);

Gdybyś zastosował podobne złączeniewewnętrzne, to użytkownicy, którzy niesą jeszcze autorami żadnej wiadomości,nie zostaliby uwzględnieni (patrz rysunek6.22). Zatem jeśli interesują Cię wszyscyużytkownicy, powinieneś użyć złączeniazewnętrznego. Zwróć uwagę, że tabela,która ma być uwzględniona w całości(users w tym przypadku), musi być podanajako pierwsza tabela złączenia typu left.

Rysunek 6.20. Złączenie wewnętrzne wszystkich trzechtabel bazy

Rysunek 6.21. To złączenie zewnętrzne zwracadla każdego użytkownika temat każdej wiadomościi nazwę każdego forum. Jeśli użytkownik nie umieściłjeszcze żadnej wiadomości na forum (tak jak tomekjwidoczny na dole rysunku), to temat wiadomościi nazwa forum będą mieć dla niego wartości NULL

Złąc

zeni

a


195

Wskazówki

Możesz nawet złączyć tabelę z nią samą.

Złączenia można tworzyć z wykorzystaniemwyrażeń warunkowych odwołujących siędo dowolnych kolumn. Nie muszą onepełnić roli kluczy, choć sytuacja taka jestnajczęstsza.

Stosując składnię bazadanych.tabela.kolumna, możesz wykonywać złączeniatabeli należących do różnych baz danych,o ile tylko wszystkie znajdują się na tymsamym serwerze. Ta metoda nie zadziała,jeżeli bazy komunikują się ze sobąza pośrednictwem sieci.

Złączenia, które nie zawierają klauzuli WHERE(np. SELECT * FROM urls, url_associations),nazywamy złączeniami pełnymi. Zwracają onerekordy z obu tabel. W przypadku dużych tabelilość zwracanych danych może stanowić pewienproblem.

Nigdy nie zostanie zwrócona wartość NULLwystępująca w kolumnie, do której odwołujemysię w złączeniu. To dlatego, że nie jest ona równażadnej innej wartości, w tym także innymwartościom NULL.

Rysunek 6.22. To złączenie wewnętrzne nie zwróciużytkownika, który nie umieścił jeszcze żadnejwiadomości na forum (tak jak tomekj widocznyna dole rysunku 6.21)

Złączenia

Rozdział 6.

196

Aby zgrupować dane:

1. Policz zarejestrowanych użytkowników(patrz rysunek 6.23).

SELECT COUNT(user_id) FROM users;

COUNT() jest prawdopodobnienajpopularniejszą z funkcji agregujących.Z jej pomocą możesz szybko zliczyćrekordy tak jak w przypadku tabeli users.Zwróć uwagę, że nie wszystkie zapytaniastosujące funkcje agregujące używająklauzuli GROUP BY.

2. Policz, ile razy każdy użytkownik umieściłna forum swoją wiadomość (patrzrysunek 6.24).

SELECT username,COUNT(message_id) AS NumberFROM users LEFT JOIN messages AS mUSING (user_id) GROUP BY (m.user_id);

Grupowanie wyników zapytaniaW poprzednim rozdziale przedstawiłem dwieróżne klauzule — ORDER BY i LIMIT — mające wpływna wynik zapytania. Pierwsza z nich umożliwiaokreślenie uporządkowania zwracanych rekordów,a druga pozwala określić, które z rekordów będącychwynikiem zapytania zostaną w rzeczywistościzwrócone. Kolejna klauzula, GROUP BY, grupujezwracane dane w podobne do siebie bloki informacji.Na przykład, aby pogrupować wszystkie wiadomościwedług forum, wpisz:

SELECT * FROM messages GROUP BY forum_id;

Zwracane dane nie będą miały postaci pojedynczychrekordów, tylko grup informacji. Zamiast dużejliczby wiadomości pochodzących z danego forumzobaczysz je wszystkie w postaci jednego rekordu.Ten przykład nie jest być może szczególnieprzydatny, ale wystarczający do przedstawieniasamej koncepcji.

Z klauzulą GROUP BY często używa się jednej z funkcjiagregujących przedstawionych w tabeli 6.7 (funkcjitych można używać również niezależnie od klauzuliGROUP BY).

Na wyrażenie GROUP BY możesz oczywiście nakładaćwarunki WHERE, ORDER BY i LIMIT, tworząc zapytaniaw rodzaju:

SELECT kolumny FROM tabela WHERE warunek GROUP BY kolumna ORDER BY kolumna LIMIT x, y;

Rysunek 6.23. To zapytanie grupujące zwracaliczbę wartości user_id w tabeli users

Grup

owan

ie w

ynik

ów z

apyt

ania

Tabela 6.7. Funkcje grupujące MySQL-a

Funkcja Przeznaczenie

AVG() Zwraca średnią wartość z podanejkolumny.

MIN() Zwraca najmniejszą wartośćz podanej kolumny.

MAX() Zwraca największą wartość z podanejkolumny.

SUM() Zwraca sumę wszystkich wartościz danej kolumny.

COUNT() Zwraca liczbę rzędów.GROUP_CONCAT() Zwraca konkatenację wartości

kolumny.


197

Jest to rozszerzona wersja zapytaniawystępującego w kroku 1., ale zamiastzliczać samych użytkowników, podajeliczbę wiadomości związanych z każdymużytkownikiem. Zastosowanie złączeniaumożliwia dostęp do informacji w dwóchtabelach. Aby uwzględnić użytkowników,którzy nie byli jeszcze aktywni na forum,zastosowałem złączenie wewnętrzne.

3. Znajdź dwóch najaktywniejszychużytkowników forum (rysunek 6.25).

SELECT username, COUNT(message_id) AS Number FROM users LEFT JOIN messages AS m USING (user_id) GROUP BY (m.user_id) ORDER BY Number DESC LIMIT 2;

Rysunek 6.24. To zapytanie GROUP BY podaje liczbęwiadomości umieszczonych na forum przez każdegoużytkownika

Rysunek 6.25. Klauzula ORDER BY pozwalaposortować autorów na podstawie liczby wiadomościumieszczonych na forum. Klauzula LIMIT powoduje,że podane zostaną tylko dwa wyniki

W zapytaniach z klauzulą GROUP BY nadal możeszsortować wyniki zapytań. Proces ten możeszuprościć, stosując alias Number dla wartościCOUNT(message_id).

Wskazówki

Jak już miałeś okazję się przekonać, NULLjest bardzo specyficzną wartością. Interesującejest to, że wyrażenie GROUP BY umieszczawszystkie wartości NULL w tej samej grupie,ponieważ wszystkie one wykazują taki sam„brak wartości”.

Funkcja COUNT() zlicza jedynie wystąpieniawartości różnych od NULL. Dlatego pamiętaj,aby użyć jej dla każdej kolumny (*) lubdla kolumn, które nie zawierają wartości NULL(na przykład klucza głównego). Jeśli zapytanieużyte w punkcie 2. (rysunek 6.24) stosowałobyfunkcję COUNT() dla wszystkich kolumn (*)zamiast tylko dla message_id, to pokazałobybłędną wartość równą 1 dla wszystkichużytkowników, którzy nie napisali jeszcze żadnejwiadomości. Stałoby się tak, ponieważ zapytaniezwróciłoby dokładnie jeden rekord dla każdegoz tych użytkowników.

Opanowanie klauzuli GROUP BY i funkcjiomówionych w tym podrozdziale zajmie Citrochę czasu. Za każdym razem, gdy użyjesznieprawidłowej składni, zostanie zgłoszonybłąd. Poeksperymentuj trochę z monitoremmysqla, aby zapamiętać, jak dokładnie musząbyć sformułowane wszystkie zapytania, którychbędziesz chciał używać w swoich aplikacjach.

Z klauzulą GROUP BY związana jest klauzulaHAVING, która zachowuje się jak klauzula WHEREzastosowana do grupy rekordów.

Grupowanie w

yników zapytania

Rozdział 6.

198

IndeksyMechanizm indeksów to specjalny systemwykorzystywany w bazach danych do poprawyich wydajności. Zakładając na swych tabelachindeksy, sprawiasz, że MySQL przywiązuje wagędo określonych kolumn. Aby indeksy mogły byćwykorzystywane w sposób maksymalnie efektywny,MySQL przechowuje je w osobnych plikach.

Na każdej tabeli można założyć co najmniej 16indeksów, a każdy indeks może obejmować do 15kolumn. Choć rozciąganie indeksu na kilka kolumnmoże wydawać się zastanawiające, przydaje się topodczas częstego przeszukiwania tego samegozestawu kolumn (np. imion i nazwisk, miasti województw).

Nie powinieneś jednak przesadzać z indeksowaniem.Choć przyspiesza ono odczytywanie informacjiz bazy, to jednocześnie spowalnia ich modyfikowanie(ponieważ informacje o zmianach muszą trafićdo indeksów). Najlepszym wyjściem jest zakładanieindeksów na kolumnach, które:

Często wymieniane są w zapytaniachpo klauzulach WHERE.

Często wymieniane są w zapytaniachpo klauzulach ORDER BY.

Często wykorzystywane są jako główny punktzłączenia.

Mają wiele różnych wartości (nie powinno sięindeksować kolumn zawierających wielepowtarzających się wartości).

W MySQL-u występują cztery rodzaje indeksów:INDEX (standardowy), UNIQUE (który wymaga,aby w każdym rzędzie występowały inne wartości),FULLTEXT (do wyszukiwań FULLTEXT) oraz PRIMARY KEY(klucz główny, będący szczególnym przypadkiemindeksu UNIQUE). Pamiętaj, że dana kolumna możebyć tylko raz zaindeksowana i wobec tego wybierzdla niej najodpowiedniejszy rodzaj indeksu.

Wyposażony w te wiadomości zmodyfikujębazę forum, dodając do niej indeksy.W tabeli 6.8 podałem indeksy utworzonedla poszczególnych kolumn za pomocąpolecenia ALTER omówionego w ramce.

Inde

ksy Tabela 6.8. Indeksy używane przez bazę danych

forum. Nie wszystkie kolumny są indeksowane,a dwa indeksy zostały utworzone dla par kolumn:user.pass i user.username oraz messages.bodyi messages.subject

Indeksy bazy danych forum

Nazwa kolumny Tabela Typ indeksu

forum_id forums PRIMARY

name forums UNIQUE

message_id messages PRIMARY

forum_id messages INDEX

parent_id messages INDEX

user_id messages INDEX

body/subject messages FULLTEXT

date_entered messages INDEX

user_id users PRIMARY

username users UNIQUE

pass/username users INDEX

email users UNIQUE


199

Rysunek 6.26. Dla kolumny name dodano nowyindeks, który poprawi efektywność zapytańi zapobiegnie wprowadzaniu powtarzających sięwartości

Aby dodać indeks do istniejącej tabeli:

1. Załóż indeks na kolumnie name w tabeli forums(patrz rysunek 6.26).

ALTER TABLE forums ADD UNIQUE (name);

Tabela forums ma już założony indeks typu kluczgłówny na kolumnie forum_id. Ponieważ namerównież jest polem, do którego będą częsteodwołania i którego wartość musi być unikatowaw każdym rekordzie, zakładam na nim indeksUNIQUE.

Modyfikowanie tabelPolecenie ALTER służy przede wszystkim do modyfikowania struktury tabeli w bazie danych.Najczęściej oznacza to dodawanie, usuwanie bądź modyfikację kolumn, ale również dodawanieindeksów. Polecenia ALTER można również użyć do zmiany nazwy tabeli. Teoretycznie przy dobrymprojekcie bazy jej struktura nie powinna stwarzać żadnych problemów. Jednak w praktyce częstozdarza się wprowadzać w niej pewne zmiany. Składnia polecenia ALTER jest następująca: ALTER TABLE nazwa_tabeli KLAUZULA;

Ponieważ klauzul, które można zastosować, jest bardzo wiele, w tabeli 6.9 wymieniłem tylko najczęściejstosowane. Pełną listę znajdziesz w podręczniku MySQL-a.

Indeksy

Tabela 6.9. Popularne warianty polecenia ALTER (gdzie t reprezentuje nazwę tabeli, c nazwę kolumny,a i nazwę indeksu. Pełną specyfikację polecenia znajdziesz w dokumentacji MySQL-a

Klauzule, które można stosować w poleceniach ALTER TABLE

Klauzula Sposób użycia Znaczenie

ADD COLUMN ALTER TABLE t ADD COLUMN c TYP Dodaje nową kolumnę na końcu tabeli.CHANGE COLUMN ALTER TABLE t CHANGE COLUMN c c TYP Pozwala zmienić typ danych i właściwości kolumny.DROP COLUMN ALTER TABLE t DROP COLUMN c Usuwa kolumnę z tabeli razem z wszystkimi jej

danymi.ADD INDEX ALTER TABLE t ADD INDEX i (c) Zakłada nowy indeks na kolumnie c.

DROP INDEX ALTER TABLE t DROP INDEX i Usuwa istniejący indeks.RENAME AS ALTER TABLE t RENAME AS nowa_t Zmienia nazwę tabeli.

Rozdział 6.

200

Jesli podczas wykonywania tego zapytaniapojawi się komunikat o błędziestwierdzający, że typ tabeli nie obsługujeindeksów FULLTEXT (patrz rysunek 6.28),na razie opuść ten wiersz zapytaniai sprawdź w następnym podrozdziale,w jaki sposób zmienić typ tabeli.

2. Załóż indeksy dla tabeli messages(patrz rysunek 6.27).

ALTER TABLE messagesADD INDEX(forum_id),ADD INDEX(parent_id),ADD INDEX(user_id),ADD FULLTEXT(body, subject),ADD INDEX(date_entered);

Ta tabela będzie mieć najwięcej indeksów,ponieważ jest najważniejszą tabelą w baziei zawiera trzy klucze obce (forum_id, parent_idi user_id), dla których należy założyć osobneindeksy. Dodatkowo pola body i subjectotrzymują indeks FULLTEXT używany podczaswyszukiwań typu FULLTEXT w dalszej częścitego rozdziału. Również kolumna date_enteredotrzymuje indeks, ponieważ będzie używanaprzez klauzule ORDER BY (do sortowaniawiadomości po dacie).

Rysunek 6.27. Do tabeli messages dodano kilkaindeksów naraz. MySQL raportuje wykonanieoperacji oraz podaje liczbę rekordów, którychdotyczyła (powinna to być liczba wszystkichrekordów tabeli)

Rysunek 6.28. Indeksów FULLTEXT nie można stosować dla wszystkich typówtabel. Jeśli napotkasz ten komunikat o błędzie, to rozwiązanie znajdzieszw tym rozdziale w części „Stosowanie różnych typów tabel”

Inde

ksy


201

3. Dodaj indeksy do tabeli users(patrz rysunek 6.29).

ALTER TABLE usersADD UNIQUE (username),ADD INDEX (pass, username),ADD UNIQUE (email);

Tabela users będzie mieć dwa indeksyUNIQUE oraz jeden indeks założony na dwóchkolumnach. Indeksy UNIQUE zastosowałemw tym przypadku, ponieważ chcę zapobiecmożliwości zarejestrowania dwóchużytkowników o tej samej nazwielub wielokrotnego zarejestrowaniaużytkownika o tym samym adresie e-mail.

Rysunek 6.29. Indeksy zostały równieżdodane do trzeciej tabeli

Indeks założony na kolumnach pass i usernamema za zadanie poprawić efektywność zapytańwykonywanych podczas uwierzytelnianiaużytkownika, gdy kombinacja tych dwóchkolumn będzie używana w wyrażeniuwarunkowym WHERE.

4. Obejrzyj bieżącą strukturę wszystkich tabel(patrz rysunek 6.30).

DESCRIBE forums;DESCRIBE messages;DESCRIBE users;

Polecenie DESCRIBE języka SQL zwracainformacje o nazwach kolumn występującychw tabeli i w ich typach, a także o kolejności,w jakiej one występują i o pozakładanychindeksach. Podaje ono także, czy dane polemoże przyjmować wartość NULL, czy określonodla niego jakąś wartość domyślną itd.

Rysunek 6.30. Aby zobaczyć szczegółowe informacje na temat struktury tabeli,użyj polecenia DESCRIBE. Kolumna Key informuje o indeksach

Indeksy

Rozdział 6.

202

Wskazówki

Próba założenia indeksu UNIQUE na kolumniezawierającej duplikaty spowoduje błąd, a indeksnie zostanie utworzony.

Tworząc indeks, możesz nadać mu nazwę.

ALTER TABLE tabela ADD INDEX nazwa_indeksu (kolumna);

Jeżeli tego nie zrobisz, otrzyma on nazwękolumny, na której go zakładasz.

Słowo COLUMN w większości poleceń ALTERjest opcjonalne.

Załóżmy, że stworzyłeś indeks dla kilku kolumn:

ALTER TABLE tabela ADD INDEX (kol1, kol2, kol3)

Powstały indeks umożliwia efektywneprzeszukiwanie kolumny kol1, kombinacjikolumn kol1 i kol2 oraz kombinacji wszystkichtrzech podanych kolumn. Nie zwiększaefektywności przeszukiwania kolumny kol2i kol3 lub ich kombinacji.

Inde

ksy


203

Stosowanieróżnych typów tabeliMySQL obsługuje kilka różnych typów tabeli(typ tabeli nazywany bywa również silnikiemprzechowywania). Każdy z tych typów ma innewłaściwości, odrębne ograniczenia (dotycząceilości przechowywanych danych)i charakteryzuje się inną efektywnościądziałania w określonych sytuacjach. Jednakinterakcje użytkownika (w sensie wykonywaniazapytań) z różnymi typami tabel są spójne.

Najważniejszym typem tabeli jest MyISAM. Jeston domyślnym typem tabeli na wszystkichplatformach oprócz Windows. Tabele tegotypu są najodpowiedniejsze dla większościaplikacji i umożliwiają szybkie wykonywaniepoleceń SELECT oraz INSERT. Ich podstawowąwadą jest to, że nie obsługują transakcji.

Kolejnym typem tabeli pod względempopularności zastosowań jest InnoDB, który jestdomyślnym typem tabeli MySQL-a w systemieWindows. Tabele InnoDB obsługują transakcjei umożliwiają szybkie wykonywanie poleceńUPDATE. Jednak silnik InnoDB jest w ogólnymprzypadku wolniejszy niż MyISAM i wymagawiększej przestrzeni dyskowej serwera. Tabeletypu InnoDB nie obsługują również indeksówtypu FULLTEXT (i dlatego mogłeś napotkać błądprzedstawiony na rysunku 6.28, jeśli używaszWindows).

Aby określić, którego silnika chcemy używać,na końcu polecenia CREATE dodajemy specjalnąklauzulę:

CREATE TABLE tabela (kolumna1 TYPKOLUMNY,kolumna2 TYPKOLUMNY...) ENGINE = INNODB

Jeśli tworząc tabele, nie podamy ich typu, to MySQLużyje typu domyślnego.

Typ istniejącej tabeli możemy zmienić za pomocąpolecenia ALTER:

ALTER TABLE tabela ENGINE = MYISAM

Ponieważ następny przykład w tym rozdziale będziewymagać tabeli typu MyISAM, to pokażę najpierwkroki konieczne do skonfigurowania odpowiedniegotypu tabeli na przykładzie tabeli messages. W kilkupierwszych krokach dowiesz się, jak sprawdzićwykorzystywany typ silnika, (ponieważ zmianatypu tabeli może nie być konieczna). Stosow

anie różnych typów tabeli

Rozdział 6.

204

Aby zmienić typ tabeli:

1. Obejrzyj aktualną informację o tabeli(patrz rysunek 6.31).

SHOW TABLE STATUS;

Polecenie SHOW TABLE STATUS zwraca wieleprzydatnych informacji o tabelach bazy danych.Informacje te odczytuje się niewygodnie,ponieważ mają one postać szerokiej tabelizajmującej wiele wierszy. W każdym wierszuznajduje się najpierw nazwa tabeli, a następniejej typ. Zwykle jest nim MyISAM lub InnoDB.

2. Jeśli to konieczne, zmień typ tabelimessages na MyISAM (rysunek 6.32).

ALTER TABLE messages ENGINE=MYISAM;

Jeśli w punkcie 1. okazało się, że typtabeli jest inny niż MyISAM, to zmień goza pomocą powyższego polecenia(nie musisz zwracać uwagi na małe i dużelitery w typie tabeli). Jeśli wykonałeśdomyślną instalację i konfiguracjęMySQL-a, to zmiana typu tabeli niebędzie potrzebna w systemie Mac OS X,ale musisz jej dokonać w systemieWindows.

Rysunek 6.31. Zanim zmienisz typ tabeli, sprawdź go za pomocąpolecenia SHOW TABLE STATUS

Rysunek 6.32. Zmieniłem typ tabeli (lub silnikprzechowywania) za pomocą polecenia ALTERSt

osow

anie

róż

nych

typ

ów t

abel

i


205

Rysunek 6.33. Aby uzyskać bardziej czytelnąpostać wyników zapytania, użyłem znacznika \G

3. Możesz sprawdzić zmianę typu tabeli, wykonującponownie polecenie SHOW TABLE STATUS.

Wskazówki

Aby ułatwić odczytanie wyników dowolnegozapytania, możesz dodać w kliencie mysqlaparametr \G na końcu zapytania (rysunek 6.33):

SHOW TABLE STATUS \G

Znacznik ten informuje, że tabela wynikówpowinna zostać wyświetlona pionowo zamiastpoziomo. Zwróć uwagę, że nie musisz w tymprzypadku użyć średnika kończącego polecenieSQL, ponieważ funkcję tę spełnia znacznik \G.

W tej samej bazie danych mogą występowaćróżne typy tabel. W zależności od domyślnegotypu tabel w Twojej instalacji MySQL-a,stwierdzenie to może już być prawdziwe dla bazydanych forum. To samo możesz zaobserwowaćw przypadku bazy danych ecommerce, w którejtabele klientów i produktów są typu MyISAM,ale tabela zamówień jest typu InnoDB(aby umożliwić stosowanie transakcji).

Stosowanie różnych typów

tabeli

Rozdział 6.

206

Wyszukiwanie FULLTEXTW rozdziale 5. przedstawiłem słowo kluczowe LIKEumożliwiające proste porównania łańcuchów,na przykład:

SELECT * FROM usersWHERE last_name LIKE 'Kowalsk%';

W ten sposób nie wykonamy jednak wyszukiwańz zastosowaniem wielu słów. Aby stało się tomożliwe, wprowadzono wyszukiwania FULLTEXT.

Wyszukiwania FULLTEXT wymagają obecnościindeksu FULLTEXT, który można założyć jedyniena tabelach typu MyISAM. W następnych przykładachbędę używać tabeli messages bazy forum. Jeśliutworzona przez Ciebie tabela messages nie jest typuMyISAM i (lub) nie ma indeksu FULLTEXT założonegona kolumnach body i subject, wykonaj kroki podanena kilku poprzednich stronach, aby to zmienić.

Wskazówki

Wstawianie rekordów do tabel, na którychzałożono indeks FULLTEXT, może być znaczniewolniejsze ze względu na skomplikowanąnaturę tego indeksu.

Indeks FULLTEXT możemy zakładać na wielukolumnach, jeśli wszystkie mają byćprzeszukiwane.

Wyszukiwania FULLTEXT mogą służyćdo implementacji prostych usług wyszukiwania.Ponieważ jednak indeks FULLTEXT stosujesię do jednej tabeli, to usługi wyszukiwaniainformacji w wielu tabelach wymagajązastosowania bardziej zaawansowanychrozwiązań.

Realizacja podstawowego wyszukiwaniaFULLTEXT

Po założeniu indeksu FULLTEXT możeszwydawać zapytania przy użyciu funkcji MATCHi AGAINST w wyrażeniach warunkowych WHERE:

SELECT * FROM tabela WHERE MATCH (kolumna) AGAINST('poszukiwane_słowa')

MySQL zwróci pasujące rekordy począwszyod spełniających warunek wyszukiwaniaw największym stopniu. Podczas wyszukiwaniastosowane są następujące reguły:

Łańcuchy rozbijane są na poszczególnesłowa.

Słowa krótsze niż 4-znakowe są ignorowane.

Słowa często używane są ignorowane.

Jeśli ponad połowa rekordów spełniawarunek wyszukiwania, to nie jest zwracanyżaden rekord.

Zwłaszcza ostatnia reguła jest zaskakującadla wielu użytkowników, którzy rozpoczynajądopiero przygodę z wyszukiwaniami FULLTEXTi dziwią się, dlaczego nie zwracają one żadnychwyników. Jeśli wypełnisz tabelę zbyt małąliczbą rekordów, to MySQL nie zwróciwłaściwych wyników.

Wys

zuki

wan

ie F

ULL

TEXT


207

Aby wykonać wyszukiwanie FULLTEXT:

1. Wypełnij tabelę messages dużą ilościądanych — zwłaszcza w polu body.

Możesz użyć w tym celu poleceń INSERTściągniętych z serwera ftp.

2. Wykonaj proste wyszukiwanie FULLTEXTdla słowa skrypt (patrz rysunek 6.34).

SELECT subject, body FROM messages WHERE MATCH (body, subject) AGAINST ('skrypt');

Ten prosty przykład zwróci wynik podwarunkiem, że co najmniej jeden rekord i mniejniż połowa rekordów tabeli messages zawierasłowo tabela w polu body lub subject. Zwróćuwagę, że kolumny, do których odwołuje sięMATCH, muszą być tymi samymi kolumnami,jakie podałeś, tworząc indeks FULLTEXT. W tymprzykładzie mogłeś zatem użyć albo body,subject, albo subject, body, ale nie samych bodylub subject (patrz rysunek 6.35).

Rysunek 6.34. Podstawowe wyszukiwanie FULLTEXT

Rysunek 6.35. Zapytanie FULLTEXT możesz wykonać jedynie na tej samejkolumnie lub kombinacji kolumn, dla której utworzyłeś indeks FULLTEXT.W tym przypadku zapytanie zakończy się błędem, mimo że kombinacjakolumn body i subject ma indeks FULLTEXT

Wyszukiw

anie FULLTEXT

Rozdział 6.

208

3. Wykonaj to samo wyszukiwanie FULLTEXT,pokazując dodatkowo stopień spełnieniawarunku przez poszczególne rekordy (patrzrysunek 6.36).

SELECT subject, body, MATCH (body, subject) AGAINST('skrypt') AS R FROM messages WHERE MATCH (body, subject) AGAINST('skrypt');

Jeśli użyjesz tego samego wyrażeniaMATCH...AGAINST jako wybieranej wartości,to pokazany zostanie również stopień spełnieniawarunku przez rekordy.

4. Wykonaj wyszukiwanie FULLTEXT stosując kilkasłów (rysunek 6.37).

SELECT subject, body FROM messages WHERE MATCH (body, subject) AGAINST('projekt formularz');

W tym przypadku rekord spełni warunekwyszukiwania, jeśli pole subject lub bodybędzie zawierać jedno z podanych słów. Rekordzawierający oba słowa uzyska wyższy stopieńspełnienia warunku.

Wskazówki

Pamiętaj, że jeśli wyszukiwanie FULLTEXTnie zwróci żadnych rekordów, to albo żadenrekord nie spełnia warunku wyszukiwania,albo spełnia go ponad połowa rekordów.

Dla uproszczenia wszystkie zapytaniaprzedstawione w tym podrozdzialezapisałem jako najprostsze polecenia SELECT.Wyszukiwania FULLTEXT można też używaćw bardziej skomplikowanych zapytaniachczy złączeniach.

MySQL zawiera w kodzie źródłowymlistę kilkuset słów pospolitych, które sąignorowane w wyszukiwaniach FULLTEXT.

Minimalną długość wyszukiwanego słowa(wynoszącą domyślnie 4 znaki) możnakonfigurować.

Domyślnie wyszukiwania FULLTEXTnie rozróżniają małych i wielkich liter.

Rysunek 6.36. Możesz również zobaczyć stopień spełniania warunku wyszukiwania przez poszczególne rekordy

Rysunek 6.37. Wyszukiwanie FULLTEXT można także stosować dla wielu słów

Wys

zuki

wan

ie F

ULL

TEXT


209

Wyszukiwania FULLTEXTw trybie Boolean

Bardziej zaawansowane wyszukiwania FULLTEXTmożna wykonywać, korzystając z trybuBoolean. W trybie tym używamy wyrażeniaIN BOOLEAN MODE wewnątrz klauzuli AGAINST:

SELECT * FROM tabela WHERE MATCH(kolumny) AGAINST('poszukiwane_słowa' IN BOOLEAN MODE)

W trybie Boolean możemy stosować szeregoperatorów (tabela 6.10) określających sposóbtraktowania każdego wyszukiwanego słowa:

SELECT * FROM tabela WHERE MATCH(kolumny) AGAINST('+baza -mysql' IN BOOLEAN MODE)

W tym przykładzie rekord zostanie wybrany,jeśli zawiera słowo baza i nie zawiera słowamysql. Jako słabszą formę operatorareprezentowanego przez znak minus (-)możemy stosować tyldę (~). Oznacza ona,że rekord może zawierać dane słowo,ale wtedy w mniejszym stopniu spełniawarunek wyszukiwania.

Znak maski (*) umożliwia wyszukiwanie różnychwariantów słowa. Na przykład zastosowanie gow słowie bezpiecz* spowoduje wyszukanie rekordówzawierających słowa bezpieczny, bezpieczeństwoi tym podobnych. Dwa kolejne operatoryumożliwiają określenie, że dane słowo jest mniej (<)lub bardziej (>) ważne. Znaki cudzysłowu pozwalająwyszukiwać całe frazy, a nawiasy — tworzyćpodwyrażenia.

Poniższe zapytanie wyszuka rekordy zawierającefrazę serwer WWW oraz słowo html. Obecnośćsłowa JavaScript obniży wartość rekordu jakowyniku wyszukiwania.

SELECT * FROM tabela WHEREMATCH (kolumny) AGAINST('>"serwer WWW" +html~JavaScript' IN BOOLEAN MODE)

W trybie Boolean pojawiają się następujące różnicew sposobie działania wyszukiwania FULLTEXT:

Jeśli słowo nie jest poprzedzone żadnymoperatorem, to jest opcjonalne, ale podnosiwartość rekordu, który je zawiera.

Wyniki są zwracane nawet wtedy, gdy ponadpołowa rekordów spełnia warunekwyszukiwania.

Wyniki nie są automatycznie sortowanena podstawie stopnia, w jakim spełniają warunekwyszukiwania.

Z uwagi na tę ostatnią właściwość możnasamodzielnie posortować rekordy wedługstopnia w jakim spełniają warunek wyszukiwania,co zaprezentuję w kilku następnych krokach.W przypadku wyszukiwań w trybie Boolean nadalobowiązuje zasada, że słowa krótsze niż 4-znakowesą ignorowane. Nie zmienia tego użycie operatora+, na przykład słowo +php zostanie zignorowane.

Wyszukiw

anie FULLTEXT

Tabela 6.10. Operatory umożliwiająceprecyzyjniejsze wyszukiwanie FULLTEXT

Operatory dostępne w trybie Boolean

Operator Znaczenie

+ Rekord musi zawierać słowo poprzedzonetym operatorem.

- Rekord nie może zawierać słowapoprzedzonego tym operatorem.

~ Słowo poprzedzone tym operatorem obniżawartość rekordu jako wyniku wyszukiwania.

* Maska wyszukiwania.

< Zmniejsza znaczenie słowa.

> Zwiększa znaczenie słowa.

"" Rekord musi zawierać frazę umieszczonąw znakach cudzysłowu.

() Tworzy podwyrażenie.

Rozdział 6.

210

Aby wykonać wyszukiwanie FULLTEXTw trybie Boolean:

1. Wykonaj proste wyszukiwanie FULLTEXT różnychprzypadków słowa baza (bazy, bazie itp.)(patrz rysunek 6.38).

SELECT subject, body FROM messages WHERE MATCH(body, subject) AGAINST('baz*' IN BOOLEAN MODE) \G

Słowo baza może pojawić się w wiadomościachforum w wielu różnych przypadkach takichjak bazy lub bazie. Powyższe zapytanie wyszukarekordy zawierające te przypadki dziękizastosowaniu znaku maski (*).

Dodatkowo zastosowałem znacznik \G,aby łatwiej przeanalizować wyniki.

2. Wykonaj zapytanie wyszukującewiadomości dotyczące tabelze szczególnym uwzględnieniemzawierających termin normalizacja(patrz rysunek 6.39).

SELECT subject, body FROM messages WHERE MATCH(body, subject) AGAINST ('>formularz* +skrypt*' IN BOOLEAN MODE)\G

Zapytanie to najpierw wyszukujewszystkie rekordy zawierające słowoskrypt* (skryptu, skrypty, …) i formularz*(formularz, formularza, formularzy, …).Obecność terminu skrypt* jest wymagana(na co wskazuje operator +), natomiastwyróżnione zostają rekordy zawierającetermin formularz* (na co wskazujeoperator >).

Rysunek 6.38. Proste wyszukiwanie FULLTEXT w trybie BOOLEAN

Rysunek 6.39. To wyszukiwanie dotyczy wariantów dwóch różnych słów, z których jedno ma wyższy priorytet

Wys

zuki

wan

ie F

ULL

TEXT


211

Optymalizacja bazy danychEfektywność działania bazy danych zależygłównie od jej struktury i indeksów. Tworzącbazę, powinieneś spróbować:

Wybrać najlepszy silnik przechowywania.

Używać jak najmniej pojemnego typudanych dla każdej kolumny.

Definiować kolumny jako NOT NULL tam,gdzie to możliwe.

Używać wartości całkowitych jako kluczygłównych.

Uważnie definiować indeksy, wybierającodpowiedni typ oraz stosując jedla właściwych kolumn.

Ograniczać indeksy do pewnej liczbyznaków, jeśli to możliwe.

Oprócz tych wskazówek warto zastosowaćdwie proste techniki optymalizacji bazdanych. Jednym ze sposobów poprawyefektywności działania MySQL-a jestwykonanie polecenia OPTIMIZE dla tabel.Polecenie to usuwa wolne przestrzeniei przywraca wysoką efektywność działaniatabel. OPTIMIZE TABLE tabela;

Wykonanie tego polecenia jest szczególniezalecane po modyfikacji tabeli za pomocąpolecenia ALTER.

Aby zwiększyć wydajność wykonywanegozapytania, warto zrozumieć, w jaki sposóbserwer MySQL-a je przetwarza. W tym celunależy posłużyć się słowem kluczowymjęzyka SQL o nazwie EXPLAIN. Informacjena temat działania zapytań znajdzieszw podręczniku MySQL-a.

Wskazówki

W MySQL 5.1.7 wprowadzono jeszcze jedentryb wyszukiwań FULLTEXT: tryb językanaturalnego. Jeśli nie wybierzesz innego trybu(np. Boolean), to jest on trybem domyślnym.

Modyfikator WITH QUERY EXPANSION pozwalazwiększyć liczbę zwracanych wyników.Zapytania z tym modyfikatorem wykonująw rzeczywistości dwa wyszukiwania, ale zwracająjeden zbiór wyników. Drugie wyszukiwaniebazuje na terminach wyszukanych dodatkowow najlepszych wynikach pierwszegowyszukiwania. Pozwala to znaleźć dodatkowerekordy, ale nie zawsze odpowiadają onepoczątkowym kryteriom wyszukiwania.

Wyszukiw

anie FULLTEXT

Rozdział 6.

212

Wykonywanie transakcjiTransakcja bazodanowa to seria zapytańwykonywanych podczas jednej sesji. Przypuśćmy,że wstawiamy rekord do jednej tabeli, inny rekorddo drugiej tabeli, a potem wykonujemy aktualizację.Bez transakcji każda operacja jest realizowananatychmiast i nie można jej cofnąć. Jeśli użyjemytransakcji, będziemy mogli ustawić punktpoczątkowy i końcowy, a następnie zatwierdzićlub wycofać wszystkie zapytania (jeśli, na przykład,zawiedzie jedno zapytanie, będzie można wycofaćwszystkie).

Transakcje są często potrzebne w interakcjachfinansowych, nawet tak podstawowych jak przelew100 złotych z jednego konta na drugie. Proces tenwydaje się prosty, ale w rzeczywistości składa sięz kilku etapów:

Potwierdzenia, że na pierwszym koncie znajdujesię przynajmniej 100 złotych.

Zmniejszenia stanu pierwszego kontao 100 złotych.

Zwiększenia stanu drugiego konta o 100 złotych.

Sprawdzenia, czy udało się zwiększyć standrugiego konta.

Jeśli którakolwiek z tych operacji się nie powiedzie,należy cofnąć je wszystkie. Jeśli na przykład nie udasię umieścić pieniędzy na drugim koncie, powinnyone wrócić na pierwsze. Odbywa się to do momentu,w którym uda się przeprowadzić całą transakcję.

Aby korzystać z transakcji w MySQL-u, trzebaposługiwać się tabelami InnoDB (lub silnikiemtego typu). W celu rozpoczęcia nowej transakcjiw kliencie mysql należy wpisać:

START TRANSACTION;

Na zakończenie transakcji należy wydać instrukcjęCOMMIT, aby zatwierdzić wszystkie zapytania,albo ROLLBACK, aby je wycofać.

Po zatwierdzeniu lub wycofaniu zapytańtransakcja jest uznawana za zakończonąi MySQL wraca do trybu automatycznegozatwierdzania (ang. autocommit). Oznacza to,że wszystkie zapytania są natychmiastrealizowane. Aby rozpocząć następnątransakcję, wystarczy ponownie wpisać STARTTRANSACTION.

Trzeba wiedzieć, że niektórych typów transakcjinie da się wycofać; dotyczy to w szczególnościzapytań, które tworzą, modyfikują, opróżniająlub usuwają tabele albo tworzą lub usuwająbazy danych. Co więcej, wykonanie takiegozapytania skutkuje zatwierdzeniemi zakończeniem bieżącej transakcji.

Powinieneś też pamiętać, że transakcje sąspecyficzne dla każdego połączenia. Zatemjeden użytkownik klienta mysql dokonujeinnej transakcji niż użytkownik drugiegoklienta, a obie te transakcje są niezależneod realizowanych przez skrypt PHP.

To powiedziawszy, zaprezentuję bardzo prostyprzykład użycia transakcji w kliencie mysql.W rozdziale 17. pokażę, jak realizowaćtransakcje z wykorzystaniem skryptów PHP.

Wyk

onyw

anie

tra

nsak

cji


213

Aby wykonać transakcję,należy wykonać poniższe czynności:

1. Uruchomić klienta mysql i wybrać bazędanych test.

Ponieważ jest to tylko przykład,wykorzystam hipotetyczną bazę danych test.

2. Utworzyć nową tabelę accounts(patrz rysunek 6.40).

CREATE TABLE accounts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL DEFAULT 0.0, PRIMARY KEY (id)) ENGINE=InnoDB;

Oczywiście trudno uznać to za kompletnyprojekt tabeli czy bazy danych. Zasadynormalizacji nakazywałyby rozdzielić imięi nazwisko użytkownika na dwie kolumny,a nawet umieścić je w innej tabeli. Jednakżetabela ta w zupełności wystarczy do naszychcelów.

Najważniejszym aspektem definicji tabeli jestokreślenie mechanizmu InnoDB, który obsługujetransakcje.

3. Wypełnić tabelę danymi.

INSERT INTO accounts (name, balance)VALUES ('Beata Piechota', 5460.30),('Dawid Wójcik', 909325.24),('Ilona Machnik', 892.00);

Można użyć dowolnie wybranych nazwiski wartości. Należy zauważyć, że MySQLautomatycznie zatwierdzi to zapytanie,ponieważ nie została jeszcze rozpoczętażadna transakcja.

Rysunek 6.40. Nowa tabela utworzona w bazie danych test posłużydo zademonstrowania transakcji

Wykonyw

anie transakcji

Rozdział 6.

214

4. Rozpocząć transakcję i pobrać bieżącą zawartośćtabeli (patrz rysunek 6.41).

START TRANSACTION;SELECT * FROM accounts;

5. Podjąć 100 złotych z konta Dawida Wójcika(albo dowolnego innego użytkownika).

UPDATE accountsSET balance=(balance-100)WHERE id=2;

Instrukcja UPDATE, odrobina matematykii klauzula WHERE pozwalają zmniejszyć stankonta o 100 złotych. Choć MySQL informuje,że zmodyfikowany został jeden wiersz, efektnie będzie trwały, dopóki transakcja nie zostaniezatwierdzona.

6. Wpłacić 100 złotych na konto Beaty Piechoty:

UPDATE accountsSET balance=(balance+100)WHERE id=1;

Jest to przeciwieństwo operacji wykonanejw etapie 5., co odzwierciedla przelewanie100 złotych z jednego konta na drugie.

7. Sprawdzić wyniki (patrz rysunek 6.42).

SELECT * FROM accounts;

Jak widać na rysunku, na jednym konciejest teraz o 100 złotych więcej, a na drugimo 100 złotych mniej niż na początku(patrz rysunek 6.41).

Rysunek 6.41. Rozpoczęto transakcję i wyświetlonowszystkie rekordy znajdujące się w tabeli

Rysunek 6.42. Wykonano dwa zapytaniai wyświetlono wyniki

Wyk

onyw

anie

tra

nsak

cji


215

Rysunek 6.43. Ponieważ użyto instrukcjiROLLBACK, wykonane wcześniej zapytaniazostały wycofane

Rysunek 6.44. Instrukcja COMMITutrwala rezultaty transakcji

8. Wycofać transakcję.

ROLLBACK;

Aby zademonstrować wycofywanie transakcji,unieważniam wykonane wcześniej zapytania.Instrukcja ROLLBACK przywraca bazę danychdo stanu sprzed rozpoczęcia transakcji. Instrukcjata kończy jednocześnie transakcję, przełączającMySQL-a z powrotem do trybu automatycznegozatwierdzania.

9. Sprawdzić wyniki (patrz rysunek 6.43).

SELECT * FROM accounts;

Zapytanie powinno zwrócić zawartość tabelisprzed rozpoczęcia transakcji.

10. Powtórzyć etapy 4. – 6.

Aby sprawdzić, co się stanie w raziezatwierdzenia transakcji, należy ponowniewykonać dwa zapytania UPDATE. Należy pamiętaćo rozpoczęciu nowej transakcji, ponieważw przeciwnym razie zapytania zostanąautomatycznie zatwierdzone!

11. Zatwierdzić transakcję i sprawdzić wyniki(patrz rysunek 6.44).

COMMIT;SELECT * FROM accounts;

Po wydaniu instrukcji COMMIT transakcja zostaniezatwierdzona, co oznacza, że wszystkie zmianystaną się trwałe. Instrukcja ta jednocześniekończy transakcję, przełączając MySQL-az powrotem do trybu automatycznegozatwierdzania.

Wykonyw

anie transakcji

Rozdział 6.

216

Wskazówki

Jedną z największych zalet transakcji jest to,że zapewniają one ochronę przed zdarzeniamilosowymi, takimi jak awaria serwera. Transakcjajest albo w całości zatwierdzana,albo ignorowana.

Aby wyłączyć tryb automatycznegozatwierdzania w serwerze MySQL-a,należy wpisać:

SET AUTOCOMMIT=0;

Nie trzeba będzie wówczas wydawać instrukcjiSTART TRANSACTION, a wszystkie wykonanezapytania zostaną zrealizowane dopieropo wpisaniu instrukcji COMMIT (albo użyciuzapytania CREATE, ALTER itp.).

W transakcjach można tworzyć punkty zapisu:SAVEPOINT nazwa_punktu_zapisu;

Funkcja ta umożliwia wycofanie transakcjido określonego punktu zapisu:

ROLLBACK TO SAVEPOINT nazwa_punktu_zapisu;

Wyk

onyw

anie

tra

nsak

cji

PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start

Technology

Transcript of PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start