Types de colonnes SQL

L’intérêt de bien choisir les types de colonnes

Bien choisir les types de colonnes d’une base de données permet d’assurer une certaine intégrité du format des données. En effet, en stockant une date dans un champ de type date on s’assure de ne pas permettre l’enregistrement d’une donnée erronée ou totalement différente.

Un bon type de colonne permet également d’utiliser moins d’espace de stockage en évitant d’utiliser inutilement des types réservant de grandes zones en mémoire. Utiliser un champ de type VARCHAR au lieu d’un type TEXT permet de gagner plusieurs octets par enregistrement. Tout comme l’usage du type DATE au lieu de VARCHAR, et les exemples sont nombreux.

Les formats numériques entiers

Les données pouvant être apparentée à un nombre entier sont à enregistrer dans un format INT. Le moteur de BDD stocke cette donnée en binaire et un octet peur prendre 256 formes. Donc pour un champ numérique cela revient à pouvoir stocker un nombre entre 0 et 255 compris. Pour un champ texte limité à 1 octet on ne stockerait qu’un seul caractère, donc un nombre compris de 0 à 9.

Type	Poids en octets	Valeur min	Valeur max
TINYINT	1	-128	127
SMALLINT	2	-32 768	32 767
MEDIUMINT	3	-8 388 608	8 388 607
INT	4	-2 147 483 648	2 147 483 647
BIGINT	8	-9 223 372 036 854 775 808	9 223 372 036 854 775 807

S’il n’est pas utile de devoir stocker des nombres négatifs, la colonne peut utiliser l’attribut UNSIGNED. Seuls des nombres positifs pourront alors être enregistrés et la limite maximale sera plus importante. Cela s’explique par le simple fait qu’il ne sera plus nécessaire pour le moteur de BDD d’utiliser un bit pour stocker le signe + ou -. La valeur minimale étant zéro, voici les valeurs maximales pour chaque type :

Type	Poids en octets	Valeur max
TINYINT	1	255
SMALLINT	2	65 535
MEDIUMINT	3	16 777 215
INT	4	4 294 967 295
BIGINT	8	18 446 744 073 709 551 615

Les format numériques flottants

Pour les nombres à virgule, il existe le type FLOAT qui occupe 4 octets et le type DOUBLE qui occupe 8 octets. La différence est dans la précision. Le type DOUBLE est plus précis car il stocke 48 chiffres après la virgule contre 31 pour le type FLOAT.

Il existe également le type DECIMAL pour lequel on spécifie le nombre de chiffres maximum souhaité avant et après la virgule. Par exemple DECIMAL(5, 3) autorise au maximum 5 chiffres dont 3 après la virgule. Ce type utilise un peu plus d’espace mémoire que DOUBLE et FLOAT.

Une astuce pour économiser en espace est de tout stocker sous forme d’entiers. En effet, pour stocker 32,233 par exemple, on peut décider de le multiplier par 1000 pour l’enregistrer dans un type MEDIUMINT puis de le diviser par 1000 lorsque l’on récupère la valeur. MEDIUMINT utilise seulement 3 octets là ou DECIMAL(5,3) en consommerait 7.

Notez qu’il arrive que le moteur de BDD stocke dans un DOUBLE la valeur 8,97 mais que lorsque l’on test si cette valeur est égale à 8,97 en PHP par exemple, le résultat soit faux. Cela peut arriver car la valeur a pu être stockée avec une précision non visible. Il est donc recommandé d’arrondir systématiquement au nombre de décimales souhaité les valeurs enregistrées en base de données et, par précautions, d’arrondir à nouveau au moment de leur récupération / de leur utilisation.

Les formats date et heure

Voici les formats date et heure disponible en MySQL :

Type	Poids en octets	Typage	Format
TIME	3	Chaîne	HH:MM:SS
TIMESTAMP	4	Numérique	AAAAMMJJHHMMSS
DATETIME	8	Chaîne	AAAA-MM-JJ HH:MM:SS
DATE	3	Chaîne	AAAA-MM-JJ
YEAR	1	Numérique	AAAA

Chaque donnée a des limites logiques. Les minutes et secondes sont comprises entre 0 et 59, les heures entre 0 et 23, les jours entre 1 et 28 à 31 selon le mois, les mois entre 1 et 2.

YEAR est limité à 1 octet. Ses bornes sont donc 1901 et 2155. Si l’on insère un nombre entre 0 et 69 il lui sera automatiquement ajouté 2000. Si l’on insère un nombre entre 70 et 99 il lui sera ajouté 1900. Ce type est assez peu recommandé également.

Les colonnes à choix multiples / unique

Le type SET permet de prédéfinir les valeurs possibles d’une colonne. Par exemple, une colonne ayant comme valeurs possibles pile et face, il sera possible d’enregistrer l’une ou l’autre ou les deux valeurs ou encore vide. Cela consomme peu en mémoire car le moteur MySQL ne consomme qu’un quart d’octet pour une valeur possible sur quatre. De 1 à 8 valeurs possibles la colonne occupera 1 octet, puis 2 octets de 9 à 16 valeurs possibles, 3 octets de 17 à 24 valeurs possibles, 4 octets de 25 à 32 valeurs possibles, et enfin 8 octets de 33 à 64 valeurs possibles.

Il existe également le type ENUM. Ce dernier ne peut pas prendre plusieurs valeurs possible. C’est l’une des valeurs possibles ou vide. Il occupe encore moins de place, un seul octet pour 0 à 255 valeurs possibles, 2 octets pour aller jusqu’à 65 535 valeurs possibles.

Les colonnes de texte

Les types servant à stocker du texte consomme tous un octet par caractère. La taille minimale est donc de 1 octet. La mémoire utilisé est ensuite dynamique. En effet, pour chaque enregistrement, la taille utilisée sera celle nécessaire pour le texte à enregistrer. Selon le type, 1 à 4 octets supplémentaires sont utilisés pour stocker la longueur du texte.

Type	Taille maximale	Poids en octet
CHAR	255	1 (pas d’octet supplémentaire pour la taille ici, car c’est forcément 1 pour un seul caractère)
VARCHAR	255 65 535 depuis MySQL 5.5	Longueur + 1
TINYBLOB TINYTEXT	255	Longueur + 1
BLOB TEXT	65 535	Longueur + 2
MEDIUMBLOB MEDIUMTEXT	16 777 215	Longueur + 3
LONGBLOB LONGTEXT	4 294 967 296	Longueur + 4

La différence entre les types BLOB et TEXT est que BLOB stocke les informations directement en binaire, ce qui permet de stocker le contenu d’une image ou d’un exécutable tout comme du texte. TEXT ne sert qu’à stocker du texte. Quelques nuances sont à connaitre, notamment le fait que l’interclassent est par conséquent inutile sur les types BLOB. Le tri et la comparaison seront également différents car étant appliqués sur des données binaires ils seront sensibles à la casse, là où les types TEXT ne le sont pas.