Deze website maakt gebruik van diensten van Google voor het tonen van advertenties en het bijhouden van bezoekersstatistieken. Google kan hiermee uw surfgedrag volgen. Zie voor meer informatie het privacybeleid van Google. Via Your Online Choices kunt u zich afmelden voor gepersonaliseerde advertenties. Deze melding verbergen.

20 MySQL Introductie

In geval van een statische website volstaat het om gegevens in losse bestanden op te slaan. Hoe dynamischer de website wordt, hoe meer de noodzaak zal ontstaan om gegevens op een samenhangende manier op te slaan. De losse bestanden volstaan niet meer, er is een database nodig! Content management systemen, webwinkels, forums, ze gebruiken allemaal een database om allerlei gegevens op te slaan. Van gebruikersaccounts, inhoud van pagina's tot soms afbeeldingen aan toe. MySQL (spreek uit: mai es-kjoe-el) is het meest gebruikte open-source databasesysteem en werkt naadloos samen met PHP. Dit hoofdstuk gaat in op MySQL en databases in het algemeen. In de volgende twee hoofdstukken zien we hoe we via PHP met MySQL kunnen werken.

Theorie

Theorie

MySQL, hoe kom ik er aan?

De meeste webhosts hebben MySQL beschikbaar. Het hostingpakket heeft dan één of meerdere MySQL databases inbegrepen. Soms is het nodig om een iets duurder hostingpakket te nemen. Op je eigen computer is MySQL waarschijnlijk al geïnstalleerd als onderdeel van een ontwikkelomgeving als XAMPP, zie ook het hoofdstuk Voorbereiding. In andere gevallen kan MySQL gedownload worden via mysql.com om zelf te installeren. Voor je webserver onder Linux is MySQL meestal ook beschikbaar via je pakketmanager.

Om toegang te krijgen tot een MySQL database is een gebruikersnaam en wachtwoord nodig. Deze worden verstrekt door de webhost of in geval van XAMPP is de gebruikersnaam root en is het wachtwoord leeg gelaten.

MariaDB

MariaDB is een afsplitsing van MySQL die ontstaan is na de overname van MySQL door Oracle, omdat er sterke twijfel was of Oracle MySQL nog langer vrij beschikbaar zou houden. MySQL kan een op een vervangen worden door MariaDB zonder aanpassingen in je scripts. Een aantal webhosts levert tegenwoordig standaard MariaDB databases in plaats van MySQL. Ook wordt XAMPP tegenwoordig geleverd met MariaDB in plaats van MySQL. Op dit moment (2018) maakt het niet echt uit welke van de twee je gebruikt en kun je in plaats van MySQL ook MariaDB lezen. Wanneer je op internet gaat zoeken naar hoe je bepaalde query's maakt, is het echter beter om op MySQL te zoeken, ook als je MariaDB gebruikt, omdat je dan veel meer resultaten krijgt.

Databases en tabellen

MySQL kan met meerdere databases werken. Een database bevat tabellen met gegevens. Een tabel heeft rijen en kolommen. Een rij is een set gegevens die bij elkaar horen. De kolommen bepalen wat voor soort gegevens er in iedere rij opgeslagen kunnen worden. In figuur 20.1 is schematisch een database weergegeven met twee tabellen. Iedere tabel heeft drie kolommen en zes rijen.

opbouw database uit tabellen met rijen en kolommen — Figuur 20.1: Opbouw van een database met tabellen, kolommen en rijen.

Iedere database heeft een unieke naam en binnen een database heeft elke tabel een unieke naam. Een naam bestaat uit maximaal 64 tekens. In principe zijn alle tekens toegestaan die in een bestandsnaam mogen voorkomen, inclusief spaties (zo lang deze maar niet aan het einde van de naam staan. De slash (/), backslash (\) en punt (.) zijn niet toegestaan in de naam. Het kan geen kwaad om dezelfde regels te gebruiken voor de namen van variabelen in PHP, dan zit je altijd goed.

Voorbeeld

MySQL is bij uitstek geschikt om gebruikersaccounts op te slaan voor een beveiligd gedeelte van je website. Daar is één tabel voor nodig met in ieder geval kolommen voor gebruikersnaam, wachtwoord en e-mailadres. Ook maken we een kolom om bij te houden wanneer een gebruiker voor het laatst is ingelogd en maken we een kolom voor id's. Een id is als het ware een volgnummer. Het is noodzakelijk dat er ten minste één kolom is met unieke waarden. Dit wordt de primaire sleutel genoemd. Een id is zeer geschikt als primaire sleutel en maakt het later makkelijker om bewerkingen op de tabel uit te voeren.

Een voorbeeld van een tabel met gebruikersaccounts is weergegeven in tabel 20.1.

Tabel 20.1: Voorbeeldtabel met gebruikersaccounts
id	gebruikersnaam	wachtwoord	email	laatst_ingelogd
1	jantje	5371e...798	jantje@example.com	2015-08-18 21:09:54
2	pietje	978a7...3b2	pietje@example.com	2015-08-18 17:54:11
3	klaasje	b6749...4de	klaasje@example.com	2015-03-12 08:12:36

Merk op dat de wachtwoorden niet als plain text zijn opgeslagen in de database, maar dat gebruik is gemaakt van een hash-functie, zoals is toegelicht in hoofdstuk 11. Voor de overzichtelijkheid van de tabel is niet de hele hash weergegeven, maar in werkelijkheid bestaat ieder gehasht wachtwoord uit 64 hexadecimale tekens.

Opslagmethode

Voor iedere tabel binnen MySQL moet een opslagmethode (storage engine) worden gekozen. De meestgebruikte opslagmethoden zijn MyISAM en InnoDB. Hoewel er meer verschillen zijn, is MyISAM meer geschikt voor tabellen waar hoofdzakelijk uit wordt gelezen en is InnoDB meer geschikt voor tabellen waar veel in wordt geschreven. De standaardopslagmethode van MySQL is enige tijd geleden gewisseld van MyISAM naar InnoDB. Voor de meeste websites is het verschil tussen beide niet merkbaar en kan voor de opslagmethode worden gekozen die standaard is voor jouw MySQL installatie.

Kolommen

Binnen een tabel hebben kolommen een unieke naam. Hiervoor gelden dezelfde regels als voor de namen van databases en tabellen. Voor iedere kolom wordt een datatype ingesteld, en in iedere tabel wordt één kolom aangemerkt als primaire sleutel.

Primaire sleutel

In iedere tabel wordt één kolom aangemerkt als primaire sleutel (primary key). Dit betekent dat alle waarden in deze kolom uniek moeten zijn. Hiermee houdt MySQL verschillende rijen uit elkaar en kunnen specifieke rijen later worden bewerkt of verwijderd.

Het is gebruikelijk om in iedere tabel een id te gebruiken als primaire sleutel. In feite wordt er dan een extra kolom toegevoegd waar voor iedere rij een volgnummer geplaatst wordt. MySQL kan voor iedere nieuwe rij automatisch een nieuw volgnummer toekennen. Een id-kolom maakt het ook makkelijker om vanuit PHP bewerkingen op de tabel uit te voeren, door het juiste id mee te geven in de query-string, bijvoorbeeld edit.php?id=20.

Datatypen

Voor iedere kolom wordt een datatype ingesteld. Het datatype bepaalt welke soort gegevens er in de kolom worden opgeslagen. Er zijn datatypen voor tekst, voor getallen en voor datum en tijd. Het is belangrijk om vooraf bij het maken van de tabel goed na te denken over welke gegevens er in welke kolom kunnen worden opgeslagen. Achteraf kan het datatype indien nodig nog worden aangepast, maar als je het meteen goed kiest hoef je het ook niet in de gaten te houden.

Numerieke datatypen

Tabel 20.2 en tabel 20.3 geven de meest gebruikte numerieke datatypen. Er zijn datatypen voor integers en voor decimale getallen. Het datatype bepaalt de kleinste en grootste waarden die kan worden opgeslagen in een kolom met het gekozen datatype. Voor een numeriek datatype kan worden gekozen of er alleen positieve getallen of ook negatieve getallen kunnen worden opgeslagen. We noemen dit unsigned (zonder teken) en signed (met teken). Kies het kleinst mogelijke datatype om efficiënt met opslagruimte om te gaan. Een kolom met een integer datatype is geschikt als primaire sleutel.

Tabel 20.2: Numerieke datatypen: integer
Datatype	Minimum	Maximum
TINYINT SIGNED	-128	127
TINYINT UNSIGNED	0	255
SMALLINT SIGNED	-32768	32767
SMALLINT UNSIGNED	0	65535
MEDIUMINT SIGNED	-8388608	8388607
MEDIUMINT UNSIGNED	0	16777215
INT SIGNED	-2147483648	2147483647
INT UNSIGNED	0	4294967295
BIGINT SIGNED	-9223372036854775808	9223372036854775807
BIGINT UNSIGNED	0	18446744073709551615

Tabel 20.3: Numerieke datatypen: decimaal
Datatype	Negatief	Positief
FLOAT SIGNED	-3.402823466E+38 tot -1.175494351E-38	1.175494351E-38 tot 3.402823466E+38
FLOAT UNSIGNED		1.175494351E-38 tot 3.402823466E+38
DOUBLE SIGNED	-1.7976931348623157E+308 tot -2.2250738585072014E-308	2.2250738585072014E-308 tot 1.7976931348623157E+308
DOUBLE UNSIGNED		2.2250738585072014E-308 tot 1.7976931348623157E+308

Tekstuele datatypen

Tabel 20.4 geeft een overzicht van tekstuele datatypen. Dit zijn datatypen waarin tekst kan worden opgeslagen. Eigenlijk hoeft alleen maar te worden gekeken hoe groot de langste tekst is die moet kunnen worden opgeslagen om het juiste tekstuele datatype te bepalen. Het VARCHAR datatype is een speciaal soort tekstueel datatype. Er moet worden opgegeven hoeveel tekens er maximaal opgeslagen worden (M) en een VARCHAR kolom kan primaire sleutel. Verder wordt bij een VARCHAR kolom eventuele witruimte (spatie, tab, etc.) aan het begin en einde van de data automatisch verwijderd.

Tabel 20.4: Tekstuele datatypen
Datatype	Maximum tekens
TINYTEXT	255 (2⁸ – 1) bytes
TEXT	65 535 (2¹⁶ – 1) bytes of 64 kiB
MEDIUMTEXT	16 777 215 (2²⁴ – 1) bytes of 16 MiB
LONGTEXT	4 294 967 295 (2³² – 1) bytes of 4 GiB
VARCHAR(M)	255 tekens

Datum en tijd datatypen

Tabel 20.5 geeft een overzicht van datum en tijd datatypen. Hoewel datums en tijden ook in tekstkolommen kunnen worden opgeslagen, bieden de specifieke datum en tijd datatypes onder andere als voordeel dat een uniforme notatie wordt afgedwongen. Het TIMESTAMP datatype is een speciaal soort datum en tijd datatype. De eerste TIMESTAMP kolom van een rij wordt automatisch bijgewerkt bij het invoegen of bewerken van de betreffende rij. Dit datatype is daarom vooral handig om bij te houden wanneer een rij voor het laatst is bijgewerkt. TIMESTAMP kolommen worden opgeslagen in UTC en worden bij het bijwerken en ophalen van gegevens automatisch geconverteerd naar de in MySQL ingestelde tijdzone.

Tabel 20.5: Datum en tijd datatypen
Datatype	Weergave	Bereik
DATE	YYYY-MM-DD	'1000-01-01' tot '9999-12-31'
DATETIME	YYYY-MM-DD HH:MM:SS	'1000-01-01 00:00:00' tot '9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD HH:MM:SS	'1970-01-01 00:00:01' UTC tot '2038-01-09 03:14:07' UTC
TIME	HH:MM:SS	'-838:59:59' tot '838:59:59'

Karaktersets en Collaties

Iedere database heeft een karakterset en een collatie. De karakterset bepaalt hoe teksten (in tekstkolommen) worden gecodeerd en dus welke tekens er kunnen worden opgeslagen. De collatie bepaalt hoe tekens in de karakterset vergeleken moeten worden, bijvoorbeeld bij alfabetisch sorteren. De collatie bepaalt onder andere of letters met accenten moeten worden gezien als aparte letters of als gelijke van de letter zonder accent. Ook bepaalt de collatie of er bij sorteren en zoeken onderscheid moet worden gemaakt tussen hoofdletters en kleine letters. Voor Nederlandse en Engelse teksten volstaat de karakterset latin1 met de collatie latin1_general_ci. De ci staat voor case insensitive, ofwel niet-hoofdlettergevoelig. Zo is er ook het wel-hoofdlettergevoelige latin1_general_cs. Wanneer ook meer exotische tekens moeten kunnen worden opgeslagen, kies dan voor de utf8 karakterset met utf8_general_ci collatie.

De karakterset en collatie van een database worden bij het aanmaken van de database ingesteld. Als dit niet wordt gedaan, dan worden de standaard karakterset en collatie gebruikt. Indien gewenst kan per tabel, of zelfs per kolom, worden afgeweken van de karakterset en collatie die voor de database is ingesteld.

SQL

MySQL heet MySQL omdat het de SQL-taal gebruikt om de gebruiker met de database-server te laten communiceren. De afkorting SQL wordt uitgesproken als síekwêl, in het Nederlands vaak als es-ku-el, en staat voor Structured Query Language. Ieder actie die moet worden uitgevoerd wordt geschreven in een SQL-query die door de server wordt verwerkt. Het voordeel van SQL is dat het eenvoudig door mensen geschreven en begrepen kan worden, terwijl het eenvoudig door computers geïnterpreteerd kan worden. In Tabel 20.6 staan een aantal voorbeelden van SQL-query's.

Tabel 20.6: SQL-query's
Handeling	Query
database maken	CREATE DATABASE `phpboek` CHARACTER SET = 'latin1' COLLATE = 'latin1_general_ci'
tabel maken	CREATE TABLE `gebruikers` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `gebruikersnaam` TINYTEXT, `wachtwoord` VARCHAR(32), `email` TINYTEXT, `laatst_ingelogd` TIMESTAMP ) ENGINE = 'MyISAM'
rij invoegen (SQL-standaard)	INSERT INTO `gebruikers` ( `gebruikersnaam`, `wachtwoord`, `email` ) VALUES ( 'jantje', SHA256('ABCDEFGH'), 'jantje@example.com' )
rij invoegen (MySQL-dialect)	INSERT INTO `gebruikers` SET `gebruikersnaam` = 'pietje', `wachtwoord` = SHA256('BCDEFGHI'), `email` = 'pietje@example.com'
gegevens lezen	SELECT * FROM `gebruikers` WHERE `id` = '1'
gegevens wijzigen	UPDATE `gebruikers` SET `wachtwoord` = SHA256('FGEW%^ds') WHERE `id` = '2'
rijen verwijderen	DELETE FROM `gebruikers` WHERE `id` = '1'
tabel wissen	TRUNCATE TABLE `gebruikers`
tabel verwijderen	DROP TABLE `gebruikers`

In SQL

worden sleutelwoorden zoals CREATE, TINYTEXT, WHERE, etc. geschreven in hoofdletters. Dit is niet verplicht, maar komt de leesbaarheid ten goede;
worden namen van databases, tabellen en kolommen geschreven tussen backticks (`). Dit is niet verplicht, maar voorkomt problemen wanneer (onbewust) sleutelwoorden worden gebruikt als namen;
worden waarden geschreven tussen enkele aanhalingstekens ('). Dit is niet verplicht voor bekende waarden zoals collaties en opslagmethoden en ook niet voor getallen, maar dit overal doen voorkomt dat het wordt vergeten;
mogen SQL-query's op één regel worden geschreven, maar dit is niet verplicht en is het verdelen van je query over meerdere regels vaak overzichtelijker.

phpMyAdmin

phpMyAdmin is een grafische interface om MySQL databases te beheren. Bij het testen van scripts is het handig om via phpMyAdmin mee te kijken met wat er met de database gebeurt.

phpMyAdmin wordt door de meeste webhosts standaard aangeboden bij een webhostingpakket. Als je gebruik maakt van XAMPP is phpMyAdmin beschikbaar via 127.0.0.1/phpmyadmin (tenzij je bij de installatie hebt gekozen op phpMyAdmin niet te installeren.

Contact | Over PHPBoek | Doneren | Copyright en aansprakelijkheid | Privacybeleid