Insérer rapidement des lignes en masse avec MySQL

Plongeons dans le monde de l'insertion de données en masse dans MySQL. Imaginez que vous êtes un développeur de logiciels construisant une toute nouvelle application. Vous avez une fonctionnalité fantastique qui repose sur un ensemble de données massif. Mais avant de le lancer dans le monde réel, vous devez tester comment votre système gère une grande quantité de données.

C'est là que l'insertion de données en masse devient utile. C'est comme une méthode turbo pour peupler votre base de données avec beaucoup d'informations rapidement.

Vous pouvez simuler des scénarios du monde réel en insérant des milliers, voire des millions d'enregistrements pour voir comment votre base de données et votre application se comportent sous pression.

Les insertions en masse sont essentielles pour évaluer les performances de votre base de données et de votre application, vous permettant de comparer différentes configurations et d'optimiser la vitesse.

Examinons quelques exemples de scénarios où vous pourriez utiliser des insertions en masse pour des tests :

Vous pourriez utiliser une insertion en masse pour ajouter des utilisateurs, des produits, des commandes ou même des avis clients à votre base de données en une seule fois. C'est beaucoup plus rapide que d'ajouter chaque commande individuellement. Cela vous permet de tester comment votre application gère de grandes quantités de données.

Vous devez peupler votre base de données avec un grand nombre de produits pour tester la fonctionnalité de recherche et de filtrage.

Donc, si vous travaillez avec un système qui gère de grands volumes de données, les insertions en masse sont un outil puissant à avoir dans votre boîte à outils.

Dans la prochaine partie, nous explorerons quelques exemples spécifiques de la façon dont l'insertion en masse peut être utilisée pour tester votre logiciel.

Pour générer 10 000 lignes de données pour votre table wp_postmeta dans une instruction INSERT unique :

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

 SELECT

  nums.n,

  CONCAT('Ten Thousand Key ',  CAST(nums.n AS CHAR)),

  CONCAT('Ten Thousand Value ',  CAST(nums.n AS CHAR))

 FROM (

  SELECT 

   d1.N + d2.N * 10 + d3.N * 100 + d4.N * 1000 + 1 AS n

  FROM

   (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1

  ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2

  ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3

  ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4

  ORDER BY n

 ) nums;

Décomposons cela étape par étape :

• Sous-requête (nums) : Cette sous-requête est responsable de la génération de la séquence de nombres.

  • Elle utilise une instruction SELECT pour combiner quatre sous-requêtes identiques (étiquetées d1, d2, d3, et d4) en utilisant UNION ALL.
  • Chaque sous-requête contient les nombres de 0 à 9.
  • L'expression d1.N + d2.N * 10 + d3.N * 100 + d4.N * 1000 + 1 AS n combine les valeurs des quatre sous-requêtes pour créer une séquence de nombres de 1 à 10 000.
  • ORDER BY n garantit que les nombres sont générés dans l'ordre croissant.

• Requête principale : C'est l'instruction INSERT INTO.

  • Elle utilise l'instruction SELECT pour spécifier les données à insérer.
  • post_id : La variable nums.n (qui contient les nombres générés) est utilisée directement comme post_id.
  • meta_key : L'expression CONCAT('Clé de Dix Mille ', CAST(nums.n AS CHAR)) génère une clé unique pour chaque ligne.
  • meta_value : L'expression CONCAT('Valeur de Dix Mille ', CAST(nums.n AS CHAR)) crée une valeur unique pour chaque ligne.

Données produites : La requête insérera 10 000 lignes dans la table wp_postmeta. Chaque ligne aura :

• post_id : Un nombre de 1 à 10 000.
• meta_key : Une clé unique comme "Clé de Dix Mille 1", "Clé de Dix Mille 2", etc.
• meta_value : Une valeur unique comme "Valeur de Dix Mille 1", "Valeur de Dix Mille 2", etc.

Cette requête MySQL peut être utilisée pour des entrées de dix mille lignes, mais pour des entrées de données plus importantes, il est nécessaire d'avancer dans l'approche de génération de données.

La requête MySQL suivante est une manière astucieuse de générer un million de lignes de données pour votre table wp_postmeta en utilisant des variables définies par l'utilisateur et une série d'opérations CROSS JOIN.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

SELECT

 (@rn := @rn + 1) as rn,

 CONCAT('One Million Key ',  CAST(@rn AS CHAR)),

 CONCAT('One Million Value ',  CAST(@rn AS CHAR))

FROM

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d5 CROSS JOIN 

 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d6 CROSS JOIN

 (SELECT @rn := 0) params

Décomposons cette requête MySQL étendue :

• Variable définie par l'utilisateur (@rn) :

  • La requête introduit une variable définie par l'utilisateur appelée @rn. Cette variable est utilisée pour suivre le numéro de ligne pendant que nous générons des données.
  • Initialisation : La variable est initialisée à 0 dans la dernière instruction SELECT : (SELECT @rn := 0) params.

• CROSS JOIN :

  • La requête utilise six opérations CROSS JOIN pour générer un grand nombre de lignes.
  • Chaque CROSS JOIN combine les résultats de deux tables. Dans ce cas, nous utilisons six sous-requêtes identiques (étiquetées d1 à d6) contenant les nombres de 0 à 9.
  • Chaque CROSS JOIN multiplie le nombre de lignes par 10. Puisque nous avons six opérations CROSS JOIN, le nombre total de lignes générées est de 10 * 10 * 10 * 10 * 10 * 10 = 1 000 000.

• Instruction SELECT :

  • L'instruction SELECT récupère les données suivantes pour chaque ligne :
    • (@rn := @rn + 1) as rn : Cette expression incrémente la variable @rn de 1 pour chaque ligne, créant effectivement un numéro de ligne. La clause as rn donne au numéro de ligne généré l'alias rn.
    • CONCAT('Clé d'un Million ', CAST(@rn AS CHAR)) : Cette expression génère une clé unique pour chaque ligne. Elle combine la chaîne "Clé d'un Million " avec le numéro de ligne actuel (@rn) converti en caractère.
    • CONCAT('Valeur d'un Million ', CAST(@rn AS CHAR)) : Cette expression génère une valeur unique pour chaque ligne. Elle combine la chaîne "Valeur d'un Million " avec le numéro de ligne actuel (@rn) converti en caractère.

Données produites :

• La requête insérera un million de lignes dans la table wp_postmeta. Chaque ligne aura :
  • post_id : Un numéro de ligne de 1 à 1 000 000.
  • meta_key : Une clé unique comme "Clé d'un Million 1", "Clé d'un Million 2", etc.
  • meta_value : Une valeur unique comme "Valeur d'un Million 1", "Valeur d'un Million 2", etc.

Points clés :

• Les variables définies par l'utilisateur sont des outils puissants pour manipuler les données dans une requête.
• CROSS JOIN est un excellent moyen de générer un grand nombre de lignes en combinant plusieurs tables ou sous-requêtes.
• Cette méthode est plus efficace que l'utilisation d'opérations UNION ALL imbriquées pour générer un grand nombre de lignes.

Cette requête démontre une approche intelligente et efficace pour générer une quantité massive de données de test pour votre base de données.

Mots-clés : Insertion en masse MySQL, Insertion de données volumineuses MySQL, Insérer plusieurs lignes MySQL, Meilleures pratiques d'insertion en masse MySQL, Générer des données de test MySQL, Insérer de nombreuses lignes MySQL efficacement, CROSS JOIN MySQL pour la génération de données, Exemples d'insertion en masse MySQL, Insertion par lots dans MySQL, Techniques de génération de données MySQL