Base de données

Modifier une table SQL sans perdre ses données : les 3 méthodes

8 juillet 2026 · par Romain RICHER

Modifier la structure d’une table vide, c’est trivial : on la supprime, on la recrée, et c’est réglé. Mais dès qu’une table contient des données — parfois des millions de lignes, parfois en production — la moindre modification de sa structure devient une opération délicate. Ajouter une colonne, changer un type, renommer un champ : chacune de ces opérations peut, mal menée, verrouiller la table, ralentir l’application, voire faire perdre des données.

Cet article présente les grandes méthodes pour faire évoluer la structure d’une table non vide, avec leurs avantages et leurs inconvénients, pour vous aider à choisir la bonne stratégie selon votre situation. Les exemples utilisent une table fictive clients pour illustrer le propos.

Pourquoi c’est délicat

Trois risques principaux pèsent sur toute modification de structure d’une table pleine.

La perte de données. Certaines opérations, comme changer le type d’une colonne ou la supprimer, peuvent détruire des données si elles sont mal préparées. Convertir une colonne texte en numérique, par exemple, peut échouer ou tronquer des valeurs.

Le verrouillage. C’est le risque le plus sous-estimé. Beaucoup d’opérations de modification de structure posent un verrou sur la table pendant leur exécution. Sur une petite table, c’est imperceptible ; sur une table de plusieurs gigaoctets, le verrou peut durer plusieurs minutes, pendant lesquelles l’application ne peut plus ni lire ni écrire. En production, cela se traduit par des erreurs et des délais côté utilisateurs.

La durée et les ressources. Réécrire une grosse table consomme du processeur, de la mémoire et de l’espace disque, et génère des journaux de transaction volumineux. Sur un environnement répliqué, ces changements doivent en plus se propager à toutes les répliques.

Le bon choix de méthode dépend directement de ces trois facteurs : la taille de la table, la nature de la modification, et surtout la contrainte de disponibilité (peut-on se permettre une interruption, ou faut-il du « zéro coupure » ?).

Méthode 1 : l’ALTER TABLE direct

La méthode la plus simple et la plus connue est la commande ALTER TABLE. Elle permet d’ajouter, modifier ou supprimer une colonne directement.

-- Ajouter une colonne
ALTER TABLE clients ADD COLUMN telephone VARCHAR(20);

-- Supprimer une colonne
ALTER TABLE clients DROP COLUMN telephone;

Quand on ajoute une colonne, les lignes existantes reçoivent une valeur nulle (ou la valeur par défaut si on en précise une). Les données déjà présentes sont préservées.

Avantages : simple, une seule commande, lisible, immédiat sur une petite table.

Inconvénients : sur une grosse table, l’opération peut verrouiller la table et durer longtemps, surtout si elle nécessite une réécriture complète (changement de type, ajout d’une colonne avec valeur par défaut sur d’anciennes versions de SGBD). Toutes les modifications ne sont pas possibles par ce biais selon le système.

Bon à savoir : les SGBD modernes ont beaucoup progressé. MySQL/MariaDB proposent des options ALGORITHM=INPLACE, LOCK=NONE qui permettent, pour certaines opérations, de modifier la structure sans bloquer les lectures et écritures. PostgreSQL, depuis la version 11, ajoute une colonne avec valeur par défaut sans réécrire toute la table, ce qui rend l’opération quasi instantanée. Il vaut donc toujours la peine de vérifier si l’opération visée bénéficie de ces optimisations avant de conclure qu’un ALTER TABLE direct est trop risqué.

Ne sur-employez pas la reconstruction. Par excès de prudence, on choisit parfois une reconstruction complète (méthode 2, ci-dessous) alors qu’un simple ALTER TABLE ... MODIFY aurait largement suffi. Le cas le plus fréquent est le changement de taille d’une colonne. Trois situations se distinguent :

Agrandir une colonne (par exemple passer un VARCHAR(200) à VARCHAR(250), ou augmenter la précision d’un nombre) se fait directement, sans risque et quasi instantanément : aucune donnée existante ne peut être invalidée puisqu’on élargit. Reconstruire la table pour cela serait un gaspillage de temps et un risque inutile.
Réduire une colonne est possible en ALTER direct aussi, mais sous condition : si des valeurs existantes dépassent la nouvelle taille, le SGBD refusera l’opération. Il faut d’abord vérifier (et éventuellement nettoyer) les données concernées.
Changer de type (texte vers numérique, par exemple) est le cas où la reconstruction redevient souvent nécessaire, car la conversion des données existantes ne se fait pas toujours automatiquement.

La règle est donc de bien identifier la nature exacte du changement avant de choisir : un simple agrandissement de colonne ne justifie jamais une reconstruction.

Méthode 2 : la reconstruction par table intermédiaire

Quand l’ALTER TABLE direct n’est pas possible ou trop risqué, la méthode classique consiste à reconstruire la table : on crée une nouvelle table avec la structure voulue, on y copie les données, puis on bascule.

Le principe, en quatre temps :

-- 1. Créer la nouvelle table avec la structure cible
CREATE TABLE clients_new (
    id INTEGER,
    nom VARCHAR(100),
    email VARCHAR(150),
    telephone VARCHAR(20),
    actif SMALLINT DEFAULT 1
);

-- 2. Copier les données de l'ancienne vers la nouvelle
INSERT INTO clients_new (id, nom, email)
SELECT id, nom, email FROM clients;

-- 3. Supprimer l'ancienne table
DROP TABLE clients;

-- 4. Renommer la nouvelle
ALTER TABLE clients_new RENAME TO clients;

Cette approche est très souple : elle permet n’importe quelle transformation de structure (changement de type, réorganisation des colonnes, ajout de contraintes), puisqu’on définit librement la nouvelle table et qu’on maîtrise la copie des données via le SELECT.

Une variante plus prudente : sauvegarder avant de reconstruire. Plutôt que de créer une table au nouveau nom puis de renommer, une approche fréquente en production consiste à sauvegarder la table dans une copie, puis à supprimer et recréer la table sous son nom d’origine, et enfin à réinjecter les données depuis la sauvegarde. Cela garde le nom de table inchangé (pratique quand des vues ou du code y font référence) tout en conservant un filet de sécurité :

-- 1. Sauvegarder la table existante
CREATE TABLE clients_save AS SELECT * FROM clients;

-- 2. Supprimer puis recréer la table avec la nouvelle structure
DROP TABLE clients;
CREATE TABLE clients ( ... nouvelle structure ... );

-- 3. Réinjecter les données depuis la sauvegarde
INSERT INTO clients (id, nom, email)
SELECT id, nom, email FROM clients_save;

-- 4. Une fois la vérification faite, supprimer la sauvegarde
DROP TABLE clients_save;

Attention au piège de la correspondance des colonnes. C’est l’erreur la plus fréquente de cette méthode. Dans un INSERT ... SELECT, si vous n’indiquez pas explicitement la liste des colonnes, les valeurs sont associées par position, et non par nom. Concrètement, la 1re colonne du SELECT alimente la 1re colonne de la table, la 2e la 2e, et ainsi de suite — quels que soient les noms. Conséquence : si l’ordre ou le nombre de colonnes diffère entre la sauvegarde et la nouvelle table, les données atterrissent dans les mauvaises colonnes (ou l’insertion échoue). Le réflexe de sécurité est donc de toujours nommer explicitement les colonnes des deux côtés (dans le INSERT et dans le SELECT), pour rendre la correspondance sans ambiguïté et repérer immédiatement tout décalage.

Un dernier point de vigilance sur les contraintes : si la nouvelle table déclare des colonnes NOT NULL, la réinjection échouera si l’ancienne contenait des valeurs nulles sur ces colonnes. Vérifiez la compatibilité des données avant de lancer la copie.

Avantages : aucune limite sur la transformation possible ; on contrôle exactement comment les données sont converties lors de la copie ; on garde l’ancienne table jusqu’à la bascule, ce qui offre un filet de sécurité.

Inconvénients : elle nécessite temporairement de l’espace disque supplémentaire (les données existent en double le temps de la copie) ; il faut penser à recréer les index, contraintes, clés étrangères et déclencheurs sur la nouvelle table ; et surtout, entre la copie et la bascule, les écritures survenues sur l’ancienne table risquent d’être perdues si l’application continue de tourner. Cette méthode convient donc bien à une fenêtre de maintenance, moins à un service actif en continu.

Méthode 3 : la migration progressive (sans coupure)

Pour les systèmes qui ne peuvent pas se permettre d’interruption, une troisième voie existe : la migration progressive, aussi appelée motif « expand/contract » (étendre puis contracter). L’idée est de décomposer le changement en petites étapes réversibles, chacune compatible avec l’application en cours d’exécution.

Prenons l’exemple du remplacement d’une colonne par une autre : scinder un champ nom_complet en deux colonnes prenom et nom. Voici les quatre étapes, avec les ordres SQL correspondants.

Étape 1 — Étendre. On ajoute les nouvelles colonnes sans toucher à l’ancienne. C’est un simple ALTER TABLE, rapide et non bloquant. L’application continue de fonctionner comme avant, en ignorant ces colonnes encore vides.

ALTER TABLE clients ADD COLUMN prenom VARCHAR(80);
ALTER TABLE clients ADD COLUMN nom VARCHAR(80);

Étape 2 — Remplir. On alimente les nouvelles colonnes à partir de l’ancienne. Sur une grosse table, on procède par lots plutôt qu’en un seul UPDATE massif (qui verrouillerait la table). Ici, on suppose que le prénom précède le nom dans nom_complet :

-- Remplissage par lots (exemple : 10 000 lignes à la fois)
UPDATE clients
SET prenom = SUBSTR(nom_complet, 1, INSTR(nom_complet, ' ') - 1),
    nom    = SUBSTR(nom_complet, INSTR(nom_complet, ' ') + 1)
WHERE prenom IS NULL
  AND ROWNUM <= 10000;
-- (à répéter jusqu'à ce que plus aucune ligne ne soit concernée)

En parallèle, on adapte l’application pour qu’elle écrive désormais dans les deux formats à la fois (l’ancienne colonne et les nouvelles) : c’est la « double écriture ». Ainsi, toute nouvelle donnée reste cohérente pendant la transition. Cette partie relève du code applicatif, pas du SQL.

Étape 3 — Basculer. Une fois toutes les lignes remplies et vérifiées, on fait pointer l’application sur les nouvelles colonnes en lecture. C’est essentiellement un changement applicatif (l’application lit prenom/nom au lieu de nom_complet), pas une opération SQL. On garde encore l’ancienne colonne quelque temps, par sécurité.

Étape 4 — Contracter. Enfin, quand on est certain que plus rien n’utilise l’ancienne colonne, on la supprime :

ALTER TABLE clients DROP COLUMN nom_complet;

Le point à retenir : cette méthode entremêle des étapes SQL (ajout, remplissage, suppression) et des étapes applicatives (double écriture, bascule des lectures). C’est ce qui la rend puissante — aucune interruption — mais aussi plus exigeante que les deux autres.

Avantages : aucune interruption de service ; chaque étape est petite, testable et réversible ; le risque est étalé dans le temps plutôt que concentré sur une opération unique et risquée.

Inconvénients : c’est de loin la méthode la plus complexe et la plus longue à mettre en œuvre ; elle demande de modifier l’application en parallèle (écriture double, puis bascule) ; elle exige de la rigueur pour ne pas laisser le système dans un état intermédiaire incohérent.

Comment choisir

Le tableau suivant résume les critères de choix :

Méthode	Idéale quand…	À éviter si…
ALTER direct	Table petite, ou modification simple bénéficiant des optimisations sans verrou	Grosse table en production avec verrouillage bloquant
Reconstruction	Transformation complexe, fenêtre de maintenance disponible	Service actif en continu, ou disque insuffisant
Migration progressive	Zéro coupure exigé, table critique et volumineuse	Petit changement simple (complexité inutile)

En pratique, la règle est de commencer par la méthode la plus simple qui fonctionne : tester si un ALTER TABLE direct suffit (avec les options non bloquantes de votre SGBD), et ne monter en complexité que si la taille de la table ou la contrainte de disponibilité l’imposent.

Les précautions communes

Quelle que soit la méthode retenue, quelques réflexes s’imposent :

Sauvegardez avant toute chose. Une sauvegarde récente est votre unique garantie en cas de problème. Ne modifiez jamais la structure d’une table de production sans filet.
Testez d’abord ailleurs. Rejouez l’opération sur un environnement de test avec un volume de données représentatif. Une opération instantanée sur 100 lignes peut prendre une heure sur 100 millions.
Utilisez une transaction quand c’est possible. Sur les SGBD qui supportent le DDL transactionnel (comme PostgreSQL), encadrer les opérations dans une transaction permet un retour arrière propre en cas d’erreur.
Rendez vos scripts rejouables. Prévoir la suppression des tables temporaires en début et fin de script évite les erreurs si le script est passé deux fois sur le même environnement.
Planifiez hors des heures de pointe. Même avec les meilleures précautions, une modification de structure se fait de préférence quand l’activité est faible.

En résumé

Modifier la structure d’une table pleine, ce n’est pas choisir une commande, c’est choisir une stratégie adaptée au risque :

L’ALTER TABLE direct pour les cas simples et les petites tables, en profitant des optimisations sans verrou des SGBD modernes.
La reconstruction par table intermédiaire pour les transformations complexes, quand une fenêtre de maintenance est possible.
La migration progressive pour les systèmes critiques exigeant zéro coupure, au prix d’une plus grande complexité.

Dans tous les cas, la sauvegarde préalable et le test sur un volume réaliste ne sont pas optionnels. Le meilleur ingénieur n’est pas celui qui lance l’opération la plus audacieuse, mais celui qui a prévu comment revenir en arrière si elle échoue.