MySQL har en funksjon for å eksportere en tabell til CSV-filen. Et CSV-filformat er en kommadelt verdi som vi bruker til å utveksle data mellom ulike applikasjoner som Microsoft Excel , Goole Docs og Open Office. Det er nyttig å ha MySQL-data i CSV-filformat som lar oss analysere og formatere dem på den måten vi ønsker. Det er en ren tekstfil som hjelper oss å eksportere data veldig enkelt.
MySQL gir en enkel måte å eksportere en tabell til CSV-filer som ligger på databaseserveren. Vi må sørge for følgende før vi eksporterer MySQL-data:
- MySQL-serverens prosess har lese-/skrivetilgang til den spesifiserte (mål)mappen, som inneholder CSV-filen.
- Den angitte CSV-filen skal ikke eksistere i systemet.
For å eksportere tabellen til en CSV-fil, bruker vi VELG INTO....UTFIL uttalelse. Denne uttalelsen er et kompliment fra LAST DATA kommando, som brukes til å skrive data fra en tabell og deretter eksportere den til et spesifisert filformat på serververten. Det er for å sikre at vi har et filprivilegium til å bruke denne syntaksen.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Vi kan også bruke denne syntaksen med en verdisetning for å eksportere data direkte til en fil. Følgende uttalelse forklarer det tydeligere:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Hvis vi ønsker å eksportere alle tabellkolonner , vil vi bruke syntaksen nedenfor. Med denne uttalelsen vil rekkefølgen og antall rader bli kontrollert av REKKEFØLGE ETTER og GRENSE klausul.
navn på byer i USA
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Fra ovenstående,
LINJER AVSLUTTET AV ',' : Den brukes til å indikere linjene med rader i en fil som avsluttes av en kommaoperator. Hver linje inneholder hver kolonnes data i filen.
FELT OMKNYTTET AV ''' : Den brukes til å spesifisere feltet til filen omgitt av doble anførselstegn. Det forhindrer verdiene som inneholder kommaskilletegn. Hvis verdiene i doble anførselstegn, gjenkjenner den ikke komma som skilletegn.
Lagringssted for eksportert fil
Lagringsplasseringen til hver eksportert fil i MySQL er lagret i standardvariabelen secure_file_priv . Vi kan utføre kommandoen nedenfor for å få standardbanen til en eksportert fil.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Etter utførelse vil det gi resultatet som følger der vi kan se denne banen: C:/ProgramData/MySQL/MySQL Server 8.0/Opplastninger/ som standard filplassering. Denne banen vil bli brukt når en eksportkommando kjøres.
Hvis vi ønsker å endre standard eksportplassering for CSV-filen spesifisert i secure_file_priv variabel, må vi redigere min.ini konfigurasjonsfil. På Windows-plattformen ligger denne filen på denne banen: C:ProgramDataMySQLMySQL Server X.Y .
regexp_like i mysql
Hvis vi ønsker å eksportere MySQL-data, må vi først lage en database med minst én bord . Vi skal bruke denne tabellen som et eksempel.
Vi kan lage en database og tabell ved å kjøre koden nedenfor i redigeringsprogrammet vi bruker:
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');
Hvis vi utfører PLUKKE UT uttalelse, vil vi se følgende utgang:
Eksporter MySQL-data i CSV-format ved å bruke SELECT INTO ... OUTFILE-setningen
For å eksportere tabelldataene til en CSV-fil, må vi utføre spørringen som følger:
få gjeldende dato i java
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 ' ';
Vi vil få følgende utgang, der vi kan se at seks rader er berørt. Det er fordi den angitte tabellen bare inneholder seks rader.
Hvis vi kjører den samme setningen igjen, produserer MySQL en feilmelding som kan sees i utdataene nedenfor:
Feilmeldingen forteller oss at det angitte filnavnet allerede eksisterer på den angitte plasseringen. Derfor, hvis vi eksporterer den nye CSV-filen med samme navn og sted, kan den ikke opprettes. Vi kan løse dette enten slette den eksisterende filen på den angitte plasseringen eller gi nytt navn til filnavnet for å opprette den på samme sted.
Vi kan bekrefte CSV-filen som er opprettet på den angitte plasseringen eller ikke ved å navigere til en gitt bane som følger:
Når vi åpner denne filen, vil den se ut som bildet nedenfor:
På bildet kan vi se at de numeriske feltene er i anførselstegn. Vi kan endre denne stilen ved å legge til VALGFRITT klausul før INKLUDERT AV :
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 ' ';
Eksportere data med kolonneoverskrift
Noen ganger ønsker vi å eksportere data sammen med kolonneoverskrifter som gjør filen praktisk. Den eksporterte filen er mer forståelig hvis den første linjen i CSV-filen inneholder kolonneoverskriftene. Vi kan legge til kolonneoverskriftene ved å bruke UNION ALLE uttalelse som følger:
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 ' ';
I denne spørringen kan vi se at vi har lagt til overskrift for hvert kolonnenavn. Vi kan verifisere utdataene ved å navigere til den angitte URL-en der den første linjen inneholder overskriften for hver kolonne:
Eksporter MySQL-tabell i CSV-format
MySQL OUTFILE lar oss også eksportere tabellen uten å spesifisere noe kolonnenavn. Vi kan bruke syntaksen nedenfor for å eksportere tabell i et CSV-filformat:
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 ' ';
Hvis vi utfører setningen ovenfor, gir kommandolinjeverktøyet vårt følgende resultat. Det betyr at den angitte tabellen inneholder seks rader, som eksporteres inn ansatt_backup.csv fil.
tojson java
Håndtering av nullverdier
Noen ganger har feltene i resultatsettet NULL-verdier, da vil målfilen (eksportert filtype) inneholde N i stedet for NULL. Vi kan fikse dette problemet ved å erstatte NULL-verdien med 'ikke aktuelt (N/A)' bruker IFNULL funksjon. Utsagnet nedenfor forklarer det tydeligere:
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 ' ';
Eksporter tabell til CSV-format ved hjelp av MySQL Workbench
Hvis vi ikke ønsker å få tilgang til databaseserveren for eksport av CSV-filen, tilbyr MySQL en annen måte, det vil si å bruke MySQL Workbench. Workbench er et GUI-verktøy for å jobbe med MySQL-database uten å bruke et kommandolinjeverktøy. Den lar oss eksportere resultatsettet av en setning til et CSV-format i vårt lokale system. For å gjøre dette, må vi følge trinnene nedenfor:
- Kjør setningen/spørringen og få resultatsettet.
- Klikk deretter i resultatpanelet 'eksporter postsett til en ekstern fil' alternativ. Rekordsettet brukes for resultatsettet.
- Til slutt vil en ny dialogboks vises. Her må vi oppgi et filnavn og dets format. Etter å ha fylt ut detaljene, klikk på Lagre knapp. Følgende bilde forklarer det tydeligere:
Nå kan vi bekrefte resultatet ved å navigere til den angitte banen.