Interrogazione da un MySQL la shell del database è sempre divertente e tecnologica fino a quando non hai bisogno di un output del database salvato da qualche parte per un facile accesso e riferimento; soprattutto quando si ha a che fare con set di dati di grandi dimensioni.
L'accesso rapido ai dati ti evita di dover accedere ogni volta a un server MySQL tramite una shell del terminale per fare riferimento a specifici output associati a query MySQL. Il CSV (Valore separato da virgole ) è un candidato ideale per risolvere questi tipi di interazioni ripetitive tra utente e database.
Il formato di file CSV è più adatto per salvare gli output di MySQL grazie ai suoi attributi importanti che includono:
- È un formato di salvataggio dati separato da virgole ampiamente accettabile.
- Il suo vantaggio aggiuntivo leggibile dall'uomo.
- È facile importare in qualsiasi applicazione grazie alla sua natura di testo normale.
- La sua adattabilità nella gestione e nell'organizzazione di grandi set di dati.
Prerequisiti
- Il file CSV che verrà associato a MySQL gli output di query non dovrebbero ancora esistere poiché verranno generati automaticamente durante l'esecuzione di un output di query MySQL mirato.
- Disporre dei privilegi di root sia sul database MySQL che sul sistema Linux.
Creazione di una tabella di database di esempio con più valori di riga
Affinché questo tutorial sia coinvolgente e comprensibile, è necessario che esista una tabella di database con alcuni valori. Per questo tutorial, puoi essere in MySQL o MariaDB RDBMS. Da MariaDB è un fork open source di MySQL , questi due RDBMS fare riferimento alla stessa implementazione dei loro comandi della shell del database.
Accedi al tuo MySQL database come utente DB root o con una credenziale utente del database esistente.
$ sudo mysql -u root -p
Creeremo un nuovo database per ospitare la nostra nuova tabella del database.
MariaDB[(none)]> show databases; MariaDB[(none)]> create database lst_db; MariaDB[(none)]> use lst_db;
Quindi, crea il database con alcune tabelle come mostrato.
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) );
Popolare la tabella del database MySQL con i dati
Abbiamo verificato che la nostra tabella di database MySQL creata esiste. È ora di popolarlo con alcuni dati.
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');
Confermiamo l'esistenza dei nostri lst_projects valori della tabella.
MariaDB[(none)]> SELECT * FROM lst_projects;
Esportazione dei risultati delle query MySQL in formato CSV
La directory temporanea “/var/tmp” fornisce a MySQL i privilegi di lettura e scrittura necessari. Lo useremo per ospitare tutti i file CSV generati automaticamente dalle query MySQL.
Diverse condizioni determinano il modo in cui esportiamo il risultato di una query MySQL in un formato di file CSV.
Esportazione di tutte le query MySQL in CSV
Per esportare questa query db "SELECT * FROM lst_projects; ” in un file CSV, lo implementeremo nel modo seguente:
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';
Proviamo a recuperare il file generato:
Esporta tabelle MySQL in CSV con intestazioni
Questo approccio conferisce al file CSV generato un aspetto professionale.
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');
Recuperiamo nuovamente il file generato:
Come notato, l'esportazione CSV delle query MySQL è ora ben organizzata con intestazioni di colonna.
Gestire i valori Null su query MySQL esportate
Aggiungiamo una colonna che accetti Null valori nella nostra tabella del database lst_projects .
MariaDB[(none)]> ALTER TABLE lst_projects ADD COLUMN project_status VARCHAR(15) AFTER end_date;
Non inseriremo alcun valore in questa nuova colonna per assicurarci che rimanga vuota. Le query MySQL vengono esportate con Null i valori sono preregistrati con “"N” sul file CSV generato. Per risolvere questo problema, possiamo sostituire il “"N” valore con qualcosa di più riconoscibile come “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');
Controlliamo il file CSV generato.
Esporta tabelle MySQL in CSV con nome file timestamp
Crea una routine di gestione più accurata in termini di quando sono stati generati i tuoi file CSV.
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;
Il nome del file CSV generato dovrebbe ora avere un timestamp.
Output del tuo MySQL risultati delle query in un file CSV è un modo efficiente per gestire grandi set di dati in quanto consente di risparmiare tempo e denaro, soprattutto durante la gestione dei dati per grandi organizzazioni.