Hej på er! Nu ska jag förklara lite mer om databasen MySQL och en av databasmotorerna InnoDB. Jag upptäckte igår vad detta innebär och då började jag komma ihåg att jag hade läst om detta förut i en bra bok om just MySQL databashantering. Boken heter "MySQL handboken : [den snabba vägen till grunderna i MySQL]" och passar utmärkt till den som vill lära sig MySQL och gå in lite mer på djupet.
När jag läste boken förstod jag inte så mycket om foreign keys, som på svenska heter "främmande nycklar", men det förstår jag bättre nu. Därför har jag tänkt att visa ett exempel på när det kan vara mycket användbart.
Mycket bra läsning på engelska finns i en artikel publicerad på InnoDB.com [2]
Introduktion
Vad är MySQL?
Okej, som de flesta vet så är MySQL en typ av databas där man kan lagra information på ett strukturerat sätt. Och för er som inte vet, så vet ni nu vad MySQL är för något.,
SQL = Structured Query Language
Men vad kan MySQL användas till?
MySQL använder s.k. SQL-frågor för att skicka och hämta data från en databas. MySQL kan användas till att lagra information strukturerat om t.ex kunder som har bokat klipptider hos en frisör, eller produkter och artiklar som man vill sälja via en hemsida. När någon sedan har lagt en beställning kan man koppla produkterna till en specifik beställning som i sin tur hör till en registrerad kund.
Ett vanligt exempel från gymnasiet är en databas med elever som kommer att börja på skolan nästkommande termin. Vi fick lära oss hur man designar en databas med tabeller som är relaterade till varandra och hur man endast kan tillåta unika personer i databasen baserat på unika personnummer.
Fint, men vad är InnoDB för något?
I MySQL används olika databasmotorer (engines) för att spara och hämta data på olika sätt. Ska datans integritet kontrolleras eller spelar det ingen roll? Ska tabeller relateras med varandra eller skötas med programmering? Det är uppgiften som en databasmotor har.
InnoDB är inte så vanlig bland nybörjare eftersom standardmotorn heter MyISAM och många vet inte eller bryr sig inte om att kontrollera vilken databasmotor man använder. Databasen funkar ju fortfarande på samma sätt, eller? Ja det är sant men InnoDB kan kontrollera data i databasens tabeller så att länkarna, relationerna och integriteten inte bryts om en ändring sker i databasen.
Huvudregeln säger att i en relationsdatabas (en databas med relationer vilket hörs på namnet) så måste relaterade nycklar ha en korrekt länkning - nyckel1 med ID 4 i tabell A måste vara samma som nyckel1 med ID 4 i tabell B. Med InnoDB kan man kontrollera att ID-numret måste vara samma i båda tabellerna som är relaterade med varandra genom användning av främmande nycklar.
Om du inte förstår så är det helt förståeligt. Det kan vara ett svårt koncept i början innan man lär sig. Jag kommer strax gå igenom skillnaderna mellan InnoDB och MyISAM (den vanligaste databasmotorn).
Först en kort beskrivning om primärnycklar och främmande nycklar
En primärnyckel måste vara unik i en tabell. Dubbletter och kopior får inte förekomma. Ett vanligt exempel på en primärnyckel är ett ID i en tabell som automatiskt räknar uppåt när nya rader med data läggs till. Om man ska ha ett personnummer i en databas så passar personnumret utmärkt som en primärnyckel eftersom det ska vara unikt. Detta är bra så att man inte råkar fylla i ett som redan finns.
När ska man använda InnoDB och MyISAM?
Kortfattat
Om du har en t.ex en hemsida med en databas som innehåller en tabell som kommer att uppdateras och ändras kontinuerligt och väldigt ofta, då passar InnoDB. Speciellt om du har flera tabeller som är relaterade till varandra. Då kan InnoDB säga till när det blir fel och hjälpa till med uppdatering i andra tabeller. InnoDB är långsammare när det gäller SELECT och ändringar.
MyISAM ska man helst använda om det sällan sker INSERT, UPDATE och DELETE i tabellerna. Alltså om det sker mycket SELECT. MyISAM är snabbast men är också enklast att arbeta med.
Det bästa scenariot är...
Om man kan köra två databaser som master och slave med replikering. Då kan en av dem innehålla tabeller med InnoDB som håller koll på all integritet, och den andra har kopior av alla tabeller från den första fast med MyISAM istället för InnoDB. När data ska hämtas så anropas den andra med MyISAM. När data ska läggas till, uppdateras eller tas bort så anropas den första. Då får man det bästa av två världar - nja två olika tekniker i alla fall.
Nu ett exempel med InnoDB
Exempel 1
Beskrivning: Vi kommer ha en tabell där vi lagrar information om kategorier för produkter som säljs på en hemsida. Observera att detta exempel inte kommer fungera med MyISAM.
Skapa först en databas. Skapa sedan nedanstående tabeller.
CREATE TABLE tbl_products(
product_id INT UNSIGNED NOT NULL,
product_name VARCHAR(20),
PRIMARY KEY(product_id)
) ENGINE=InnoDB;CREATE TABLE tbl_categories(
category_id INT UNSIGNED NOT NULL,
category_name VARCHAR(20),
PRIMARY KEY(category_id)
) ENGINE=InnoDB;CREATE TABLE tbl_categories_products(
category_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
PRIMARY KEY(product_id, category_id),
FOREIGN KEY (category_id) REFERENCES tbl_categories(category_id),
FOREIGN KEY (product_id) REFERENCES tbl_products(product_id)
) ENGINE=InnoDB;Nu kan vi se på bilden här nedanför hur relationen mellan tabellerna ser ut.
(Bild 1) Relation mellan tabeller produkter och kategorier
Lägga till lite data till tabellerna. Här lägger vi till några produkter:
INSERT INTO tbl_products(product_id, product_name)
VALUES
(1, 'MP3 spelare'),
(2, 'iPod'),
(3, 'iPad'),
(4, 'Dell Dimension 1111 PC'),
(5, 'PHP och MySQL Handboken'),
(6, 'Bäverdalen'),
(7, 'Samsung Galaxy Tab')Lägg till några kategorier för produkterna:
INSERT INTO tbl_categories(category_id, category_name)
VALUES
(1, 'Musikspelare'),
(2, 'Läsplattor'),
(3, 'PC'),
(4, 'Böcker')Skapa relationer mellan kategorier och produkter:
INSERT INTO tbl_categories_products(category_id, product_id)
VALUES
(1, 1),
(1, 2),
(2, 3),
(3, 4),
(4, 5),
(4, 6),
(2, 7)Försök att lista ut själv hur relationen är uppbyggd. Första kolumnen är vilken kategori som den andra kolumnen tillhör. T.ex "PHP och MySQL Handboken" tillhör kategori nr 4 vilket är "Böcker".
Resultat
Nu måste utvecklaren och alla mjukvaror som tänker använda tabellerna tänka på att kontrollera integriteten själva genom att uppdatera alla tabeller som ändras för varje SQL-fråga till databasen. ID numren måste stämma överens annars får man ett felmeddelande av databasen. Så om jag nu skulle råka fylla i data (en post) i tabellen tbl_categories_products med följande SQL-fråga, så skulle jag få ett felmeddelande eftersom kategori ID-numret inte finns i tabellen tbl_categories.
INSERT INTO tbl_categories_products(category_id, product_id) VALUES (8, 2)Exempel för MyISAM
Tyvärr har jag inte några exempel för tabeller som har MyISAM men jag vet att det fungerar genom att skapa sk Triggers. Läs gärna referensen [1] för att läsa mer.
Vad händer om man vill ändra ett ID-nummer i tabellen för kategorier?
Då blir det fel. Man måste ändra i båda tabellerna samtidigt har jag för mig. Men det problemet kan vi lösa i kommande text och exempel.
Cascade för "delete" och "update"
Med nyckelordet "Cascade" kan man tillåta att InnoDB själv ska ha kontroll över alla tabellerna automatiskt. Säg nu att jag vill byta ut ID-numret för kategorin "Läsplattor" till ID 17, då kan jag uppdatera numret för category_id i tabellen tbl_categories och ändringen sker även automatiskt i tabellen tbl_categories_products eftersom den är relaterad till category_id och måste vara likadan. Ändras en tabell så måste den andra tabellen också ändras.
Detsamma gäller när en rad tas bort. Om jag känner för att ta bort hela kategorin "Böcker" (ID nr 4) så kommer raden/raderna i tabellen tbl_categories_products med category_id = 4 att också tas bort.
Hur gör man då detta... man kan använda Cascade när man skapar en främmande nyckel. Nu kommer jag visa med ett exempel hur man gör. Man kan använda CREATE TABLE och ALTER TABLE för att ändra i efterhand. Jag kommer visa hur man gör via CREATE TABLE.
CREATE TABLE tbl_categories_products(
category_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
PRIMARY KEY(product_id, category_id),
FOREIGN KEY (category_id) REFERENCES tbl_categories(category_id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES tbl_products(product_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;Detta betyder nu att tabellen övervakar de främmande nycklarna i de främmande tabellerna tbl_categories och tbl_products.
Om du nu testar att köra följande SQL-fråga så kan du se att två tabeller uppdateras. Den ena som du valde och den andra som har en relation med den du valde.
UPDATE tbl_categories SET category_id = 14 WHERE category_id = 2Kontrollera tabellerna
SELECT * FROM tbl_categories
SELECT * FROM tbl_products
SELECT * FROM tbl_categories_products (har ändrat sitt category_id som var 2 automatiskt till 14)
Det borde se ut ungefär så här nu i tabellen tbl_categories:
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
| 1 | Musikspelare |
+-------------+---------------+
| 14 | Läsplattor |
+-------------+---------------+
| 3 | PC |
+-------------+---------------+
| 4 | Böcker |
+-------------+---------------+
tbl_categories_products:
+-------------+------------+
| category_id | product_id |
+-------------+------------+
| 1 | 1 |
+-------------+------------+
| 1 | 2 |
+-------------+------------+
| 14 | 3 |
+-------------+------------+
| 3 | 4 |
+-------------+------------+
| 4 | 5 |
+-------------+------------+
| 4 | 6 |
+-------------+------------+
| 14 | 7 |
+-------------+------------+Slutsats
Slutsatsen med detta testet är att jag nu kommer överväga att använda InnoDB för mer kritiska applikationer där det sker många ändringar i databasen. Om jag kör detta system på en hemsida kommer jag att köra cachelagring av all data så behöver data bara hämtas en gång med SELECT från databasen och resten sparas i textfiler.
Det kommer också bli svårare att designa sin databas så jag får planera bättre i framtiden exakt vilka tabeller jag vill använda och hur man eventuellt kan uppdatera databasen i framtiden för att minska problem och fel så mycket som möjligt.
Nu behöver jag bara uppdatera en tabell och behöver inte tänka på vilka andra tabeller som är indirekt relaterade till tabellen genom "osynliga" relationer. Med detta menar jag att jag alltid innan har skrivit applikationer som själva måste hålla reda på att ändra i t.ex tabellen tbl_categories_products så fort jag uppdaterar ett kategori ID eller tar bort en kategori.
Ett bra scenario är kommentarer som är kopplade till ett blogginlägg. Tar man bort blogginlägget så ska alla tillhörande kommentarer tas bort automatiskt utan att jag som programmerare behöver tänkta på att skriva kod som måste utföra det manuellt. Nu sköts allt i databasen och koden blir mer abstract och enklare att underhålla.
Slutord
Det var allt för den här gången. Jag hoppas att artikeln har varit till någon nytta för er.
Mvh
Waschman
Referenser
[1] http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html
[2] http://blogs.innodb.com/wp/2010/09/mysql-5-5-innodb-as-default-storage-engine/
