Créa-blog

#100JoursPourCoder
Projet Créa-code

Ressources pour développeur web

Théme de la semaine : Allez plus loin avec le CSS

Les types de données MySQL : Guide complet (varchar, int…)

⏱️ Temps de lecture estimé : 11 minutes
Accueil Sécurité Les types de données MySQL : Guide complet (varchar, int…)

Lorsque vous commencez à créer une base de données MySQL, l’une des premières questions à se poser est : comment stocker correctement vos informations ? Chaque donnée que vous souhaitez conserver doit être associée à un type de données. Ce choix est crucial, car il influence non seulement la manière dont MySQL va stocker les informations, mais également la performance, la sécurité et la précision de vos requêtes.

Dans ce tutoriel, nous allons explorer de manière détaillée tous les types de données que vous pouvez rencontrer en MySQL. Nous expliquerons ce que chacun signifie, à quoi il sert, leurs avantages et leurs limites, et surtout comment et quand les utiliser.

Vous trouverez également des exemples pratiques pour chaque type de données, ce qui vous permettra de comprendre facilement leur fonctionnement dans des situations réelles.

Nous aborderons à la fois les types numériques, les types textuels, les types datés et temporels, ainsi que les types spéciaux comme ENUM et SET. L’objectif est que, à la fin de ce guide, vous puissiez choisir le type de données le plus adapté pour vos tables et éviter les erreurs fréquentes liées à un mauvais choix de type.

Qu’est-ce qu’un type de données en MySQL ?

Un type de données définit la nature d’une valeur que vous pouvez stocker dans une colonne d’une table. En d’autres termes, il s’agit d’un format qui indique à MySQL comment interpréter et gérer vos données. Par exemple, un numéro de téléphone, un nom, un identifiant ou une date ne sont pas stockés de la même manière.

Les types de données remplissent plusieurs rôles essentiels. Tout d’abord, ils garantissent que les données saisies sont cohérentes. Vous ne pouvez pas accidentellement stocker du texte dans une colonne conçue pour des nombres entiers.

Ensuite, ils permettent d’optimiser la mémoire et la performance des requêtes. Enfin, certains types de données offrent des fonctionnalités avancées comme le tri, la recherche ou la validation automatique.

Pourquoi choisir le bon type est important

Si vous choisissez un type de données inadapté, plusieurs problèmes peuvent survenir.

Par exemple, utiliser un type VARCHAR pour stocker un identifiant numérique peut ralentir vos requêtes si vous effectuez de nombreuses comparaisons ou tris. À l’inverse, stocker du texte dans un type CHAR de longueur fixe peut gaspiller de l’espace inutilement.

Un choix judicieux améliore la performance, la lisibilité et la maintenabilité de vos bases de données. Dans ce tutoriel, nous allons donc détailler les types les plus utilisés et leurs usages spécifiques.

Les types numériques

Les types numériques sont utilisés pour stocker des nombres. MySQL propose plusieurs types numériques, chacun adapté à une situation particulière. Les plus courants sont INTTINYINTSMALLINTBIGINTDECIMALFLOAT et DOUBLE.

INT et ses variantes

Le type INT (integer) permet de stocker des nombres entiers, positifs ou négatifs. Selon vos besoins, vous pouvez choisir des variantes qui consomment moins d’espace mémoire : TINYINTSMALLINT ou BIGINT.

TINYINT est idéal pour les petits nombres, comme le nombre d’articles en stock (0 à 255).
SMALLINT peut être utilisé pour des valeurs légèrement plus grandes, par exemple l’âge d’un utilisateur.
INT est le plus polyvalent et convient à la majorité des identifiants ou compteurs.
BIGINT est utile pour des valeurs très grandes, comme un compteur mondial d’utilisateurs ou des identifiants uniques générés automatiquement.

Chaque type a un intervalle précis, que MySQL gère automatiquement. Vous pouvez également définir si le champ doit être UNSIGNED, ce qui signifie que seules les valeurs positives seront autorisées, doublant ainsi l’intervalle positif possible.

Exemple : créer une table avec différents types d’entiers

CREATE TABLE produits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock SMALLINT UNSIGNED NOT NULL,
    ventes INT DEFAULT 0
);

Dans cet exemple, nous avons choisi BIGINT pour l’ID afin de permettre un très grand nombre de produits, SMALLINT pour le stock car la quantité est limitée, et INT pour les ventes, qui peuvent croître de manière plus significative.

DECIMAL, FLOAT et DOUBLE : pour les nombres à virgule

Pour stocker des valeurs décimales comme des prix ou des mesures scientifiques, MySQL propose DECIMALFLOATet DOUBLE.

DECIMAL est précis et stocke exactement la valeur que vous entrez. Il est parfait pour les montants financiers, car il évite les erreurs d’arrondi.
FLOAT et DOUBLE sont des nombres approximatifs, adaptés aux calculs scientifiques ou statistiques où une petite marge d’erreur est acceptable.

Exemple : table pour des produits avec prix et poids

CREATE TABLE produits_detail (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prix DECIMAL(8,2) NOT NULL,
    poids FLOAT NOT NULL
);

Ici, DECIMAL(8,2) signifie que le prix peut avoir jusqu’à 8 chiffres au total, avec 2 chiffres après la virgule. Le poids est stocké en FLOAT pour permettre des mesures approximatives, comme 1,234 kg.

Les types de données textuels

Les types textuels sont utilisés pour stocker des chaînes de caractères. Ils sont indispensables pour les noms, adresses, descriptions ou tout contenu alphanumérique. MySQL propose plusieurs types textuels adaptés à différentes tailles et besoins : CHARVARCHARTEXTTINYTEXTMEDIUMTEXT et LONGTEXT.

CHAR : texte à longueur fixe

Le type CHAR est utilisé pour stocker des chaînes de caractères dont la longueur est toujours la même. Il est utile lorsque vous connaissez exactement la taille des données à stocker. Par exemple, un code postal ou un identifiant à 10 caractères.

Le principal avantage du CHAR est la rapidité : comme chaque valeur a la même longueur, MySQL peut les lire et les manipuler très rapidement. Cependant, il peut gaspiller de l’espace si vos valeurs sont souvent plus courtes que la longueur définie, car MySQL complète automatiquement avec des espaces.

Exemple : table avec codes postaux et genre

CREATE TABLE utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code_postal CHAR(5) NOT NULL,
    genre CHAR(1)
);

Dans cet exemple, code_postal est toujours composé de 5 caractères et genre d’un seul caractère (M ou F).

VARCHAR : texte à longueur variable

Contrairement à CHARVARCHAR est utilisé pour les chaînes de longueur variable. Il est idéal pour les noms, prénoms, adresses email, titres ou tout texte dont la taille peut varier.

L’avantage principal est l’économie d’espace. MySQL ne réserve que l’espace nécessaire pour chaque valeur, plus un ou deux octets pour indiquer la longueur. L’inconvénient est une légère perte de performance lors de la lecture, surtout sur de très grandes tables.

Exemple : table avec noms et emails

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Ici, le nom peut faire jusqu’à 50 caractères et l’email jusqu’à 100, mais MySQL ne stockera que la taille réelle de chaque valeur.

TEXT : texte long

Pour stocker des textes très longs, comme des descriptions, des commentaires ou des articles, MySQL propose plusieurs types TEXT : TINYTEXTTEXTMEDIUMTEXT et LONGTEXT.

TINYTEXT convient pour de petites notes ou commentaires (255 caractères maximum).
TEXT est idéal pour des descriptions classiques (jusqu’à 65 535 caractères).
MEDIUMTEXT et LONGTEXT permettent de stocker de très longs contenus, comme des articles complets ou du contenu HTML.

Exemple : table pour articles de blog

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(150) NOT NULL,
    contenu TEXT NOT NULL
);

Ici, le titre est limité à 150 caractères pour garder la structure cohérente, tandis que contenu peut accueillir de longs articles.

Les types spéciaux : ENUM et SET

MySQL propose également des types spéciaux pour les cas où les valeurs possibles sont limitées et connues à l’avance. Ces types sont ENUM et SET.

ENUM : valeur unique parmi une liste définie

Le type ENUM permet de stocker une valeur unique choisie parmi une liste prédéfinie. C’est très pratique pour des champs comme le statut d’une commande, le type de compte ou la catégorie d’un produit.

Avantages :

  • Garantit l’intégrité des données, car seules les valeurs définies sont autorisées.
  • Facile à lire et à manipuler dans les requêtes.

Limites :

  • Ajouter ou supprimer une valeur de l’énumération nécessite de modifier la structure de la table.
  • Ne convient pas pour des listes très longues ou susceptibles de changer souvent.

Exemple : table pour commandes

CREATE TABLE commandes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    statut ENUM('en attente', 'expédiée', 'livrée', 'annulée') NOT NULL
);

Ici, le champ statut ne peut contenir qu’une des quatre valeurs définies.

SET : plusieurs valeurs parmi une liste définie

Le type SET est similaire à ENUM, mais il permet de stocker plusieurs valeurs parmi une liste prédéfinie. Il est utile lorsque vous souhaitez associer plusieurs options à une seule entrée.

Avantages :

  • Permet de stocker plusieurs choix sans créer de table supplémentaire.
  • Simple à utiliser pour de petites listes de valeurs fixes.

Limites :

Formation web et informatique - Alban Guillier - Formateur

Des formations informatique pour tous !

Débutant ou curieux ? Apprenez le développement web, le référencement, le webmarketing, la bureautique, à maîtriser vos appareils Apple et bien plus encore…

Formateur indépendant, professionnel du web depuis 2006, je vous accompagne pas à pas et en cours particulier, que vous soyez débutant ou que vous souhaitiez progresser. En visio, à votre rythme, et toujours avec pédagogie.

Découvrez mes formations Qui suis-je ?
  • Moins flexible que des tables de relation pour des listes dynamiques.
  • Limité à 64 options par champ.

Exemple : table pour les préférences utilisateur

CREATE TABLE utilisateurs_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    langues SET('fr', 'en', 'es', 'de') NOT NULL
);

Ici, un utilisateur peut choisir plusieurs langues parmi les quatre proposées.

Les types de données pour les dates et heures

MySQL offre plusieurs types de données spécialement conçus pour stocker les informations temporelles. Ces types permettent de gérer les dates, les heures et les intervalles avec précision, ce qui est indispensable pour des applications comme les agendas, les historiques de commandes, ou le suivi des utilisateurs.

DATE : pour stocker les dates

Le type DATE permet de stocker une date sous la forme AAAA-MM-JJ. Il est idéal pour enregistrer une date de naissance, une date de commande ou toute information temporelle qui ne nécessite pas l’heure.

Exemple : table pour utilisateurs avec date de naissance

CREATE TABLE utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    date_naissance DATE NOT NULL
);

Avantages :

  • Format standard et facile à lire.
  • Permet d’effectuer des comparaisons et des calculs sur les dates.

Limites :

  • Ne stocke pas l’heure, donc impossible de connaître le moment précis d’un événement dans la journée.

TIME : pour stocker une heure

Le type TIME est utilisé pour stocker uniquement l’heure au format HH:MM:SS. Il est pratique pour des horaires de travail, des durées ou des horaires d’événements.

Exemple : table pour horaires d’ouverture

CREATE TABLE horaires (
    id INT AUTO_INCREMENT PRIMARY KEY,
    jour VARCHAR(10) NOT NULL,
    ouverture TIME NOT NULL,
    fermeture TIME NOT NULL
);

Avantages :

  • Stocke précisément les heures, minutes et secondes.
  • Permet de calculer des durées facilement.

Limites :

  • Ne contient pas la date, donc il ne peut pas situer l’événement dans le temps complet.

DATETIME : pour stocker date et heure

Le type DATETIME combine date et heure sous la forme AAAA-MM-JJ HH:MM:SS. Il est parfait pour les enregistrements qui nécessitent un horodatage précis, comme les journaux d’activité ou les commandes en ligne.

Exemple : table pour historique des commandes

CREATE TABLE historique_commandes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_utilisateur INT NOT NULL,
    date_commande DATETIME NOT NULL
);

Avantages :

  • Permet de stocker la date et l’heure exactes d’un événement.
  • Compatible avec de nombreuses fonctions MySQL pour le calcul et la comparaison.

Limites :

  • Occupe plus d’espace mémoire qu’un simple DATE ou TIME.

TIMESTAMP : pour horodatage automatique

Le type TIMESTAMP est similaire à DATETIME, mais il offre une fonctionnalité particulière : il peut être mis à jour automatiquement à chaque modification d’une ligne. Il est souvent utilisé pour suivre la dernière modification d’un enregistrement.

Exemple : table avec suivi des modifications

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(150) NOT NULL,
    contenu TEXT NOT NULL,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Avantages :

  • Met automatiquement à jour l’horodatage lors de chaque modification.
  • Très pratique pour le suivi des changements sans avoir besoin d’un trigger supplémentaire.

Limites :

  • Limité à la période entre 1970 et 2038 pour certaines versions de MySQL.

Conseils pour bien choisir et utiliser les types de données

  1. Privilégiez la précision : utilisez un type adapté à vos données pour éviter les erreurs et optimiser l’espace.
  2. Évitez VARCHAR excessif : ne définissez pas de longueurs trop grandes pour les colonnes de texte si ce n’est pas nécessaire.
  3. Utilisez ENUM ou SET avec parcimonie : parfait pour les listes fixes, mais évitez pour des données susceptibles de changer fréquemment.
  4. Soyez cohérent avec les dates : préférez DATE pour les dates seules et DATETIME ou TIMESTAMP pour les horodatages complets.
  5. UNSIGNED pour les nombres positifs : cela permet d’augmenter l’intervalle positif disponible.
  6. Normalisez vos unités : pour des mesures, choisissez toujours la même unité (ex. kg ou g) pour éviter les conversions lors des calculs.

Tableau récapitulatif des types de données MySQL

Type de donnéesDescription / ContenuCapacité maximale / Taille
Numériques
TINYINTEntier petit-128 à 127 (SIGNED), 0 à 255 (UNSIGNED)
SMALLINTEntier court-32 768 à 32 767 (SIGNED), 0 à 65 535 (UNSIGNED)
MEDIUMINTEntier moyen-8 388 608 à 8 388 607 (SIGNED), 0 à 16 777 215 (UNSIGNED)
INT / INTEGEREntier standard-2 147 483 648 à 2 147 483 647 (SIGNED), 0 à 4 294 967 295 (UNSIGNED)
BIGINTEntier grand-9 223 372 036 854 775 808 à 9 223 372 036 854 775 807 (SIGNED), 0 à 18 446 744 073 709 551 615 (UNSIGNED)
DECIMAL / NUMERICNombre exact à virgule fixeDéfinie par DECIMAL(M,D) → M = chiffres totaux, D = décimales (M ≤ 65)
FLOATNombre à virgule simple précision~ ±3.402823466E+38, 7 chiffres significatifs
DOUBLE / REALNombre à virgule double précision~ ±1.7976931348623157E+308, 16 chiffres significatifs
Chaînes de caractères
CHAR(n)Chaîne de longueur fixe0 à 255 caractères
VARCHAR(n)Chaîne de longueur variable0 à 65535 caractères (limite pratique dépendant de la taille de la ligne)
TINYTEXTTexte court255 caractères
TEXTTexte moyen65 535 caractères (~64 Ko)
MEDIUMTEXTTexte plus long16 777 215 caractères (~16 Mo)
LONGTEXTTrès long texte4 294 967 295 caractères (~4 Go)
ENUMListe de valeurs possiblesJusqu’à 65 535 valeurs uniques
SETEnsemble de valeurs possibles (multi-sélection)Jusqu’à 64 valeurs uniques
Dates et heures
DATEDate1000-01-01 à 9999-12-31
DATETIMEDate et heure1000-01-01 00:00:00 à 9999-12-31 23:59:59
TIMESTAMPDate et heure (UTC, auto-gestion)1970-01-01 00:00:01 UTC à 2038-01-19 03:14:07 UTC
TIMEHeure-838:59:59 à 838:59:59
YEARAnnée1901 à 2155

Les types UNSIGNED sont toujours positifs et doublent la capacité maximale. Pour les chaînes, la taille maximale dépend aussi du type de table et de l’encodage (UTF-8 prend plus d’espace par caractère). TEXT et BLOB sont similaires, mais BLOB est pour données binaires.

Exemple complet : table utilisateur avec différents types de données

Voici un exemple d’architecture de table combinant plusieurs types de données pour un site web fictif :

CREATE TABLE utilisateurs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pseudo VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    mot_de_passe CHAR(60) NOT NULL,
    date_naissance DATE NOT NULL,
    genre ENUM('M', 'F', 'Autre') NOT NULL,
    langues SET('fr', 'en', 'es', 'de') DEFAULT 'fr',
    date_inscription TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dernier_login DATETIME NULL
);

Dans cet exemple :

  • id est un identifiant unique et positif.
  • pseudo et email sont stockés avec des longueurs variables adaptées à leur usage.
  • mot_de_passe utilise CHAR(60) pour stocker un hash fixe.
  • date_naissance est une date simple.
  • genre est un ENUM pour garantir l’intégrité des valeurs.
  • langues est un SET pour permettre plusieurs choix de langues.
  • date_inscription et dernier_login utilisent TIMESTAMP et DATETIME pour le suivi des connexions.

Erreurs fréquentes à éviter avec les types de données

Choisir un type de données inadapté peut engendrer des problèmes difficiles à corriger par la suite. Voici les principales erreurs à éviter :

Utiliser VARCHAR ou TEXT pour des nombres : Stocker des nombres sous forme de texte peut sembler pratique, mais cela pose des problèmes pour les calculs, les tris et les recherches. Par exemple, trier des identifiants stockés en VARCHAR donnera des résultats incorrects si les nombres ont des longueurs différentes. Toujours privilégier INT ou DECIMAL selon le cas.

Choisir CHAR quand VARCHAR suffit : CHAR réserve toujours la longueur définie, ce qui peut gaspiller de l’espace pour des chaînes courtes. Si vos valeurs sont variables, comme des noms ou des emails, utilisez VARCHAR pour optimiser l’espace et la performance.

Ne pas utiliser UNSIGNED quand c’est possible : Pour les nombres qui ne peuvent jamais être négatifs, comme le stock d’un produit ou le nombre d’utilisateurs, ajoutez UNSIGNED. Cela permet d’augmenter l’intervalle de valeurs positives et évite les erreurs lors de l’insertion de données négatives.

Abuser d’ENUM et SET: Ces types sont pratiques pour de petites listes de valeurs fixes, mais ils deviennent problématiques si la liste doit évoluer régulièrement. Dans ce cas, il est préférable de créer une table séparée et de gérer les relations via des clés étrangères.

Mal gérer les dates et heures : Confondre DATEDATETIME et TIMESTAMP peut entraîner des erreurs lors des calculs ou comparaisons de dates. Assurez-vous de comprendre si vous avez besoin uniquement d’une date, d’une heure ou d’un horodatage complet.

Optimiser la performance

Le choix des types de données influence directement la performance des requêtes et la taille de la base de données. Voici quelques recommandations :

Choisir le type le plus petit possible

Utilisez le type qui correspond exactement à vos besoins. Par exemple, stocker un booléen en TINYINT(1) au lieu d’INTéconomise de la mémoire et accélère les calculs.

Limiter la taille des chaînes

Pour les colonnes textuelles, ne dépassez pas inutilement la longueur maximale. Un VARCHAR(255) suffit souvent, sauf cas particulier.

Indexer judicieusement

Les index accélèrent les recherches, mais occupent de l’espace. Les types courts et fixes (comme CHAR ou INT) sont plus efficaces pour les index que des colonnes TEXT ou VARCHAR très longues.

Préférer DECIMAL pour l’argent

Pour les valeurs monétaires, utilisez DECIMAL plutôt que FLOAT ou DOUBLE afin d’éviter les erreurs d’arrondi lors des calculs financiers.

Normaliser les données

Pour des valeurs répétitives (statuts, catégories, langues), la normalisation via des tables séparées permet d’éviter les redondances et de simplifier la maintenance.

Exemple de bonnes pratiques appliquées

Imaginons une table pour une boutique en ligne. Voici une structure optimisée :

CREATE TABLE produits (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    description TEXT,
    prix DECIMAL(10,2) NOT NULL,
    stock SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    categorie_id INT UNSIGNED NOT NULL,
    date_ajout TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Dans cet exemple :

  • id est un entier positif auto-incrémenté pour les identifiants.
  • nom utilise un VARCHAR adapté à la longueur moyenne des noms.
  • description est en TEXT pour permettre de longs textes.
  • prix est en DECIMAL pour la précision monétaire.
  • stock est un SMALLINT UNSIGNED, suffisant pour le nombre de produits.
  • categorie_id fait référence à une table normalisée des catégories.
  • date_ajout est un TIMESTAMP pour horodater automatiquement l’ajout du produit.

Cette structure combine performance, précision et intégrité des données.


Choisir le bon type de données en MySQL est essentiel pour garantir la fiabilité, la performance et la maintenabilité de vos bases de données. Les types numériques, textuels, temporels et spéciaux offrent des outils puissants pour répondre à tous les besoins, mais chacun doit être utilisé à bon escient.

Les clés d’une bonne conception sont la précision, la normalisation, la cohérence des unités et l’optimisation de la taille des colonnes. En suivant les conseils de ce tutoriel, vous pourrez concevoir des tables efficaces, éviter les erreurs courantes et faciliter l’évolution de votre application dans le temps.

En résumé, MySQL vous offre une grande variété de types de données. Comprendre leurs particularités, leurs avantages et leurs limites vous permet de construire des bases solides, performantes et durables. Prenez le temps de choisir le type adapté pour chaque colonne, et vous verrez vos bases de données fonctionner plus vite et plus efficacement.