Choisir le type de colonne de ses tables MySQL

Pour optimiser au mieux sa base de données MySQL, il faut bien choisir ses types de colonnes et leur interclassement afin d'optimiser la taille des tables et des index. Voici quelques conseils pour les types de données les plus courants.

Booléen

TINYINT UNSIGNED ou ENUM('N','Y'). La deuxième solution est plus explicite, mais moins portable car le type ENUM n'est pas disponible avec certaines bases de données (anciennes version de PostgreSQL, Oracle, IBM DB2...).

Hash MD5 ou SHA1

Généralement on utilise un CHAR(32) et un interclassement ascii_bin pour stocker le MD5 au format hexadécimal. Mais cette solution n'est pas optimale. En effet, en notation hexadécimale, chaque octet est codé sur deux caractères (0 -> 00, 128 -> 80, 255 -> FF). En utilisant un BINARY(16) pour le stocker au format binaire on gagne 16 octets par enregistrement. Pour cela, on peut en PHP, passer true en deuxième argument à la fonction md5 pour obtenir la version binaire du hash. Côté MySQL, on peut utiliser la fonction UNHEX pour transformer la chaine renvoyée par la fonction MD5 (vous pouvez trouver plus d'informations sur le sujet dans la documentation MySQL).

On peut utiliser la même technique pour un hash SHA1. Seule la taille des champs diffère (40 caractères pour la version hexadécimale, 20 pour la version binaire).

Pour plus de détail, vous pouvez consulter cet article sur le stockage d'un mot de passe dans une table MySQL.

URL

Les URL sont encodées en ASCII et peuvent être écrites en minuscules et/ou en majuscules, il faut donc choisir un interclassement ascii_general_ci (si on choisit un ascii_bin, la comparaison entre deux chaines lors d'un tri se fera par rapport aux codes des caractères et les adresses écrites en majuscules sortiront en premier). Attention, les caractères non-ASCII seront remplacés par un point d'interrogation.

D'après cette page, les URL commencent à poser des problèmes à certains navigateurs (IE pour ne pas le nommer) quand leur longueur dépasse 2 000 caractères. Il faut donc utiliser le type TEXT qui peut stocker jusqu'à 65 535 octets. L'espace occupé correspond à la longueur de l'URL auquel il faut ajouter deux octets qui servent à stocker la longueur de la chaine.

Adresses IP

Le plus souvent, on les enregistre dans une colonne de type CHAR(15) (15 octets) avec un interclassement ascii_bin.

On peut cependant gagner de l'espace en utilisant un entier (INT UNSIGNED 4 octets). Il faut alors utiliser avec les fonctions MySQL INET_ATON et INET_NTOA ou les fonctions PHP ip2long et long2ipd pour effectuer la conversion entre la notation habituelle et la valeur numérique.

Nom d'hôte

D'après la RFC 1034 :

By convention, domain names can be stored with arbitrary case, but domain name comparisons for all present domain functions are done in a case-insensitive manner, assuming an ASCII character set, and a high order zero bit.

To simplify implementations, the total number of octets that represent a domain name (i.e., the sum of all label octets and label lengths) is limited to 255.

On choisira donc un TINYTEXT en ascii_general_ci.

Adresse E-mail

Une adresse E-mail est composée de deux parties : une partie locale et un nom de domaine. Ces deux éléments sont séparés par le signe @. Comme nous venons de le voir ci-dessus, le nom de domaine est limité à 255 caractères. Pour ce qui est de la partie locale, la RFC 2821 indique que la partie locale ne doit pas dépasser 64 caractères. On aurait donc une longueur maximale égale à 64 + 1 + 255, soit 320 caractères. Le jeu de caractère utilisé est l'ASCII et bien qu'elles soient généralement écrites en minuscules, il peut arriver qu'un utilisateur les saisisse en majuscules. On doit donc utiliser un VARCHAR(320) en ascii_general_ci.

Code postal

En France, c'est 5 chiffres. On peut donc utiliser un MEDIUMINT UNSIGNED qui prends 3 octets et qui peut stocker des valeurs de 0 à 16 777 215. A l'international, les codes postaux peuvent également comporter des lettres (comme dans le code postal britannique) et aller jusqu'à 10 caractères (par exemple le code ZIP+4 aux Etats-Unis). Un CHAR(10) en ascii_general_ci sera donc plus adapté.

Nom d'une ville

D'après Wikipédia, le plus long nom de commune de France comporte 45 caractères. Pour le reste du monde, vous devriez être tranquille avec un champ dimensionné pour accueillir 60 lettres.

Numéro de TVA intracommunautaire

En France, le numéro de TVA intracommunautaire est constitué du code ISO pour la France (FR), d'une clé informatique à 2 chiffres et du numéro SIREN de l'entreprise (à 9 chiffres). On utilisera donc un CHAR(13) en ascii_bin.

Numéro de compte international (IBAN)

D'après Wikipédia, le code IBAN comprend au maximum 34 caractères alphanumériques. On utilisera donc un CHAR(34) en ascii_bin.

Code d'identification d'une banque (BIC ou code SWIFT)

D'après Wikipédia, le code BIC est constitué de 8 ou 11 caractères alphanumériques. On utilisera donc un CHAR(11) en ascii_bin.

Devise

On peut utiliser les codes de la norme ISO 4217. Pour l'Euro, on utilisera donc la valeur EUR et par exemple USD pour le dollar américain. Le type de colonne dans MySQL sera donc un CHAR(3) en ascii_bin.

Si d'autres types de données vous viennent à l'esprit, n'hésitez pas à laisser un commentaire ci-dessous. On pourrait par exemple se demander quelle taille pour un prénom, un nom, un numéro de téléphone international...

ID MongoDB

Un ID BSON est composé de 12 octets, généralement représenté sous forme d'une chaîne hexadécimale qu'on pourra stocker dans une colonne de type CHAR(24) en ascii_bin

Fuseau horaire

Si vous stockez l'information sous forme d'une chaîne comme Europe/Paris par exemple, si je me base sur la table fournie avec MySQL, on peut utiliser un CHAR(32) en ascii_bin.