MySQL en InnoDB

Transacties, referentiële integriteit, normaliseren, relaties, primary keys, foreign keys…allemaal zaken die bij een database-ontwerp om de hoek komen kijken. Een veel gebruikte term bij database-ontwerpen is ACID:

Atomicity : Transacties worden óf helemaal afgerond, of falen volledig.
Consistency : Integriteit van data blijft gewaarborgd.
Isolation : Een transactie blijft onzichtbaar voor andere processen totdat deze is afgerond
Durability : Het systeem blijft consistent na bijvoorbeeld een crash tijdens een transactie.

In deze tutorial hoop ik je wegwijs te maken hoe MySQL de meeste van deze zaken heeft geïmplementeerd door de InnoDB tabel-engine te gebruiken. (De meeste, behalve Durability, InnoDB werkt met een transaction-log welke in geval van een crash kan worden gebruikt om te recoveren, maar daar heb ik slechte ervaringen mee).

MySQL-tabellen maken standaard gebruik van de MyISAM-engine, zo’n engine bepaalt welke functionaliteit je allemaal kunt gebruiken. In het voorbeeld spreek ik over de InnoDB-engine, het lijkt me dus verstandig om de belangrijkste verschillen tussen die 2 te bekijken:

MyISAM:
- Fulltext index
- Geen transactions
- Geen PK / FK-relaties

InnoDB:
- Géén fulltext index
- Wél transactions
- Wél PK / FK-relaties

M.b.v. transactions kun je 1 of meerdere queries uitvoeren, zonder dat de wijzigingen zichtbaar zijn voor andere processen. Mocht er tijdens dit proces een query mislukken zal de transactie falen en worden er geen wijzigingen aangebracht. Als er niet aan een bepaalde voorwaarde wordt voldaan kun je een zgn. ROLLBACK uitvoeren waarmee alle uitgevoerde queries ongedaan worden gemaakt. Als alle queries goed zijn uitgevoerd kun je een COMMIT uitvoeren waarmee alle queries definitief zijn gemaakt.

Het klassieke voorbeeld is die van de bank:

Stel dat ik 100EUR wil overmaken van mijn rekening naar die van jou; je zou dan het volgende kunnen uitvoeren in SQL:

UPDATE rekening SET saldo=saldo-100 WHERE naam=’Remco’
UPDATE rekening SET saldo=saldo+100 WHERE naam=’Jouw naam’

Maar.....stel nu dat er tussen die 2 queries iets misgaat, waardoor de 2e query niet wordt uitgevoerd, ik ben dan 100EUR lichter, maar jij hebt je geld niet. Het geld is dus verdwenen.

Met transactions zou het zo gebeuren:

## Start de transactie
START TRANSACTION;
UPDATE rekening SET saldo=saldo-100 WHERE naam=’Remco’;
UPDATE rekening SET saldo=saldo+100 WHERE naam=’Jouw naam’;

## Als we hier komen zijn beide queries uitgevoerd
## Maak de wijzigingen permanent
COMMIT;

De query “START TRANSACTION” geeft aan dat er een transactie begint. Vervolgens worden er 2 queries uitgevoerd en volgt uiteindelijk de “COMMIT”, als er tussendoor iets fout gaat zal de COMMIT niet worden uitgevoerd, de queries zullen dus niet worden verwerkt. Uiteraard is dit geheel afhankelijk van hoe je script in elkaar steekt.

Je zou het ook nog uit kunnen breiden door te controleren of mijn saldo wel voldoende is, in “echte” databases kun je dit netjes in een database-functie oplossen, met MySQL moet je wat client-side werk verrichten. Afhankelijk van het saldo kun je een ROLLBACK uitvoeren om de boel te annuleren.

PK staat voor primary key, FK voor foreign key. Een PK is de sleutel tot 1 record in een tabel, ieder record in je tabel(len) hoort een eigen PK te hebben, in MySQL realiseer je dit met een INTEGER welke je AUTO_INCREMENT maakt, zo dwingt MySQL min of meer af dat je altijd een unieke waarde hebt, het vreemde is dan weer als je een tabel leegt dat de PK ook weer opnieuw begint. Iets wat eigenlijk niet hoort.

Een foreign key is een “vreemde sleutel” welke verwijst naar een PK van zichzelf of een andere tabel. Een voorbeeld zegt 1000 woorden:

Tabel met gebruikers:
CREATE TABLE gebruikers (
gebruiker_id INT( 10 ) NOT NULL AUTO_INCREMENT,
naam VARCHAR( 75 ) NOT NULL ,
PRIMARY KEY ( gebruiker_id )
) TYPE = InnoDB;

Tabel met berichten:
CREATE TABLE berichten (
bericht_id int(10) NOT NULL auto_increment,
van_gebruiker_id int(10) default NULL,
naar_gebruiker_id int(10) default NULL,
bericht text NOT NULL,
PRIMARY KEY (bericht_id),
KEY van_gebruiker_id (van_gebruiker_id,naar_gebruiker_id)
) ENGINE=InnoDB

Let erop dat de datatypes van gebruiker_id, naar_gebruiker_id en van_gebruiker_id allen van hetzelfde datatype moeten zijn, bijvoorbeeld INT(10). Daarnaast is een INDEX op de FK’s verplicht. Let er tevens op dat deze FK-velden als standaardwaarde ‘NULL’ hebben en NULL is totaal iets anders dan 0. NULL is echt leeg, en 0 is gewoon de waarde 0, als deze in een FK staat zal het een fout genereren aangezien een 0 als PK meestal niet voorkomt.

Beide tabellen dienen van het type InnoDB te zijn, zie ook de Tips & Tricks.

Op de voorgaande pagina stonden wat SQL-dumps voor nieuwe tabellen. Die tabellen gaan we nu koppelen.

Wat kun je nou met die relaties? Nou, je dwingt op database-niveau af dat er in de tabel met berichten alleen records kunnen zitten van gebruikers welke voorkomen in de tabel met gebruikers!

Hoe je dit doet is een stukje SQL-code, waarbij je verschillende mogelijkheden hebt. Het gaat hier om de tabel met berichten, daarin liggen de FK’s naar de tabel gebruikers:

## Foreign key aanmaken welke van_gebruiker_id koppelt aan de tabel met gebruikers
ALTER TABLE berichten ADD FOREIGN KEY(van_gebruiker_id) REFERENCES gebruikers(gebruiker_id) ON DELETE CASCADE ON UPDATE CASCADE

## Foreign key aanmaken welke naar_gebruiker_id koppelt aan de tabel met gebruikers
ALTER TABLE berichten ADD FOREIGN KEY(naar_gebruiker_id) REFERENCES gebruikers(gebruiker_id) ON DELETE CASCADE ON UPDATE CASCADE

Het laatste stukje in de query vertelt al wat er gebeurt. Als er een record uit de tabel gebruikers wordt verwijderd zullen bijbehorende berichten ook verwijderd worden, daar zorgt het stukje ON DELETE CASCADE voor.

Als het gebruiker_id in de tabel gebruikers wordt aangepast zullen automatisch alle berichten worden aangepast naar de nieuwe gebruiker_id, daar zorgt het stukje ON UPDATE CASCADE voor. CASCADE staat voor “achterelkaar” of “trapsgewijs”, er worden dus meerdere acties achter elkaar uitgevoerd.

Je kunt dit natuurlijk ook andersom doen, waarmee je je data beveiligt, zodat je geen user kan verwijderen zolang er nog berichten zijn welke voor of van de gebruiker zijn. Het stukje ON DELETE CASCADE vervang je dan door ON DELETE RESTRICT. Naast deze mogelijkheden bestaan er nog SET NULL en NO ACTION, deze twee zijn vanzelfsprekend.

<?php
// Probeer verbinding te maken
if( !$rConn = mysql_connect('host', 'user', 'pass') )
{
// Database-verbinding faalde
die('Geen databaseverbinding!');
}
else
{
// Succesvolle verbinding, selecteer de database
if( mysql_select_db('db', $rConn) )
{
// Voer een record in de tabel gebruikers
$qryInsert = "INSERT INTO gebruikers(naam) VALUES ('remco')";
if( !mysql_query($qryInsert, $rConn) )
{
// Query mislukte, toon error
echo mysql_error();
}
else
{
// Bepaal het gegenereerde gebruiker_id
$gebruikerID = mysql_insert_id($rConn);

// Voer een bericht in naar een NIET bestaande gebruiker met ID 145
$qryFout = "INSERT INTO berichten(van_gebruiker_id,naar_gebruiker_id,bericht)
VALUES(".$gebruikerID.", 145, 'Lekker fout')";

if( !mysql_query($qryFout, $rConn) )
{
// Query mislukte, toon error
echo mysql_error();
}

// Voer een bericht in naar zichzelf
$qryGoed = "INSERT INTO berichten(van_gebruiker_id,naar_gebruiker_id,bericht)
VALUES(".$gebruikerID.", ".$gebruikerID.", 'Lekker goed')";

if( !mysql_query($qryGoed, $rConn) )
{
// Query mislukte, toon error
echo mysql_error();
}

// Probeer nu de user te verwijderen
$qryDeleteUser = "DELETE FROM gebruikers WHERE gebruiker_id=".$gebruikerID;
if( !mysql_query($qryDeleteUser, $rConn) )
{
// Query mislukte, toon error
echo mysql_error();
}

/*
Afhankelijk van hoe je foreign key constraint eruit ziet is de user met
zijn berichten verwijderd, of heb je een melding gekregen dat er niet kan
worden verwijderd omdat anders de foreign key constraint wordt
overschreden.
*/
}
}
}
?>

Bij het werken met InnoDB zijn er wel een paar zaken waar je rekening mee moet houden, ik probeer zoveel mogelijk knelpunten te behandelen, zoals deze voor mij bekend zijn.

- Zorg dat je tabellen tijdens het aanmaken al van het type InnoDB zijn. Converteren van MyISAM naar InnoDB wordt ten zeerste afgeraden door MySQL!!!

- Tabellen met onderlinge relaties dienen beiden van het type InnoDB te zijn, van MyISAM linken naar InnoDB gaat dus niet!

- Velden welke als FK fungeren dienen van hetzelfde datatype te zijn als de PK waar ze naar refereren.

- Velden welke als FK fungeren dienen te worden geïndexeerd.

- Velden welke als FK fungeren dienen als standaardwaarde NULL te hebben en geen 0

MySQL is een leuke database om mee te beginnen en kan redelijk wat, maar naarmate je meer van een database verlangt zal je merken dat het niet voldoet aan veel eisen. Een rasechte freak heeft er een mooi stukje over geschreven, dat nog regelmatig wordt aangevult en is echt de moeite waard om eens kritisch te bekijken:

http://www.yapf.net/faq.php?cmd=100&itemid=832

Alternatieven? PostgreSQL, ook opensource en ook op Windows beschikbaar :)
Daar heb je gewoon 1 tablehandler die alle genoemde zaken ondersteund + nog veel meer, zoals functies, triggers, views etc.

Veel database-plezier!