Le monde de MySQL et MariaDB

Mise à jour massive des enregistrements et colonnes MySQL – du débutant au professionnel

Bases du SQL
目次

1. Introduction

MySQL est utilisé dans de nombreuses applications web et pour la gestion de bases de données, et la mise à jour des données est extrêmement importante pour les opérations quotidiennes et la maintenance des applications. En particulier, pour les systèmes manipulant de gros volumes de données ou lorsque plusieurs enregistrements doivent être mis à jour simultanément, il est nécessaire d’exploiter l’instruction UPDATE de MySQL afin d’opérer efficacement.
Cet article explique en détail comment mettre à jour plusieurs enregistrements et colonnes en masse à l’aide de l’instruction UPDATE de MySQL. Des utilisations simples aux mises à jour conditionnelles plus complexes, nous les présentons dans l’ordre, ce qui en fait une ressource utile pour ceux qui souhaitent réaliser des opérations de mise à jour complexes avec MySQL.

2. Syntaxe de base des instructions UPDATE

L’instruction UPDATE de MySQL est une syntaxe permettant de mettre à jour des données dans une table selon des conditions spécifiques. Tout d’abord, examinons la syntaxe de base de l’instruction UPDATE et comment mettre à jour un seul enregistrement ou une seule colonne.

Syntaxe de base

La syntaxe de base de l’instruction UPDATE de MySQL est la suivante.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name : indique le nom de la table à mettre à jour.
  • clause SET : indique les colonnes à mettre à jour et leurs nouvelles valeurs. Lors de la mise à jour de plusieurs colonnes en même temps, séparez les paires colonne‑valeur par des virgules.
  • clause WHERE : indique la condition pour les enregistrements à mettre à jour. Si vous omettez la condition, tous les enregistrements de la table seront mis à jour, donc soyez prudent.

Exemple de mise à jour d’un seul enregistrement ou d’une seule colonne

À titre d’exemple d’utilisation de base, voyons comment mettre à jour un seul enregistrement ou une seule colonne.

UPDATE users
SET name = 'Tanaka'
WHERE id = 1;

Cette instruction SQL met à jour la colonne name de l’enregistrement dont l’id vaut 1 dans la table users en le remplaçant par « Tanaka ». En spécifiant une clause WHERE, vous ne mettez à jour que l’enregistrement spécifique.

3. Mise à jour en masse de plusieurs enregistrements

Lorsque vous mettez à jour plusieurs enregistrements en masse, vous pouvez spécifier plusieurs conditions dans la clause WHERE. Par exemple, vous pouvez utiliser les clauses IN ou OR pour définir plusieurs conditions et mettre à jour efficacement plusieurs enregistrements.

Mise à jour de plusieurs enregistrements avec la clause IN

L’utilisation de la clause IN vous permet de mettre à jour les enregistrements qui correspondent à une liste précise de valeurs.

UPDATE users
SET status = 'active'
WHERE id IN (1, 3, 5, 7);

Dans cette instruction SQL, au sein de la table users, les enregistrements dont l’id vaut 1, 3, 5 ou 7 voient leur colonne status mise à jour à « active ». L’utilisation de la clause IN vous permet de mettre à jour en masse plusieurs enregistrements répondant à la condition.

Spécification de plusieurs conditions avec la clause OR

La clause OR vous permet de combiner et de spécifier plusieurs conditions.

UPDATE users
SET status = 'inactive'
WHERE id = 2 OR id = 4 OR id = 6;

Dans cette instruction SQL, la colonne status des enregistrements dont l’id vaut 2, 4 ou 6 est mise à jour à « inactive ». L’utilisation de la clause OR vous permet de mettre à jour les enregistrements qui correspondent à plusieurs conditions simultanément.

4. Mise à jour simultanée de plusieurs colonnes

Dans les instructions UPDATE de MySQL, vous pouvez mettre à jour plusieurs colonnes en même temps. Cela est utile lorsque vous devez modifier plusieurs informations d’un coup tout en conservant la cohérence des données.

Exemple de mise à jour de plusieurs colonnes

Lorsque vous mettez à jour plusieurs colonnes simultanément, indiquez les noms de colonnes et leurs valeurs dans la clause SET, séparés par des virgules.

UPDATE products
SET price = price * 1.1, stock = stock - 1
WHERE id = 10;

Dans cette instruction SQL, pour l’enregistrement de la table products dont l’id vaut 10, la colonne price est augmentée de 10 % et la colonne stock est diminuée de 1. En spécifiant plusieurs colonnes dans la clause SET, vous pouvez mettre à jour efficacement plusieurs informations.

5. Mise à jour conditionnelle avec les instructions CASE

Dans les instructions UPDATE de MySQL, vous pouvez utiliser des expressions CASE pour attribuer des valeurs différentes selon des conditions. Cela vous permet de modifier de façon flexible le contenu de la mise à jour en fonction de multiples conditions, rendant les opérations de mise à jour complexes simples à exécuter.

Syntaxe de base utilisant les expressions CASE

La syntaxe de base d’une instruction UPDATE utilisant une expression CASE est la suivante.

UPDATE table_name
SET column_name = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE default_value
END
WHERE condition;
  • Nom de colonne : Spécifie la colonne que vous souhaitez mettre à jour.
  • Condition : Spécifiez la condition dans la clause WHEN, et définissez la valeur à appliquer dans la clause THEN lorsqu’elle correspond.
  • Valeur par défaut : La valeur définie lorsque aucune des conditions ne correspond (facultatif).

Exemple pratique avec CASE

Ici, examinons un exemple qui met à jour les salaires dans la table employees en fonction du poste.

UPDATE employees
SET salary = CASE
    WHEN position = 'Manager' THEN salary * 1.1
    WHEN position = 'Developer' THEN salary * 1.05
    WHEN position = 'Intern' THEN salary * 1.02
    ELSE salary
END;

Dans cette instruction SQL, pour chaque enregistrement de la table employees, la colonne salary est mise à jour en fonction de la valeur de la colonne position.

Mise à jour conditionnelle de plusieurs colonnes

Les expressions CASE peuvent également être appliquées à plusieurs colonnes. Dans l’exemple ci-dessous, les colonnes salary et bonus de la table employees sont mises à jour avec des valeurs différentes en fonction du poste et de l’ancienneté.

UPDATE employees
SET 
    salary = CASE
        WHEN position = 'Manager' AND years_of_service >= 5 THEN salary * 1.15
        WHEN position = 'Developer' AND years_of_service >= 3 THEN salary * 1.1
        ELSE salary
    END,
    bonus = CASE
        WHEN position = 'Manager' THEN bonus + 1000
        WHEN position = 'Developer' THEN bonus + 500
        ELSE bonus
    END;

Dans cette instruction SQL, les salaires et les primes sont mis à jour conditionnellement en une seule fois en fonction du poste et de l’ancienneté. L’utilisation d’expressions CASE permet des mises à jour flexibles basées sur plusieurs conditions.

6. Mise à jour de plusieurs tables avec JOIN

Dans MySQL, vous pouvez utiliser une clause JOIN pour combiner plusieurs tables et mettre à jour des enregistrements selon des conditions spécifiques. Cela vous permet de référencer des données provenant de différentes tables tout en mettant à jour une table, ce qui rend possible une manipulation de données complexe.

Syntaxe de base des instructions UPDATE avec JOIN

Lors de la mise à jour de plusieurs tables avec JOIN, la syntaxe de base est la suivante.

UPDATE tableA
JOIN tableB ON tableA.column = tableB.column
SET tableA.updated_column = new_value
WHERE condition;
  • Table A et Table B : Spécifiez la table cible pour la mise à jour (Table A) et la table de référence (Table B).
  • Clause ON : Spécifiez la condition de JOIN et définissez la colonne sur laquelle les tables seront jointes.
  • Clause SET : Spécifiez la colonne à mettre à jour et sa nouvelle valeur.
  • Clause WHERE : Spécifiez la condition de mise à jour, ne mettant à jour que les enregistrements correspondants.

Exemple pratique avec JOIN

Par exemple, examinons un exemple qui joint la table orders à la table customers pour mettre à jour le statut des commandes liées à un client spécifique.

UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.status = 'Shipped'
WHERE customers.vip_status = 'Yes';

Dans cette instruction SQL, la colonne status des enregistrements de la table orders liés aux clients dont le vip_status dans la table customers est « Yes » est mise à jour à « Shipped ». En utilisant une clause JOIN, vous pouvez mettre à jour en vous basant sur les informations d’autres tables.

Mise à jour JOIN avec plusieurs conditions

Vous pouvez combiner plusieurs conditions pour spécifier des critères plus granulaires. Dans l’exemple ci-dessous, le statut des commandes liées à un client spécifique est modifié en masse en fonction des conditions.

UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.status = CASE
    WHEN customers.vip_status = 'Yes' THEN 'Priority'
    WHEN customers.vip_status = 'No' AND orders.amount > 10000 THEN 'Review'
    ELSE orders.status
END
WHERE orders.date >= '2024-01-01';

En tirant parti de JOIN, vous pouvez effectuer des mises à jour de données flexibles qui s’adaptent aux conditions.

7. Performances et considérations

Lors de l’utilisation d’instructions MySQL UPDATE pour modifier plusieurs lignes ou colonnes en masse, en particulier avec de grands ensembles de données, les performances doivent être prises en compte. Vous trouverez ci‑dessous les points clés et les précautions à connaître pour améliorer les performances de mise à jour et garantir l’intégrité des données.

Conseils d’optimisation des performances

Exploiter les index

Lorsque vous mettez à jour des enregistrements en fonction de conditions spécifiques avec une clause WHERE, ajouter un index sur les colonnes concernées améliore la vitesse de recherche. Les index renforcent les performances des requêtes, permettant un traitement efficace même lorsque la mise à jour porte sur un volume important de données.

CREATE INDEX idx_customer_id ON orders(customer_id);

Cependant, un excès d’index peut réellement dégrader les performances, il est donc recommandé d’indexer uniquement les colonnes essentielles.

Réduire la charge avec le traitement par lots

Mettre à jour un grand nombre de lignes d’un coup peut imposer une charge importante au serveur de base de données et ralentir les temps de réponse. Pour des mises à jour massives, l’utilisation du traitement par lots (exécution de l’opération en plusieurs passes) peut alléger la charge du serveur.

UPDATE orders
SET status = 'Processed'
WHERE status = 'Pending'
LIMIT 1000;

En combinant cela avec un script, vous pouvez exécuter de façon répétée des mises à jour par lots pour une modification efficace des données.

Utilisation des transactions

Lorsque plusieurs instructions UPDATE sont liées ou que l’intégrité des données est cruciale, l’utilisation d’une transaction garantit la cohérence. Avec une transaction, toute erreur survenant pendant la mise à jour peut annuler toutes les modifications.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Gestion des verrous

L’exécution d’un UPDATE peut entraîner des verrous de table. Cela est particulièrement important lorsque plusieurs utilisateurs accèdent simultanément à la même table. Par exemple, l’utilisation de verrous au niveau des lignes permet à d’autres utilisateurs de travailler sur d’autres lignes en même temps, favorisant le traitement parallèle. Éviter les verrous de table complets améliore la réactivité de la base de données.

8. Résumé

Dans cet article, nous avons fourni une explication détaillée des méthodes efficaces pour mettre à jour plusieurs enregistrements et colonnes à l’aide de l’instruction UPDATE de MySQL, couvrant tout, des usages de base aux techniques avancées. Lors de la mise à jour de multiples données dans MySQL, il faut tenir compte du volume de données, de la vitesse de traitement et de l’intégrité des données.

Récapitulatif des points principaux

  1. Bases des instructions UPDATE
  • Comprendre la syntaxe de base de l’instruction UPDATE vous permet de mettre à jour en toute sécurité des colonnes ou des enregistrements individuels.
  1. Mise à jour en masse de plusieurs enregistrements
  • Nous avons montré comment mettre à jour efficacement plusieurs enregistrements répondant à des conditions spécifiques en utilisant les clauses WHERE, IN et OR.
  1. Mise à jour simultanée de plusieurs colonnes
  • En utilisant la clause SET, vous pouvez mettre à jour plusieurs colonnes d’un même enregistrement en une seule fois, ce qui permet des mises à jour efficaces tout en maintenant la cohérence des données.
  1. Mises à jour conditionnelles avec les instructions CASE
  • Exploiter les instructions CASE vous permet d’effectuer différentes mises à jour dans une même opération selon des conditions, simplifiant ainsi une logique de mise à jour complexe.
  1. Mise à jour de plusieurs tables avec JOIN
  • En faisant référence aux données d’autres tables lors de la mise à jour d’enregistrements spécifiques, vous pouvez améliorer l’intégrité globale de la base de données.
  1. Performances et considérations
  • Nous avons appris comment réaliser des mises à jour de données efficaces et sûres en tirant parti des index, du traitement par lots et des transactions. Pensez également à la gestion des verrous pour optimiser les performances de la base de données.

Conclusion

Mettre à jour efficacement les données dans MySQL est une compétence particulièrement importante en administration de bases de données. Maîtriser l’instruction UPDATE peut augmenter l’efficacité opérationnelle et optimiser les performances globales du système. Veillez à appliquer les techniques présentées dans cet article à vos tâches et projets réels.