Data types in MySQL#

MySQL voorbeelden

Dit gedeelte gaat over data types in MySQL. Deze verschillen van de (eenvoudiger) data types in SQLite. De MySQL voorbeelden in dit hoofdstuk kun je niet allemaal uitvoeren in SQLite. Voor de volledigheid hebben we dit gedeelte uit het originele materiaal overgenomen.

In het algemeen kun je zeggen dat er drie soorten kolomgegevens zijn:

  • getallen (numeriek)

  • tekst (alfanumeriek)

  • datums

Datatypes kunnen verschillende namen hebben in verschillende databases maar in principe werkt het overal hetzelfde. Controleer altijd de documentatie voor de details van de datatypen want zelfs bij dezelfde namen kunnen de details verschillend zijn.

Een overzicht van alle datatypes vind je hier:

https://www.w3schools.com/sql/sql_datatypes.asp

Numeriek#

INTEGER#

Het datatype INTEGER is een eindige verzameling van gehele getallen. Ze kunnen zowel positief als negatief zijn en 0 hoort er ook bij. Als afkorting voor INTEGER wordt INT gebruikt. Een aantal datatypen is gebaseerd op integers. Het is belangrijk het datatype zo efficiënt mogelijk te kiezen zodat de opslag in de database zo klein mogelijk blijft. Zo is het bijvoorbeeld niet nodig de lidnummers van een vereniging het datatype INT of zelfs MEDIUMINT te geven aangezien een vereniging niet snel zoveel leden zal hebben als het maximum aantal dat je hiermee kunt maken.

UNSIGNED#

Met UNSIGNED kun je bepalen dat het datatype met 0 moet beginnen. Met een TINYINT heb je bijvoorbeeld 256 mogelijkheden. Dat is één Byte van -128 tot en met 127. Het getal 0 moet je ook meerekenen, vandaar het maximum van 127. Als je bepaalt dat het datatype UNSIGNED is, begin je met 0 en dan heb je de mogelijkheden van 0 tot en met 255. Dat zijn ook weer in totaal 256 mogelijkheden (één Byte).

Type

Bytes

Minimum

Maximum

Maximum Unsigned

TINYINT

1

-128

127

255

SMALLINT

2

-32768

32767

65535

MEDIUMINT

3

-8388608

8388607

16777215

INT

4

-2147483648

2147483647

4294967295

BIGINT

8

\(-2^{63}\)

\(2^{63} - 1\)

\(2^{64} -1\)

FLOAT

Een klein getal met een aantal decimalen.

DOUBLE

Een groot getal met een aantal decimalen.

DECIMAL

Een DOUBLE opgeslagen als een STRING.

Bij een FLOAT, DOUBLE en DECIMAL kun je het maximum aantal cijfers vastleggen en ook het aantal cijfers achter de komma. Let op dat in de standaard notatie een punt wordt gebruikt waar wij een komma gebruiken.

Alfanumeriek#

De belangrijkste alfanumerieke datatypen zijn de volgende.

CHAR#

Wordt gebruikt voor een veld waar een STRING in komt met een vaste lengte, bijvoorbeeld een postcode. Je kunt er maximaal 255 letters, cijfers en speciale karakters in stoppen.

VARCHAR#

Wordt gebruikt voor een veld waar een STRING in komt met een variabele lengte, bijvoorbeeld een adres of e-mailadres. Je kunt er maximaal 255 letters, cijfers en speciale karakters in stoppen.

TINYTEXT#

Kan een STRING bevatten met een maximale lengte van 255 karakters.

TEXT#

Kan een STRING bevatten met een maximale lengte van 65,535 karakters.

ENUM#

Met ENUM kun je een keuzelijst maken.

Datums#

DATE#

Het datatype DATE wordt gebruikt voor de dag van de maand, de maand en het jaar.

SELECT last_name, hire_date
FROM employees
WHERE last_name = 'King';

DATETIME#

Het datatype DATETIME is een combinatie van datum en tijd van het formaat YYYY-MM-DD HH:MI:SS.

TIMESTAMP#

Het TIMESTAMP datatype is een uitbreiding van het DATETIME type waarbij waarden in milliseconden kunnen worden opgeslagen en weergegeven. De invoer wordt opgeslagen als een 32-bit getal waarbij gestart is met 0 op 1 januari 1970.

YEAR#

Het datatype YEAR kun je opslaan als YY (twee cijfers) of als YYYY (vier cijfers).

Conversie#

Soms vindt er impliciete conversie van een datatype plaats. Dat houdt in dat het systeem automatisch een datatype omzet. Bij de tabel employees is de kolom bonus bijvoorbeeld van het type VARCHAR (tekst).

Stel dat je daar toch mee wil rekenen en er 100 bij op wil tellen. Dan wordt automatisch het veld bonus omgezet in een numeriek datatype.

SELECT last_name, bonus, bonus + 100
FROM employees
WHERE bonus IS NOT NULL;

Ook de functies MIN, MAX, AVG, en SUM werken.

SELECT MIN(bonus), MAX(bonus), AVG(bonus), SUM(bonus)
FROM employees;

Het is echter niet verstandig om het op impliciete datatype conversie te laten aankomen. Gebruik zo nodig conversiefuncties zoals CAST of CONVERT die hierna behandeld worden, zodat er expliciete conversie plaatsvindt. Het maakt SQL beter leesbaar en robuuster. Maar nog beter is om bij het maken van de database voor numerieke informatie het juiste type te kiezen (bijvoorbeeld INT of DECIMAL) en géén VARCHAR.

Het gaat bijvoorbeeld fout als je CONCAT gebruikt in combinatie met AVG voor informatie van het type VARCHAR. De tweede kolom van onderstaande informatie is niet de uitvoer die je graag zou hebben. In de derde kolom is ROUND ervoor gezet en wordt het weer hersteld.

SELECT AVG(bonus), CONCAT(AVG(bonus), ' dollar'), CONCAT(ROUND(AVG(bonus)), ' dollar')
FROM employees;

Cast#

Om datatypes om te zetten van het ene type naar het andere type kun je CAST gebruiken. De syntax is dan:

CAST(expression AS data_type(length))

Waarbij length optioneel is en die hoef je dus niet per se toe te voegen.

Zet een datum om naar tekst en toon enkel het jaartal (de eerste vier karakters):

SELECT last_name,hire_date, CAST(hire_date AS CHAR(4))
FROM employees
WHERE last_name = 'King';

Zet een datum van het formaat DATE om naar het formaat DATETIME:

SELECT last_name,hire_date, CAST(hire_date AS DATETIME)
FROM employees
WHERE last_name = 'King';

Zet een VARCHAR om naar een INTEGER:

SELECT last_name, CAST(bonus AS INTEGER)
FROM employees
WHERE bonus IS NOT NULL;

Convert#

Met CONVERT kun je precies hetzelfde doen als met CAST: het datatype omzetten van het ene type naar het andere. De syntaxis is:

CONVERT(value, type)

SELECT last_name,hire_date, CONVERT(hire_date, char)
FROM employees
WHERE last_name = 'King';


SELECT last_name,hire_date, CONVERT(hire_date, datetime)
FROM employees
WHERE last_name = 'King';