MySQL dispose d'une fonctionnalité permettant d'exporter une table dans le fichier CSV. Un format de fichier CSV est une valeur séparée par des virgules que nous utilisons pour échanger des données entre diverses applications telles que Microsoft Excel, Goole Docs et Open Office. Il est utile d'avoir les données MySQL au format de fichier CSV qui nous permet de les analyser et de les formater comme nous le souhaitons. Il s'agit d'un fichier texte brut qui nous aide à exporter des données très facilement.
MySQL fournit un moyen simple d'exporter n'importe quelle table dans des fichiers CSV résidant sur le serveur de base de données. Nous devons nous assurer des choses suivantes avant d'exporter des données MySQL :
- Le processus du serveur MySQL dispose d'un accès en lecture/écriture au dossier spécifié (cible), qui contient le fichier CSV.
- Le fichier CSV spécifié ne doit pas exister dans le système.
Pour exporter le tableau dans un fichier CSV, nous utiliserons le SÉLECTIONNER DANS....OUTFILE déclaration. Cette déclaration est un compliment du CHARGER LES DONNÉES commande, qui est utilisée pour écrire des données à partir d’une table, puis les exporter dans un format de fichier spécifié sur l’hôte du serveur. Il s'agit de garantir que nous disposons d'un privilège de fichier pour utiliser cette syntaxe.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Nous pouvons également utiliser cette syntaxe avec une instruction de valeurs pour exporter des données directement dans un fichier. La déclaration suivante l’explique plus clairement :
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Si nous voulons exporter toutes les colonnes du tableau , nous utiliserons la syntaxe ci-dessous. Avec cette instruction, l'ordre et le nombre de lignes seront contrôlés par le COMMANDÉ PAR et LIMITE clause.
numéro java à chaîne
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
De ce qui précède,
LIGNES TERMINÉES PAR ',' : Il est utilisé pour indiquer les lignes de lignes d'un fichier qui se terminent par un opérateur virgule. Chaque ligne contient les données de chaque colonne du fichier.
CHAMPS FERMÉS PAR ''' : Il permet de préciser le champ du fichier entouré de guillemets doubles. Il empêche les valeurs contenant des séparateurs par des virgules. Si les valeurs sont contenues entre guillemets doubles, il ne reconnaît pas la virgule comme séparateur.
Emplacement de stockage du fichier exporté
L'emplacement de stockage de chaque fichier exporté dans MySQL est stocké dans la variable par défaut secure_file_priv . Nous pouvons exécuter la commande ci-dessous pour obtenir le chemin par défaut d'un fichier exporté.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Après exécution, il donnera le résultat suivant où l'on pourra voir ce chemin : C:/ProgramData/MySQL/MySQL Server 8.0/Téléchargements/ comme emplacement de fichier par défaut. Ce chemin sera utilisé lors de l’exécution d’une commande d’export.
np.argmax
Si nous souhaitons modifier l'emplacement d'exportation par défaut du fichier CSV spécifié dans le secure_file_priv variable, nous devons éditer la mon.ini fichier de configuration. Sur la plateforme Windows, ce fichier se trouve sur ce chemin : C:ProgramDataMySQLMySQL Server X.Y .
Si nous voulons exporter des données MySQL, nous devons d'abord créer un base de données avec au moins un tableau . Nous allons utiliser ce tableau comme exemple.
Nous pouvons créer un base de données et table en exécutant le code ci-dessous dans les éditeurs que nous utilisons :
CREATE DATABASE testdb; USE testdb; CREATE TABLE employee_detail ( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL, Phone varchar(15) DEFAULT NULL, City varchar(25) DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY unique_email (Email), UNIQUE KEY index_name_phone (Name,Phone) ) INSERT INTO employee_detail ( Id, Name, Email, Phone, City) VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'), (2, 'Suzi', '[email protected]', '70679834522', 'California'), (3, 'Joseph', '[email protected]', '09896765374', 'Alaska'), (4, 'Alex', '[email protected]', '97335737548', 'Los Angeles'), (5, 'Mark', '[email protected]', '78765645643', 'Washington'), (6, 'Stephen', '[email protected]', '986345793248', 'New York');
Si nous exécutons le SÉLECTIONNER déclaration, nous verrons le résultat suivant :
Exportez les données MySQL au format CSV à l'aide de l'instruction SELECT INTO ... OUTFILE
Pour exporter les données de la table dans un fichier CSV, nous devons exécuter la requête comme suit :
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Nous obtiendrons le résultat suivant, où nous pouvons voir que six lignes sont affectées. C'est parce que le tableau spécifié ne contient que six lignes.
Si nous exécutons à nouveau la même instruction, MySQL produit un message d'erreur visible dans la sortie ci-dessous :
Le message d'erreur nous indique que le nom de fichier spécifié existe déjà à l'emplacement spécifié. Ainsi, si nous exportons le nouveau fichier CSV avec le même nom et le même emplacement, il ne pourra pas être créé. Nous pouvons résoudre ce problème en supprimant le fichier existant à l'emplacement spécifié ou en renommant le nom du fichier pour le créer au même endroit.
Nous pouvons vérifier ou non le fichier CSV créé à l'emplacement spécifié en accédant à un chemin donné comme suit :
Lorsque nous ouvrirons ce fichier, il ressemblera à l'image ci-dessous :
Dans l’image, on peut voir que les champs numériques sont entre guillemets. Nous pouvons changer ce style en ajoutant Clause FACULTATIVEMENT avant ENCLOSED BY :
différence de date Excel
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Exportation de données avec en-tête de colonne
Parfois, nous souhaitons exporter des données avec des en-têtes de colonnes qui rendent le fichier pratique. Le fichier exporté est plus compréhensible si la première ligne du fichier CSV contient les en-têtes de colonnes. Nous pouvons ajouter les en-têtes de colonnes en utilisant le UNION TOUS déclaration comme suit :
compte distinct
SELECT 'Id', 'Name', 'Email', 'Phone', 'City' UNION ALL SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ';' ENCLOSED BY ''' ESCAPED BY ''' LINES TERMINATED BY ' ';
Dans cette requête, nous pouvons voir que nous avons ajouté un en-tête pour chaque nom de colonne. Nous pouvons vérifier le résultat en accédant à l'URL spécifiée où la première ligne contient l'en-tête de chaque colonne :
Exporter la table MySQL au format CSV
MySQL OUTFILE nous permet également d'exporter la table sans spécifier de nom de colonne. Nous pouvons utiliser la syntaxe ci-dessous pour exporter le tableau dans un format de fichier CSV :
TABLE employee_detail ORDER BY City LIMIT 1000 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Si nous exécutons l'instruction ci-dessus, notre outil de ligne de commande produit le résultat suivant. Cela signifie que la table spécifiée contient six lignes, exportées dans employé_backup.csv déposer.
Gestion des valeurs nulles
Parfois, les champs du jeu de résultats ont des valeurs NULL, alors le fichier cible (type de fichier exporté) contiendra N au lieu de NULL. Nous pouvons résoudre ce problème en remplaçant la valeur NULL par 'sans objet (N/A)' en utilisant le IFNUL fonction. La déclaration ci-dessous l'explique plus clairement :
SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Exporter une table au format CSV à l'aide de MySQL Workbench
Si nous ne souhaitons pas accéder au serveur de base de données pour exporter le fichier CSV, MySQL propose un autre moyen, c'est-à-dire en utilisant MySQL Workbench. Workbench est un outil GUI permettant de travailler avec la base de données MySQL sans utiliser d'outil de ligne de commande. Il nous permet d'exporter le jeu de résultats d'une instruction au format CSV dans notre système local. Pour ce faire, nous devons suivre les étapes ci-dessous :
- Exécutez l'instruction/requête et obtenez son jeu de résultats.
- Ensuite, dans le panneau des résultats, cliquez sur 'exporter le jeu d'enregistrements vers un fichier externe' option. Le jeu d'enregistrements est utilisé pour le jeu de résultats.
- Enfin, une nouvelle boîte de dialogue s'affichera. Ici, nous devons fournir un nom de fichier et son format. Après avoir rempli les détails, cliquez sur le Sauvegarder bouton. L'image suivante l'explique plus clairement :
Maintenant, nous pouvons vérifier le résultat en accédant au chemin spécifié.