ICTFF Section Informatique

Introduction

En SQL (comme pour les autres langages informatiques), les données sont séparées en plusieurs types (par exemple : texte, nombre entier, date…).
Lorsque l'on définit une colonne dans une table de la base, il faut donc lui donner un type, et toutes les données stockées dans cette colonne devront correspondre au type de la colonne.

Il est important de bien comprendre les usages et particularités de chaque type de données, afin de choisir le meilleur type possible lorsque vous définissez les colonnes de vos tables. En effet, choisir un mauvais type de données pourrait entraîner :

un gaspillage de mémoire (ex. : si vous stockez de toutes petites données dans une colonne faite pour stocker de grosses quantités de données)
des problèmes de performance (ex. : il est plus rapide de faire une recherche sur un nombre que sur une chaîne de caractères)
un comportement contraire à celui attendu (ex. : trier sur un nombre stocké comme tel, ou sur un nombre stocké comme une chaîne de caractères ne donnera pas le même résultat)
l'impossibilité d'utiliser des fonctionnalités propres à un type de données (ex. : stocker une date comme une chaîne de caractères vous prive des nombreuses fonctions temporelles disponibles)

Les entiers
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT et BOOLEAN

Type	Bytes	Min	Max
TINYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8388608	8388607
INT	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807
BOOLEAN	1	0	1

Si on stocke une valeur en dehors de l'intervalle permis par le type du champ, MySQL stockera la valeur la plus proche. Par exemple, 12457 dans un TINYINT, la valeur stockée sera 127, il faut donc bien réfléchir aux types des champs.

MySQL considère le type BOOLEAN comme un entier de type TINYINT.

unsigned

L'attribut unsigned permet d'obtenir uniquement des valeurs positives et, le nombre de valeurs représentables restant le même, on pourra pour un TINYINT par exemple récupérer les valeurs négatives et avoir des valeurs entre 0 et 255.

Limitation du nombre de chiffres à l'affichage

La syntaxe INT(2) permet d'imposer 2 chiffres minimum à l'affichage, cela ne change pas les capacités de stockage dans la colonne.
Si vous déclarez un INT(2), vous pourrez toujours y stocker 152365 par exemple. Sans précision, le caractère par défaut pour le remplissage est l'espace.

zerofill

La syntaxe INT(5) zerofill permet de prévoir un remplissage par des 0. Par exemple, la valeur 134 sera représentée par: 00134.

Pour la syntaxe INT(5) zerofillon aura:

Nombre stocké	Affichage
45	00045
2	00002
96312	96312
8132689	8132689

Les décimaux
DECIMAL, NUMERIC, FLOAT, REAL et DOUBLE

NUMERIC et DECIMAL

NUMERIC et DECIMAL sont équivalents et acceptent deux paramètres : la précision et l'échelle.

la précision définit le nombre de chiffres significatifs stockés, donc les 0 à gauche ne comptent pas. En effet 0024 est équivalent à 24. Il n'y a donc que deux chiffres significatifs dans 0024.
l'échelle définit le nombre de chiffres après la virgule

Le champ DECIMAL(5,3) doit être lu comme " champ qui contient 5 chiffres en tout dont trois sont après la virgule", il pourra donc stocker la valeur 99.999 au maximum.
On peut donc stocker des nombres de 5 chiffres significatifs maximum, dont 3 chiffres sont après la virgule. Par exemple : 12.354, -54.258, 89.2 ou -56. DECIMAL(4) équivaut à écrire DECIMAL(4, 0).

FLOAT, DOUBLE et REAL

Le mot-clé FLOAT peut s'utiliser sans paramètre, auquel cas quatre bytes sont utilisés pour stocker les valeurs de la colonne. Il est cependant possible de spécifier une précision et une échelle, de la même manière que pour DECIMAL et NUMERIC.

Quant à REAL et DOUBLE, ils ne supportent pas de paramètres. DOUBLE est normalement plus précis que REAL (stockage dans 8 bytes contre stockage dans 4 bytes), mais ce n'est pas le cas avec MySQL qui utilise 8 bytes dans les deux cas.
Il faut donc d'utiliser DOUBLE pour éviter les surprises en cas de changement de SGBDR.

Les alphanumériques
CHAR, VARCHAR et TEXT

CHAR et VARCHAR

Pour stocker un texte court (moins de 255 bytes), on utilise les types CHAR et VARCHAR. Ces deux types s'utilisent avec un paramètre qui précise la taille que peut prendre le texte (entre 1 et 255).
La différence entre CHAR et VARCHAR est la manière dont ils sont stockés en mémoire: un CHAR(x) stockera toujours x octets (en remplissant si nécessaire le texte avec des espaces vides pour le compléter), un VARCHAR(x) stockera jusqu'à x octets (entre 0 et x), et stockera en plus en mémoire la taille du texte stocké.
Si vous entrez un texte plus long que la taille maximale définie pour le champ, celui-ci sera tronqué.

Différences entre CHAR et VARCHAR
Texte	CHAR(5)	Espace (bytes)	VARCHAR(5)	Espace (bytes)
''	' '	5	''	1
'tex'	'tex '	5	'tex'	4
'texte'	'texte'	5	'texte'	6
'texte trop long'	'texte'	5	'texte'	6

Donc, quand on sait le nombre de bytes occupés par l'information (le code postal par exemple) on utilisera char(), si ce nombre est variable (comme le nom d'une personne par exemple) on utilisera varchar().

TEXT

Pour les textes de tailles supérieure à 255 caractères il suffit d'utiliser le type TEXT, ou un de ses dérivés TINYTEXT, MEDIUMTEXT ou LONGTEXT.
La différence entre ceux-ci étant la place qu'ils permettent d'occuper en mémoire.

Différences entre les différents types TEXT
Texte	Longueur de la chaîne	Espace (bytes)
TINYTEXT	2⁸	Longueur de la chaîne + 1 byte
TEXT	2¹⁶	Longueur de la chaîne + 2 byte
MEDIUMTEXT	2²⁴	Longueur de la chaîne + 3 byte
LONGTEXT	2³²	Longueur de la chaîne + 4 byte

Les temporels
DATE, DATETIME, TIME, TIMESTAMP et YEAR

DATE

Une date est entrée selon le format AAAA-MM-JJ, le caractère de séparation est libre, il n'est même pas obligatoire.
La seule chose obligatoire est l'ordre dans lequel on entre la date: anné --> mois --> jour.

Donc stocker une date sous les formes AAAAMMJJ ou AAAA+MM+JJ est correct.

MySQL supporte des DATE allant de '1001-01-01' à '9999-12-31'

DATETIME

Une date + 1 heure est entrée selon le format AAAA-MM-JJ HH:MM:SS, l'ordre est obligatoire ainsi que l'espace entre le jour et l'heure.

MySQL supporte des DATETIME allant de '1001-01-01 00:00:00' à '9999-12-31 23:59:59'

TIME

Une heure est entrée selon le format HH:MM:SS.

Il s'agit ici d'une forme simplifiée, il est aussi possible de stocker des intervalles de temps.

YEAR

Si vous n'avez besoin de retenir que l'année, YEAR est un type intéressant car il ne prend qu'un seul byte en mémoire. Cependant, un byte ne pouvant contenir que 256 valeurs différentes, YEAR est fortement limité : on ne peut y stocker que des années entre 1901 et 2155.

On peut entrer une donnée de type YEAR sous forme de chaîne de caractères ou d'entiers, avec 2 ou 4 chiffres. Si l'on ne précise que deux chiffres, le siècle est ajouté par MySQL selon les mêmes critères que pour DATE et DATETIME, à une exception près : si l'on entre 00 (un entier donc), il sera interprété comme la valeur par défaut de YEAR 0000. Par contre, si l'on entre '00' (une chaîne de caractères), elle sera bien interprétée comme l'année 2000.

TIMESTAMP (PM)

Par définition, le timestamp d'une date est le nombre de secondes écoulées depuis le 1er janvier 1970, 0h0min0s et la date en question. Les timestamps étant stockés sur 4 bytes, il existe une limite supérieure : le 19 janvier 2038 à 3h14min7s. Par conséquent, vérifiez bien que vous êtes dans l'intervalle de validité avant d'utiliser un timestamp.

Le type TIMESTAMP de MySQL est cependant un peu particulier. Prenons par exemple le 4 octobre 2011, à 21h05min51s. Entre cette date et le 1er janvier 1970, 0h0min0s, il s'est écoulé exactement 1317755151 secondes. Le nombre 1317755151 est donc, par définition, le timestamp de cette date du 4 octobre 2011, 21h05min51s.
Pourtant, pour stocker cette date dans un TIMESTAMP SQL, ce n'est pas 1317755151 qu'on utilisera, mais 20111004210551. C'est-à-dire l'équivalent, au format numérique, du DATETIME '2011-10-04 21:05:51'.
Le TIMESTAMP SQL n'a donc de timestamp que le nom. Il ne sert pas à stocker un nombre de secondes, mais bien une date sous format numérique AAAAMMJJHHMMSS (alors qu'un DATETIME est donc stocké sous forme de chaîne de caractères).

Il n'est donc pas possible de stocker un "vrai" timestamp dans une colonne de type TIMESTAMP. C'est évidemment contre-intuitif, et source d'erreur. Notez que malgré cela, le TIMESTAMP SQL a les même limites qu'un vrai timestamp : il n'acceptera que des date entre le 1e janvier 1970 à 00h00min00s et le 19 janvier 2038 à 3h14min7s.

Les binaires

Une chaîne binaire n'est rien d'autre qu'une suite de caractères. Cependant, si les textes sont affectés par l'encodage et l'interclassement, ce n'est pas le cas des chaînes binaires. Une chaîne binaire n'est rien d'autre qu'une suite de bytes. Aucune interprétation n'est faite sur ces bytes.

Ceci a deux conséquences principales:

une chaîne binaire traite directement le byte, et pas le caractère que le byte représente. Donc par exemple, une recherche sur une chaîne binaire sera toujours sensible à la casse, puisque "A" (code binaire : 01000001) sera toujours différent de "a" (code binaire : 01100001)
Tous les caractères sont utilisables, y compris les fameux caractères de contrôle non-affichables définis dans la table ASCII

Par conséquent, les types binaires sont parfaits pour stocker des données "brutes" comme des images par exemple, tandis que les chaînes de texte sont parfaites pour stocker...du texte !

Les types binaires sont définis de la même façon que les types de chaînes de texte. VARBINARY(x) et BINARY(x) permettent de stocker des chaînes binaires de x caractères maximum (avec une gestion de la mémoire identique à VARCHAR(x) et CHAR(x)). Pour les chaînes plus longues, il existe les types TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB, également avec les mêmes limites de stockage que les types TEXT.

Les types de données