Comment exporter les résultats de la requête MySQL au format CSV sous Linux

Interroger à partir d'un MySQL le shell de base de données est toujours amusant et technique jusqu'à ce que vous ayez besoin d'une sortie de base de données enregistrée quelque part pour un accès et une référence faciles; surtout lorsqu'il s'agit de grands ensembles de données.

L'accès rapide aux données vous évite d'avoir à vous connecter à chaque fois à un serveur MySQL via un shell de terminal pour référencer des sorties spécifiques associées à une requête MySQL. Le CSV (Valeur séparée par des virgules ) est un candidat idéal pour résoudre ces types d'interactions répétitives entre l'utilisateur et la base de données.

Le format de fichier CSV est le mieux adapté à l'enregistrement des sorties MySQL en raison de ses principaux attributs, notamment :

• C'est un format d'enregistrement de données séparées par des virgules largement acceptable.
• Son avantage supplémentaire lisible par l'homme.
• Son importation facile dans n'importe quelle application en raison de sa nature en texte brut.
• Son adaptabilité dans la gestion et l'organisation de grands ensembles de données.

Prérequis

• Le fichier CSV vous sera associé au MySQL les sorties de requête ne doivent pas encore exister car elles seront générées automatiquement lors de l'exécution d'une sortie de requête MySQL ciblée.
• Disposer des privilèges root sur la base de données MySQL et sur le système Linux.

Création d'un exemple de table de base de données avec plusieurs valeurs de ligne

Pour que ce didacticiel soit engageant et mieux compris, une table de base de données avec certaines valeurs doit exister. Pour ce tutoriel, vous pouvez soit être sous MySQL ou MariaDB SGBDR. Depuis MariaDB est un fork open-source de MySQL , ces deux RDBMS font référence à la même implémentation de leurs commandes shell de base de données.

Connectez-vous à votre MySQL base de données en tant qu'utilisateur racine de base de données ou avec des informations d'identification d'utilisateur de base de données existantes.

$ sudo mysql -u root -p

Nous allons créer une nouvelle base de données pour héberger notre nouvelle table de base de données.

MariaDB[(none)]> show databases;
MariaDB[(none)]> create database lst_db;
MariaDB[(none)]> use lst_db;

Ensuite, créez la base de données avec quelques tables comme indiqué.

MariaDB[(none)]>  CREATE TABLE lst_projects(
	project_id INT AUTO_INCREMENT, 
	project_name VARCHAR(100) NOT NULL,
	project_category VARCHAR(100) NOT NULL,
	project_manager VARCHAR(100) NOT NULL,
	start_date DATE,
	end_date DATE,
	PRIMARY KEY(project_id)
);

Remplir la table de la base de données MySQL avec des données

Nous avons vérifié que notre table de base de données MySQL créée existe. Il est temps de le remplir avec quelques données.

MariaDB[(none)]> show tables;
MariaDB[(none)]> INSERT INTO 
	lst_projects(project_name, project_category, project_manager, start_date, end_date)
VALUES
	('Marketing','AI','David Guitar','2021-08-01','2021-12-31'),
            ('Copy writing','AI','Viola Guin','2022-01-01','2022-03-31'),
            ('Modeling','Robotics','Mary Atkins','2023-04-01','2023-07-31'),
            ('API','ML','Duncan Reeves','2024-02-01','2024-06-20'),
	('Sales','ML','Anthony Luigi','2025-05-15','2025-11-20');

Confirmons l'existence de nos lst_projects valeurs de table.

MariaDB[(none)]> SELECT * FROM lst_projects;

Exportation des résultats de la requête MySQL au format CSV

Le répertoire temporaire "/var/tmp" donne à MySQL les privilèges de lecture et d'écriture nécessaires. Nous l'utiliserons pour héberger tous les fichiers CSV générés automatiquement à partir des requêtes MySQL.

Plusieurs conditions déterminent comment nous exportons un résultat de requête MySQL vers un format de fichier CSV.

Exportation de toutes les requêtes MySQL vers CSV

Pour exporter cette requête de base de données "SELECT * FROM lst_projects ; ” dans un fichier CSV, nous l'implémenterions de la manière suivante :

MariaDB[(none)]> SELECT * FROM lst_projects
INTO OUTFILE '/var/tmp/get_all_queries.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Essayons de récupérer le fichier généré :

Exporter des tables MySQL vers CSV avec des en-têtes

Cette approche donne à votre fichier CSV généré un aspect professionnel.

MariaDB[(none)]> (SELECT 'Project Name','Project Category','Project Manager','Start Date','End Date')
UNION 
(SELECT project_name,project_category, project_manager, start_date, end_date
FROM lst_projects
INTO OUTFILE '/var/tmp/included_column_headings.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

Récupérons à nouveau le fichier généré :

Comme indiqué, l'exportation CSV de la requête MySQL est désormais bien organisée avec des en-têtes de colonne.

Traitement des valeurs nulles sur les requêtes MySQL exportées

Ajoutons une colonne qui accepte Null valeurs à notre table de base de données lst_projects .

MariaDB[(none)]> ALTER TABLE lst_projects ADD COLUMN project_status VARCHAR(15) AFTER end_date;

Nous n'insérerons aucune valeur dans cette nouvelle colonne pour nous assurer qu'elle reste vide. La requête MySQL s'exporte avec Null les valeurs sont préenregistrées avec “"N” sur le fichier CSV généré. Pour résoudre ce problème, nous pouvons remplacer le “"N” value avec quelque chose de plus pertinent comme “N/A” .

MariaDB[(none)]> (SELECT 'Project Name','Start Date','End Date','Project Status')
UNION 
(SELECT 
    project_name, start_date, end_date, IFNULL(project_status, 'N/A')
FROM
    lst_projects INTO OUTFILE '/var/tmp/with_null.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n');

Vérifions le fichier CSV généré.

Exporter des tables MySQL vers CSV avec le nom de fichier d'horodatage

Cela crée une routine de gestion plus précise en ce qui concerne le moment où vos fichiers CSV ont été générés.

MariaDB[(none)]> SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '/var/tmp/';
SET @PREFIX = 'lst_projects';
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM lst_projects INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
"  LINES TERMINATED BY '\r\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

Le nom de fichier CSV généré doit maintenant avoir un horodatage.

Sortie de votre MySQL les résultats des requêtes dans un fichier CSV sont un moyen efficace de gérer de grands ensembles de données, car cela vous fait gagner du temps et de l'argent, en particulier lors de la gestion des données pour de grandes organisations.