Base de données

Écrire un script SQL rejouable : le rendre idempotent

4 septembre 2026 · par Romain RICHER

Vous lancez un script de mise à jour sur la base de production. À la moitié, une erreur : une colonne trop courte, une contrainte violée, la connexion qui tombe. Vous corrigez le problème, vous relancez — et là tout explose. Table 'clients' already exists, puis des lignes en double partout. Le script n’était pas rejouable. Un script rejouable, ou idempotent, peut être exécuté deux fois, dix fois, sans que la base finisse dans un état différent. Ce guide montre comment y arriver, cas par cas, avec les pièges propres à MySQL, MariaDB et Oracle.

Le test tient en une phrase

Un script est idempotent si l’exécuter deux fois laisse la base dans le même état que l’exécuter une fois. C’est tout. Pas besoin de théorie : prenez une base vide, exécutez votre script, notez le résultat, relancez-le, comparez.

Ce n’est pas une élégance d’architecte. C’est ce qui décide si un incident de déploiement se règle en trente secondes ou en deux heures. Un script rejouable se relance après une coupure ; un script qui ne l’est pas oblige à restaurer une sauvegarde, ou à reconstituer à la main ce que la première tentative avait déjà écrit.

Les fichiers produits par phpMyAdmin ou mysqldump ne le sont pas. Ils supposent une base vide. C’est un choix légitime pour une restauration complète, mais dès qu’on s’en sert pour livrer une évolution, le problème apparaît.

Cas 1 : créer et supprimer des objets

Le plus simple, et celui qui bloque en premier. Sur MySQL et MariaDB, deux mots-clés suffisent :

-- Avant : plante à la seconde exécution
CREATE TABLE clients (id INT PRIMARY KEY, nom VARCHAR(60));
DROP TABLE anciens_clients;

-- Après : rejouable
CREATE TABLE IF NOT EXISTS clients (id INT PRIMARY KEY, nom VARCHAR(60));
DROP TABLE IF EXISTS anciens_clients;

Pour une vue, la formulation est encore plus directe, et elle a l’avantage de mettre à jour la définition au passage :

CREATE OR REPLACE VIEW v_clients_actifs AS
SELECT id, nom FROM clients WHERE actif = 1;

Attention en revanche aux procédures, fonctions et déclencheurs : MySQL et MariaDB n’ont pas de CREATE OR REPLACE pour ces objets. Il faut les faire précéder d’une suppression conditionnelle :

DROP PROCEDURE IF EXISTS calculer_remise;
DELIMITER $$
CREATE PROCEDURE calculer_remise(IN client_id INT)
BEGIN
  -- ...
END$$
DELIMITER ;

Oracle, lui, connaît CREATE OR REPLACE pour les vues, les procédures et les fonctions, ce qui règle la question — voyez la présentation du PL/SQL pour la syntaxe des blocs.

Cas 2 : ajouter une colonne, et la divergence MySQL / MariaDB

C’est ici que les deux moteurs, souvent traités comme interchangeables, ne le sont plus du tout. MariaDB accepte IF NOT EXISTS sur l’ajout d’une colonne ou d’un index depuis la version 10.0.2 :

-- MariaDB
ALTER TABLE clients ADD COLUMN IF NOT EXISTS telephone VARCHAR(20);
ALTER TABLE clients ADD KEY IF NOT EXISTS idx_nom (nom);

Un détail qui coûte cher : sur les clés, IF NOT EXISTS se place après le type de clé, pas après ADD. Écrire ADD IF NOT EXISTS PRIMARY KEY (id) provoque une erreur de syntaxe, alors que la même formule fonctionne pour une colonne. La bonne forme est :

ALTER TABLE clients ADD PRIMARY KEY IF NOT EXISTS (id);

MySQL 8, lui, ne connaît pas IF NOT EXISTS sur un ALTER TABLE ni sur un CREATE INDEX. Il faut interroger le dictionnaire de données et construire l’instruction à la volée :

SET @ddl := (SELECT IF(COUNT(*) > 0,
                       'DO 0',
                       'ALTER TABLE `clients` ADD COLUMN `telephone` VARCHAR(20)')
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME   = 'clients'
               AND COLUMN_NAME  = 'telephone');
PREPARE st FROM @ddl; EXECUTE st; DEALLOCATE PREPARE st;

DO 0 est une instruction qui ne fait rien : c’est le « ne rien faire » du cas où la colonne existe déjà. Pour un index, on interroge information_schema.STATISTICS au lieu de COLUMNS, et pour une contrainte, information_schema.TABLE_CONSTRAINTS.

Sur Oracle, la logique est différente : on encapsule l’ordre dans un bloc PL/SQL qui absorbe l’erreur attendue, et seulement celle-là.

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE clients ADD (telephone VARCHAR2(20))';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -1430 THEN RAISE; END IF;   -- ORA-01430 : colonne déjà existante
END;
/

Le IF SQLCODE != … est essentiel. Un EXCEPTION WHEN OTHERS THEN NULL qui avale tout masquerait aussi les vraies erreurs — un type invalide, une table absente — et vous découvririez le problème bien plus tard. Les codes utiles sont -955 (objet déjà existant), -1430 (colonne déjà existante) et -942 (table inexistante, pour un DROP).

Une remarque de méthode : rendre l’ordre rejouable ne dit rien de son coût. Ajouter une colonne à une table de cent millions de lignes reste une opération longue et verrouillante, et c’est un sujet à part entière — voyez modifier la structure d’une table non vide pour les précautions à prendre avant de lancer un tel ALTER en production.

Cas 3 : les INSERT, le vrai problème

Le DDL se règle mécaniquement. Les données, non. Pour rendre un INSERT rejouable, il faut d’abord répondre à une question qu’aucune syntaxe ne tranche : qu’est-ce qui fait que deux lignes sont « la même » ? C’est une question métier, et la réponse s’appelle une clé.

Trois formulations existent, avec des conditions d’emploi différentes.

N’insérer que si la ligne est absente

INSERT INTO clients (id, nom)
SELECT 1, 'Dupont' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE id = 1);

C’est la formulation la plus portable : elle fonctionne sur MySQL, MariaDB et Oracle (avec dual en minuscules), et surtout elle n’exige aucun index unique déclaré — seulement une colonne sur laquelle tester. Elle passe donc même sur une table dont la contrainte n’a pas encore été créée par le script.

Pour un INSERT multi-lignes, on évite d’écrire une instruction par ligne en passant par une table dérivée :

INSERT INTO clients (id, nom)
SELECT s.id, s.nom FROM (
    SELECT 1 AS id, 'Dupont' AS nom FROM DUAL
  UNION ALL SELECT 2, 'Martin' FROM DUAL
  UNION ALL SELECT 3, 'Durand' FROM DUAL
) s
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE clients.id = s.id);

Référencer la table cible dans un sous-requête d’un INSERT … SELECT est autorisé : la restriction MySQL bien connue (erreur 1093) ne concerne que UPDATE et DELETE.

INSERT IGNORE

INSERT IGNORE INTO clients (id, nom) VALUES (1, 'Dupont');

Plus court, mais deux réserves. D’abord il exige une contrainte unique réellement déclarée : sans elle, il n’ignore rien et insère un doublon. Ensuite, et c’est plus grave, IGNORE transforme en simples avertissements les troncatures de données et les violations de clé étrangère. Une vraie erreur passe alors inaperçue. À réserver aux cas où vous savez exactement ce que vous faites taire.

Insérer ou mettre à jour

-- MariaDB
INSERT INTO clients (id, nom) VALUES (1, 'Dupont')
ON DUPLICATE KEY UPDATE nom = VALUES(nom);

-- MySQL 8.0.20 et suivantes : VALUES() est déprécié, on passe par un alias
INSERT INTO clients (id, nom) VALUES (1, 'Dupont') AS n
ON DUPLICATE KEY UPDATE nom = n.nom;

L’équivalent Oracle est le MERGE. Cette forme est la bonne pour un jeu de données de référence — codes postaux, libellés, paramétrage — qu’on veut voir aligné sur le script à chaque passage. Elle est en revanche dangereuse sur des données que quelqu’un a pu modifier entre-temps : le rejeu écrase les corrections manuelles sans rien dire.

Cas 4 : le piège de l’identifiant auto-incrémenté

Voici l’exemple qu’aucune astuce ne sauve :

INSERT INTO commandes (client_id, montant) VALUES (42, 150.00);

La table a un id auto-incrémenté, absent de l’insertion. Rejouez le script : une seconde commande apparaît, identique à la première mais avec un autre id. Rien, dans l’instruction, ne permet de reconnaître qu’il s’agit de la même donnée.

Il n’y a que deux issues, et toutes deux exigent une décision de votre part. Soit vous écrivez l’identifiant explicitement — c’est ce que font les exports, et c’est pourquoi un dump phpMyAdmin se prête bien à la transformation. Soit vous désignez une clé métier, une combinaison de colonnes qui identifie la ligne dans le monde réel :

INSERT INTO commandes (client_id, reference, montant)
SELECT 42, 'CMD-2026-0173', 150.00 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM commandes WHERE reference = 'CMD-2026-0173');

La référence de commande joue ici le rôle que l’identifiant technique ne peut pas tenir. C’est le moment de rappeler qu’une clé métier mérite souvent une contrainte UNIQUE : elle documente l’intention et protège la base même quand personne ne relit le script.

Cas 5 : l’UPDATE qui se lit lui-même

La plupart des UPDATE sont déjà idempotents, et on ne s’en rend même pas compte :

UPDATE clients SET statut = 'actif' WHERE date_derniere_commande > '2026-01-01';

Posez la même valeur dix fois, le résultat est le même. L’exception est l’assignation qui lit la colonne qu’elle écrit :

-- NON idempotent : chaque exécution ajoute 1
UPDATE compteurs SET valeur = valeur + 1 WHERE cle = 'visites';

-- NON idempotent non plus : le texte s'allonge à chaque passage
UPDATE articles SET titre = CONCAT(titre, ' (archivé)') WHERE archive = 1;

Il n’existe pas de réécriture automatique de ces cas : le script doit être repensé. Pour le second exemple, une condition suffit à rendre l’opération sûre :

UPDATE articles SET titre = CONCAT(titre, ' (archivé)')
WHERE archive = 1 AND titre NOT LIKE '% (archivé)';

Le DELETE, lui, est naturellement rejouable : la seconde exécution ne trouve plus rien à supprimer. Le TRUNCATE aussi, techniquement — mais rejoué après un import réussi, il vide la table avant de la remplir. Si votre script doit compléter des données existantes, c’est exactement ce qu’il ne faut pas.

Cas 6 : le piège des collations dans les tests d’existence

Celui-ci ne se voit qu’à l’exécution, et il surprend. Pour éviter de répéter un long libellé, on le range dans une variable :

SET @titre := 'Pomme d''amour';
SET @id := (SELECT id FROM recettes WHERE titre = @titre LIMIT 1);

Sur une base WordPress, la seconde ligne échoue :

#1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
        and (utf8mb4_general_ci,IMPLICIT) for operation '='

La variable hérite de la collation de la connexion, souvent utf8mb4_general_ci ; la colonne porte celle de la table, utf8mb4_unicode_ci. MySQL refuse de comparer deux collations quand aucune n’est explicite. On peut corriger la connexion avec SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci, mais cela fige la collation de votre base dans le script — rejoué ailleurs, l’erreur revient. La solution portable consiste à retirer la collation de l’équation :

SET @id := (SELECT id FROM recettes WHERE titre = CAST(@titre AS BINARY) LIMIT 1);

Une valeur explicitement binaire n’a plus de collation à mélanger. Bonus appréciable ici : la comparaison devient octet par octet, sans équivalence d’accents ni de casse — ce qu’on veut précisément pour un test d’existence, où « Crêpes » et « Crepes » ne doivent pas être confondus.

Vérifier, plutôt que croire

Un script « rendu idempotent » sur le papier ne l’est que si vous l’avez exécuté deux fois. La méthode tient en quatre commandes :

# 1. Une base de référence : l'import d'origine, une seule fois
mysql ref < export.sql

# 2. Une base d'essai : le script transformé, trois fois de suite
mysql essai < script-rejouable.sql
mysql essai < script-rejouable.sql
mysql essai < script-rejouable.sql

# 3. Les données sont-elles identiques ?
mysql ref   -N -e "SELECT * FROM clients ORDER BY id" | md5sum
mysql essai -N -e "SELECT * FROM clients ORDER BY id" | md5sum

# 4. Et la structure ?
mysql ref   -e "SHOW CREATE TABLE clients\G" > /tmp/a
mysql essai -e "SHOW CREATE TABLE clients\G" > /tmp/b
diff /tmp/a /tmp/b

Trois exécutions plutôt que deux : certaines erreurs n’apparaissent qu’au troisième passage, quand un objet créé au premier a été modifié au second. Si vous comparez aussi les index avec SHOW INDEX, ignorez la colonne Cardinality : c’est une statistique estimée, pas un élément de structure.

Enveloppez enfin votre script dans une transaction quand c’est possible. Sur Oracle, le DDL est transactionnel ; sur MySQL et MariaDB il ne l’est pas, et un CREATE TABLE valide implicitement ce qui précède. La transaction protège donc les données, pas la structure — raison de plus pour rendre le DDL rejouable.

Un outil pour la partie mécanique

Ajouter les IF NOT EXISTS, éclater les ALTER TABLE à clauses multiples, conditionner chaque INSERT sur sa clé : c’est répétitif et facile à rater sur un fichier de plusieurs milliers de lignes. L’outil de transformation en script rejouable fait ce travail dans votre navigateur — le fichier n’est envoyé nulle part, ce qui compte quand il s’agit d’un dump de production.

Il lit le schéma présent dans le fichier pour retrouver la clé de chaque table, y compris quand phpMyAdmin la déclare dans un ALTER TABLE ADD PRIMARY KEY séparé plutôt que dans le CREATE TABLE. Et quand aucune clé n’est trouvable, il ne devine pas : il recopie l’instruction telle quelle et la classe « à reprendre ». Un INSERT transformé sur une mauvaise clé écraserait des données — mieux vaut un échec visible qu’une supposition silencieuse.

Pour relire le résultat confortablement, le formateur SQL indente et aligne les clauses. Et si votre chantier est un changement de version plutôt qu’un déploiement, l’outil de migration de version traite ce cas-là.

En résumé

Un script rejouable s’obtient par couches : IF NOT EXISTS et IF EXISTS pour les objets, CREATE OR REPLACE pour les vues, un test sur information_schema quand MySQL 8 refuse la syntaxe courte, un bloc PL/SQL filtrant le SQLCODE sur Oracle. Les INSERT demandent une décision : quelle colonne identifie une ligne. Sans réponse à cette question, aucun outil ne peut rien — et un identifiant auto-incrémenté absent des valeurs insérées ferme définitivement la porte. Reste à vérifier en exécutant trois fois et en comparant, parce qu’un script réputé idempotent qui n’a jamais été rejoué ne l’est pas encore. Pour aller plus loin, voyez les requêtes SQL du quotidien, la gestion d’une base avec phpMyAdmin et les précautions à prendre pour modifier la structure d’une table non vide.

🔒 Connexion