Derrière la quasi-totalité des sites web et applications web, se cache une base de donnée SQL. Et lors de la conception de celle-ci, il faut choisir avec justesse les différents types de colonnes. Chose qui s’avère parfois plus difficile que prévu… Quelle est la différence entre un CHAR et un VARCHAR ? Qu’est-ce que sont les BLOB ? Ce petit guide permettra d’éviter certaines erreurs qui pourraient bien, à terme, nous coûter quelques gigaoctets supplémentaires et des ralentissements inutiles.

On va se concentrer sur MySQL parce que c’est la BDD Open Source la plus répandue, mais que ce soit MySQL, PostgreSQL ou une autre ne change pas grand chose, étant donnée qu’elles sont toutes censées respecter la norme SQL – bien qu’en pratique, elles ont chacune quelques spécificités – on s’y retrouvera facilement dans un autre SGBD.

Chaines de caractères

CHAR & VARCHAR

Beaucoup ignorent la différence entre les deux puisqu’ils permettent tous deux de stocker des chaines de caractères jusqu’à 255 octets. La différence réside en fait dans la taille nécessaire pour stocker la chaine. Le CHAR(x) stockera sur x octets, donc si vous optez pour un CHAR(255) pour votre colonne, chaque champs prendra 255 octets, même si l’on y stocke qu’un mot. À l’inverse, le VARCHAR, comme son nom l’indique, a une taille variable. Le VARCHAR(x) stocke sur x octets maximum, donc un VARCHAR(255) peut ne peser que 64 octets.

Dans ce cas, pourquoi ne pas toujours opter pour le VARCHAR ? Il est un peu plus lourd pour une même quantité de données stockées, puisque en plus de votre chaine de caractères, il stocke la taille de celle-ci (puisqu’elle est variable).

On utilisera donc le CHAR pour une chaine de caractères qui est toujours de même longueur, par exemple une référence client qui fait toujours le même nombre de caractères (désolé, j’ai pas d’exemple plus parlant qui me vient à l’esprit :P ) tandis qu’un VARCHAR sera parfait pour un pseudo.

Note : depuis MySQL 5.5, VARCHAR peut aller jusqu’à 65 535 octets, soit la même taille que TEXT.

TEXT & BLOB

Les types TEXT, qui vont de TINYTEXT à LONGTEXT, servent à stocker des chaines de caractères à partir 256 octets et jusqu’à 2^32, soit 4 294 967 296 octets. Par exemple, sous Wordpress, le contenu de chaque article est stocké dans un LONGTEXT, avec ça, on peut donc écrire des romans !

Les types BLOB sont similaires à TEXT, cependant, ils stockent les données en binaire et non en chaine de caractères. Deux points important les différencient de leurs homologues TEXT :

• comme ils stockent les chaines en binaire, il est possible d’utiliser indifféremment plusieurs sets de caractères, ce qui n’est pas possible avec TEXT puisqu’on définit un set pour la colonne et on s’y tient.
• De plus, le type TEXT ne tient pas compte de la casse, donc il est possible de comparer une chaine majuscule à une minuscule, avec BLOB ce n’est pas possible puisqu’il traitera la code représentant les signes de la chaine, et non les signes eux-même.

L correspond à la taille de la chaine stockée, et le +x est la taille en octets nécessaire à stocker la taille de la chaine.

Les numériques

Les entiers

Les nombres entiers sont utilisés avec les colonnes de type INT, qui vont de TINYINT à BIGINT.

Deux attributs sont particulièrement intéressants avec les entiers. Il s’agit de ZEROFILL et de UNSIGNED. Le premier permet d’ajouter des 0 à gauche de votre nombre pour qu’il atteigne un nombre de caractères prédéfini. Le second permet de “doubler” la capacité d’une colonne.

• Il est possible de spécifier une taille minimale de caractère avec les INT. Si le nombre entré n’atteint pas le nombre de chiffres minimal spécifié, l’attribut ZEROFILL lui ajoutera des 0 à gauche. Par exemple, pour avoir au minimum 5 caractères, vous pouvez faire INT(5) ZEROFILL, si vous entrez 8, alors le ZEROFILL complétera pour enregistrer 00008.
• L’attribut UNSIGNED quant à lui, permet d’ignorer le signe. On garde donc la même intervalle de stockage, mais en n’autorisant que les positifs, on double le nombre le plus grand autorisé. Pour le TINYINT par exemple, au lieu d’avoir une intervalle comprise entre -128 et 127, l’intervalle est comprise entre 0 et 255, ce qui est bien pratique dans certains cas !

Les décimaux

NUMERIC & DECIMAL

Ils sont équivalents et peuvent prendre deux paramètres (mais ce n’est pas obligé), la précision et l’échelle.

• La précision représente le nombre de chiffres significatifs (donc pas les 0 à gauche)
• L’échelle donne le nombre de chiffres après la virgule

Par exemple, DECIMAL(6,3) pourra stocker : 5,11; 005,108; 123,108… Attention, la précision comprend les chiffres avant ET après la virgule. Donc avec une précision de 6 et une échelle de 3, vous pouvez stocker des nombres avec trois chiffres avant la virgule et trois chiffres après.

FLOAT, DOUBLE & REAL

FLOAT peut s’utiliser sans paramètre, cependant dans ce cas, quatre octets supplémentaires seront utilisés pour la valeur de la colonne. Il est aussi possible de l’utiliser avec une précision et une échelle, de la même manière que NUMERIC et DECIMAL.

DOUBLE et REAL s’utilisent sans paramètres. DOUBLE est normalement le double de REAL dans la norme SQL, c’est à dire qu’il y a une précision de 4 octets pour REAL et 8 pour DOUBLE. Néanmoins, MySQL aborde une précision de 8 dans les deux cas, alors autant utiliser DOUBLE. De cette manière, si on change de SGBD, on évitera les surprises.

Quelle est la différence entre NUMERIC, DECIMAL et FLOAT ?

NUMERIC et DECIMAL stockent les valeurs sous forme de chaine de caractère, tandis que FLOAT, mais aussi DOUBLE et REAL, les stockent sous forme de nombre. C’est cool de le savoir, mais pas grand intérêt me direz-vous. Eh bien si, puisqu’un nombre a virgule n’est jamais tout à fait exact, donc sous forme de nombre, les valeurs sont approchées.

C’est à dire que votre 2,045 pourra être stocké sous sa valeur approchée 2,0450001. Ce qui n’est pas tout à fait la même chose… et qui risque de poser problème si nous avons à faire des comparaisons dessus. En revanche, sous forme de chaine de caractères, vos nombres ne bougent pas. C’est toujours bon à savoir, selon les applications, la différence peut s’avérer importante.

Heures et dates

DATE, TIME, DATETIME & YEAR

Comme leurs noms respectifs l’indiquent, ces quatre types servent à stocker la date, l’heure, l’heure/date et l’année. Ils sont respectivement stockés sous les formats suivants :

• AAAA-MM-JJ (mais AAAAMMJJ ou AAMMJJ marchent aussi)
• HH:MM:SS (mais aussi HHMMSS)
• AAAA-MM-JJ HH:MM:SS (mais aussi AAAAMMJJHHMMSS)
• AAAA

Attention, dans le format ou vous ne précisez pas le siècle, MYSQL prendra le 20 siècle par défaut pour les dates après 70 tandis qu’il infèrera que vous parlez du XXI si vous avez une date comprise entre 00 et 69 inclus. Autant fournir un format AAAA pour éviter les erreurs ;)

MySQL supporte les dates comprise entre 1001-01-01 et 9999-12-31, sauf pour YEAR qui supporte un intervalle compris entre 1901 et 2155, inclus (mais qui a l’avantage d’être très léger car codé sur un seul octet).

TIMESTAMP

On l’utilise rarement car il ne correspond pas au TIMESTAMP UNIX (qui est celui de la majorité des langages de programmation), dans lequel le TIMESTAMP correspond au nombre de secondes écoulées entre le 1er Janvier 1970 à OOh00m00s et la date en question, logiquement exprimé en secondes. MySQL les stockent en effet sous le même format que le DATETIME, peu pratique donc, pour lui passer un TIMESTAMP généré par php, ruby ou autre.

Colonnes à choix multiples

ENUM, comme sont nom l’indique, permet d’énumérer des choix. Elle fonctionne un peu différemment des autres colonnes puiqu’on doit au préalable renseigner les différentes valeurs qu’elle peut prendre. On lui assigne donc plusieurs choix par défaut. Par exemple, prenons une colonne genre dans laquelle nous voulons répertorier plusieurs genres cinématographique :

genre ENUM('horreur', 'comedie', 'thriller', 'reportage')

Voici un exemple de colonne ENUM :

Cette colonne ne pourra contenir d’autre valeurs que celles précisées lors de la définition du ENUM, sauf :

• NULL (si autorisé lors de l’enregistrement de la colonne)
• une chaine vide si l’on tente d’insérer une valeur non valide.

L’index de la chaine vide est 0. Pour insérer une valeur, on peut soit soumettre directement la valeur en question, soit fournir son index en argument. Le type ENUM peut avoir 65535 valeurs différentes. Sa force est qu’il ne prend vraiment pas de place puisqu’il est codé sur 1 octet !

SET est semblable à ENUM à cela près qu’une même ligne peut prendre plusieurs valeurs. Par exemple, pour la colonne ci-dessous :

genre SET('horreur', 'comédie', 'thriller', 'reportage')

Une entrée peut prendre comme valeur ‘horreur’ ou ‘thriller’, mais aussi ‘horreur, thriller’. Pour enregistrer les valeurs, le fonctionnement est à nouveau assez semblable à celui de ENUM, dans le cas où l’on soumet directement la valeur de celui-ci. Pour donner plusieurs valeurs, il faut les séparer par des virgules, par exemple, pour avoir thriller et comédie dans la même ligne on fera ‘thriller, comédie’.

Il est aussi possible d’utiliser les index pour enregistrer les valeurs. Nous avons dans l’ordre horreur, comédie, thriller et reportage. Donc un nombre de quatre chiffre (puisque quatre valeurs). Si le chiffre correspondant à la valeur est à 1, on l’enregistre, si elle est à 0, on ne l’enregistre pas. Un petit tableau serait peut-être le bien venu :

Donc 0000 donnera une ligne vide, 0001 donnera reportage, 1010 donnera horreur, thriller etc…

Cependant, contrairement à ENUM, SET ne peut prendre que 64 valeurs.

Enfin, attention, SET et ENUM sont propres à MySQL, si vous les utilisez, il sera par la suite difficile de changer de SGBD.

Vous commencez avec MySQL ? Initiez-vous à MySQL en ligne de commande !