MySQL ha una funzionalità per esportare una tabella nel file CSV. Un formato file CSV è un valore separato da virgole che utilizziamo per scambiare dati tra varie applicazioni come Microsoft Excel, Goole Docs e Open Office. È utile avere i dati MySQL in formato file CSV che ci permette di analizzarli e formattarli nel modo che vogliamo. È un file di testo in chiaro che ci aiuta a esportare i dati molto facilmente.
MySQL fornisce un modo semplice per esportare qualsiasi tabella in file CSV che risiedono nel server del database. Dobbiamo garantire quanto segue prima di esportare i dati MySQL:
- Il processo del server MySQL ha accesso in lettura/scrittura alla cartella specificata (di destinazione), che contiene il file CSV.
- Il file CSV specificato non dovrebbe esistere nel sistema.
Per esportare la tabella in un file CSV, utilizzeremo il file SELEZIONA IN....OUTFILE dichiarazione. Questa affermazione è un complimento del CARICAMENTO DATI comando, che viene utilizzato per scrivere i dati da una tabella e quindi esportarli in un formato di file specificato sull'host del server. Serve per garantire di avere il privilegio sui file per utilizzare questa sintassi.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Possiamo anche usare questa sintassi con un'istruzione value per esportare i dati direttamente in un file. La seguente dichiarazione lo spiega più chiaramente:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Se vogliamo esportare tutte le colonne della tabella , utilizzeremo la sintassi seguente. Con questa istruzione, l'ordine e il numero di righe saranno controllati da ORDINATO DA E LIMITE clausola.
programma principale in Java
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Dall'alto,
LINEE TERMINATE DA ',' : Viene utilizzato per indicare le righe di righe in un file che terminano con un operatore virgola. Ogni riga contiene i dati di ciascuna colonna nel file.
CAMPI racchiusi da ''' : Serve per specificare il campo del file racchiuso tra virgolette doppie. Impedisce i valori che contengono separatori virgola. Se i valori sono racchiusi tra virgolette doppie, non riconosce la virgola come separatore.
Posizione di archiviazione del file esportato
La posizione di archiviazione di ogni file esportato in MySQL è archiviata nella variabile predefinita secure_file_priv . Possiamo eseguire il comando seguente per ottenere il percorso predefinito di un file esportato.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Dopo l'esecuzione, darà il risultato come segue dove possiamo vedere questo percorso: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ come percorso file predefinito. Questo percorso verrà utilizzato al momento dell'esecuzione di un comando di esportazione.
Se desideriamo modificare il percorso di esportazione predefinito del file CSV specificato nel file secure_file_priv variabile, dobbiamo modificare la variabile mio.ini file di configurazione. Sulla piattaforma Windows, questo file si trova in questo percorso: C:ProgramDataMySQLMySQL Server X.Y .
Se vogliamo esportare dati MySQL, per prima cosa dobbiamo creare un file Banca dati con almeno uno tavolo . Utilizzeremo questa tabella come esempio.
ricorsione Java
Possiamo creare un database e tabella eseguendo il codice seguente negli editor che stiamo utilizzando:
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');
Se eseguiamo il SELEZIONARE istruzione, vedremo il seguente output:
Esporta i dati MySQL in formato CSV utilizzando l'istruzione SELECT INTO ... OUTFILE
Per esportare i dati della tabella in un file CSV, dobbiamo eseguire la query come segue:
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 ' ';
Otterremo il seguente output, dove possiamo vedere che sono interessate sei righe. È perché la tabella specificata contiene solo sei righe.
cos'è la rom
Se eseguiamo nuovamente la stessa istruzione, MySQL produce un messaggio di errore che può essere visualizzato nell'output seguente:
Il messaggio di errore ci informa che il nome file specificato esiste già nella posizione specificata. Pertanto, se esportiamo il nuovo file CSV con lo stesso nome e percorso, non sarà possibile crearlo. Possiamo risolvere questo problema eliminando il file esistente nella posizione specificata o rinominando il nome del file per crearlo nello stesso posto.
Possiamo verificare o meno il file CSV creato nella posizione specificata navigando verso un determinato percorso come segue:
Quando apriamo questo file, apparirà come nell'immagine qui sotto:
Nell'immagine possiamo vedere che i campi numerici sono tra virgolette. Possiamo cambiare questo stile aggiungendo OPTIONALMENTE clausola prima di ENCLOSED BY :
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 ' ';
Esportazione dei dati con intestazione di colonna
A volte vogliamo esportare i dati insieme alle intestazioni di colonna che rendono il file conveniente. Il file esportato è più comprensibile se la prima riga del file CSV contiene le intestazioni delle colonne. Possiamo aggiungere le intestazioni delle colonne utilizzando il comando UNIONE TUTTI dichiarazione come segue:
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 ' ';
In questa query, possiamo vedere che abbiamo aggiunto un'intestazione per ogni nome di colonna. Possiamo verificare l'output accedendo all'URL specificato dove la prima riga contiene l'intestazione di ciascuna colonna:
Esporta tabella MySQL in formato CSV
MySQL OUTFILE ci consente anche di esportare la tabella senza specificare alcun nome di colonna. Possiamo utilizzare la sintassi seguente per esportare la tabella in un formato di file 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 ' ';
Se eseguiamo l'istruzione precedente, il nostro strumento da riga di comando produce il seguente risultato. Significa che la tabella specificata contiene sei righe, esportate in dipendente_backup.csv file.
Gestione dei valori nulli
A volte i campi nel set di risultati hanno valori NULL, quindi il file di destinazione (tipo di file esportato) conterrà N anziché NULL. Possiamo risolvere questo problema sostituendo il valore NULL con 'non applicabile (N/A)' usando il SE NULL funzione. La seguente dichiarazione lo spiega più chiaramente:
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 ' ';
Esporta tabella in formato CSV utilizzando MySQL Workbench
Se non vogliamo accedere al server del database per esportare il file CSV, MySQL offre un'altra soluzione, ovvero l'utilizzo di MySQL Workbench. Workbench è uno strumento GUI per lavorare con il database MySQL senza utilizzare uno strumento da riga di comando. Ci consente di esportare il set di risultati di un'istruzione in un formato CSV nel nostro sistema locale. Per fare ciò, dobbiamo seguire i passaggi seguenti:
- Esegui l'istruzione/query e ottieni il set di risultati.
- Quindi, nel pannello dei risultati, fai clic su 'esporta il recordset in un file esterno' opzione. Il recordset viene utilizzato per il set di risultati.
- Infine, verrà visualizzata una nuova finestra di dialogo. Qui, dobbiamo fornire un nome file e il suo formato. Dopo aver compilato i dettagli, fare clic su Salva pulsante. L'immagine seguente lo spiega più chiaramente:
Ora possiamo verificare il risultato accedendo al percorso specificato.
stampa javascript