Comment valider les données dans MySQL

Un guide pratique de la validation de la qualité des données dans MySQL — les six contrôles dont toute table a besoin, le SQL pour les écrire, et les pièges de coercition, de collation et de dates zéro qui font de MySQL un champion pour dissimuler les données fausses.

2026-08-28 · 8 min read

Pour valider les données dans MySQL, exprimez chaque attente sous forme de requête d'agrégation renvoyant un nombre de violations, exécutez-les par lot, et déclenchez un échec dès qu'un compte franchit son seuil. Les six contrôles qui comptent sont : valeurs nulles dans les colonnes obligatoires, clés en double, valeurs hors de l'ensemble autorisé, nombres hors d'une plage plausible, chaînes malformées et lignes périmées. MySQL rend l'exercice plus difficile qu'il n'y paraît, car c'est le moteur le plus disposé à accepter des données fausses sans rien dire : coercition de type implicite, dates permissives et égalité dépendante de la collation conspirent pour faire passer des lignes invalides pour valides.

Pourquoi MySQL dissimule les données fausses

Trois comportements expliquent l'essentiel des surprises.

La coercition implicite. Comparer une colonne de texte à un nombre ne produit pas d'erreur — MySQL convertit la chaîne. WHERE amount_text = 0 correspond aussi bien à 'abc', '' qu'à '0.00', parce que les trois se convertissent en 0. Toute validation qui compare des types différents mesure autre chose que ce que vous croyez.

Les dates zéro. À moins que NO_ZERO_DATE et NO_ZERO_IN_DATE ne figurent dans sql_mode, '0000-00-00' est une DATE stockable. Ce n'est pas NULL, donc les contrôles de complétude passent ; ce n'est pas une vraie date, donc les contrôles de fraîcheur la comparent au seuil et rapportent la ligne comme vieille de plusieurs siècles.

L'égalité dépendante de la collation. La collation par défaut utf8mb4_0900_ai_ci ignore les accents et la casse : 'PAID', 'paid' et 'páid' ne font donc qu'une seule valeur. Sur une colonne _bin ou _cs, ce sont trois valeurs distinctes. Le même contrôle de valeurs autorisées signifie des choses différentes sur deux tables d'une même base.

Rien de tout cela n'est un bug. Ce sont des comportements par défaut avec lesquels vous devez composer. Les contrôles eux-mêmes sont les six que vous écririez pour n'importe quel moteur — le guide complet de la validation des données couvre ce flux de travail commun ; ce qui suit en est la version correcte pour MySQL.

Les six contrôles dont toute table a besoin

1. Complétude — valeurs nulles dans les colonnes obligatoires

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `order_id` IS NULL;

Sur une table antérieure au mode strict, élargissez le contrôle pour attraper l'équivalent en chaîne vide d'une valeur nulle :

WHERE `order_id` IS NULL OR `order_id` = '';

2. Unicité — clés métier en double

SELECT COUNT(*) AS violations
FROM (
    SELECT `order_id`
    FROM `sales`.`orders`
    WHERE `order_id` IS NOT NULL
    GROUP BY `order_id`
    HAVING COUNT(*) > 1
) AS dupes;

Souvenez-vous de la réserve sur la collation : sur une colonne insensible à la casse, cette requête signale correctement 'ORD-1' et 'ord-1' comme des doublons. Sur une colonne _bin, non. Décidez de ce que vous voulez dire et figez la collation.

3. Conformité — valeurs hors de l'ensemble autorisé

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `status` IS NOT NULL
  AND `status` NOT IN ('pending', 'paid', 'shipped', 'refunded');

La protection IS NOT NULL est obligatoire — NULL NOT IN (...) vaut NULL, la ligne sort du compte, et une colonne majoritairement nulle affiche zéro violation.

Le type ENUM de MySQL donne l'impression de rendre ce contrôle inutile. Il n'en est rien : hors mode strict, une valeur ENUM invalide est stockée sous forme de chaîne vide plutôt que rejetée, si bien que la colonne peut contenir une valeur absente de sa propre définition.

4. Exactitude — nombres hors d'une plage plausible

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `total_amount` IS NOT NULL
  AND (`total_amount` < 0 OR `total_amount` > 100000);

Utilisez DECIMAL pour les montants. FLOAT et DOUBLE se comparent avec des erreurs d'arrondi, et une colonne d'entiers non signés transforme silencieusement une valeur négative en un très grand nombre positif — ce qu'un contrôle de plage attrapera, et une contrainte de schéma non.

5. Conformité — identifiants malformés

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `reference` IS NOT NULL
  AND `reference` NOT REGEXP '^ORD-[0-9]{6}
#39;;

MySQL 8.0 a remplacé l'ancien moteur POSIX par ICU : \d, \w, les quantificateurs paresseux et les classes Unicode fonctionnent donc tous. Sur MySQL 5.7 ou MariaDB, l'ancien moteur est plus limité — tenez-vous-en à des motifs compatibles POSIX comme [0-9] et [[:alpha:]] si vous devez prendre en charge les deux. REGEXP est également sensible à la collation : sur une collation _ci, la correspondance est insensible à la casse quel que soit le motif, utilisez donc REGEXP BINARY lorsque la casse compte.

6. Ponctualité — fraîcheur

SELECT COUNT(*) AS violations
FROM `sales`.`orders`
WHERE `created_at` < UTC_TIMESTAMP() - INTERVAL 24 HOUR;

Utilisez UTC_TIMESTAMP(), pas NOW(). NOW() renvoie le fuseau horaire de la session, qui, pour un client connecté depuis une autre région, n'est pas celui du serveur, et le contrôle se décale sans prévenir. Si la colonne est de type TIMESTAMP plutôt que DATETIME, MySQL convertit déjà en UTC à l'écriture — l'un des rares endroits où son comportement implicite rend service.

Ajoutez une protection contre les dates zéro là où le mode strict n'est pas imposé :

WHERE `created_at` = '0000-00-00 00:00:00'
   OR `created_at` < UTC_TIMESTAMP() - INTERVAL 24 HOUR;

Obtenir un utilisateur en lecture seule sans risque

CREATE USER 'catalyst_ro'@'%' IDENTIFIED BY '<generated>';
GRANT SELECT ON `analytics`.* TO 'catalyst_ro'@'%';

SELECT suffit à lui seul — information_schema est lisible par n'importe quel compte, automatiquement restreint aux objets que ce compte peut voir, si bien que l'import de schéma ne nécessite aucune autorisation supplémentaire. Si vous exploitez un réplica, pointez la connexion dessus : ce sont des parcours d'agrégation, ils n'ont rien à faire sur le primaire en écriture.

Fixez un plafond pour qu'un parcours sur une table non indexée ne puisse pas monopoliser un thread :

SET SESSION max_execution_time = 60000;   -- milliseconds, SELECT only

Du SQL ponctuel au contrat de données

Les contrôles écrits à la main se dégradent. La version fiable est déclarative : les attentes vivent à côté du schéma, dans un format relisible en pull request et exécutable par un runner. Catalyst utilise l'Open Data Contract Standard :

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.1.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: char(36)
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: decimal(12,2)
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}
#39;"
      - name: created_at
        logicalType: timestamp
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"

Catalyst importe les colonnes et les types depuis information_schema, propose un contrat de base, compile chaque règle vers le SQL MySQL présenté ci-dessus, et enregistre pass, warn ou fail pour chaque contrôle, avec les lignes fautives consultables. Le YAML fait l'aller-retour sans perte : modifier une règle dans le constructeur ne reformate pas le fichier et ne supprime pas vos commentaires.

Pièges MySQL à connaître

Planification et alertes

Exécutez les contrôles de chaque jeu de données juste après le job qui le charge, et laissez la planification suivre la cadence des données plutôt qu'un cron à l'heure ronde. Alertez sur les transitions — un contrôle qui passe de pass à fail — plutôt qu'à chaque exécution d'un contrôle toujours en échec, et réservez la gravité error aux règles qui doivent réellement arrêter les consommateurs en aval. Les contrôles de motif sur des champs en texte libre relèvent généralement du niveau warning, où ils constituent une tendance plutôt qu'une alerte à réveiller quelqu'un.

Questions fréquentes

Puis-je valider des données MySQL sans écrire de SQL ?

Oui. Déclarez l'attente — obligatoire, unique, valeurs autorisées, plage numérique, expression régulière, âge maximal, clé étrangère — et l'outil la compile en SQL correct pour MySQL. Catalyst lit vos colonnes depuis information_schema, suggère un contrat de départ à partir des types et de la nullabilité qu'il y trouve, et ne demande du SQL que lorsque la logique est réellement sur mesure, via une règle customSql.

La validation des données exige-t-elle un accès en écriture à MySQL ?

Non. GRANT SELECT suffit — chaque contrôle est un SELECT d'agrégation qui renvoie un seul nombre. Catalyst se connecte en lecture seule et ne stocke que des métadonnées et des résultats ; vos lignes restent dans votre base.

Cela fonctionne-t-il avec MariaDB et Amazon Aurora MySQL ?

Oui, avec une réserve : MariaDB a conservé l'ancien moteur d'expressions régulières POSIX, si bien que les motifs utilisant \d, \w ou des quantificateurs paresseux se comportent différemment de MySQL 8.0. Tenez-vous-en aux classes de caractères POSIX pour des règles portables. Aurora MySQL reproduit le comportement de MySQL en amont pour tout ce que couvre ce guide.

Pourquoi mon contrôle de complétude passe-t-il sur une colonne pleine de chaînes vides ?

Parce que '' n'est pas NULL. Hors mode strict, MySQL convertit de nombreuses insertions invalides en chaînes vides ou en zéros plutôt que de les rejeter : une colonne peut donc être entièrement remplie et entièrement dépourvue de sens. Écrivez le contrôle de complétude de façon à couvrir les deux cas, et ajoutez une règle validValues ou regex pour ce que la colonne est réellement censée contenir.

Comment cela se compare-t-il aux contraintes CHECK ?

MySQL n'a commencé à appliquer les contraintes CHECK qu'en 8.0.16 — auparavant, elles étaient analysées puis ignorées, ce qui constitue une catégorie de piège à part entière. Même lorsqu'elles sont appliquées, les contraintes rejettent les lignes à l'écriture, ce qui est souvent le mauvais comportement pour une table analytique que vous préféreriez charger puis mettre en quarantaine. Un contrat de données décrit la garantie, s'exécute selon une planification, et vous donne les lignes en échec au lieu d'un chargement rejeté.