MySQL har en funktion til at eksportere en tabel til CSV-filen. Et CSV-filformat er en kommasepareret værdi, som vi bruger til at udveksle data mellem forskellige applikationer såsom Microsoft Excel , Goole Docs og Open Office. Det er nyttigt at have MySQL-data i CSV-filformat, der giver os mulighed for at analysere og formatere dem på den måde, vi ønsker. Det er en almindelig tekstfil, der hjælper os med at eksportere data meget nemt.
MySQL giver en nem måde at eksportere enhver tabel til CSV-filer, der ligger på databaseserveren. Vi skal sikre følgende ting, før vi eksporterer MySQL-data:
- MySQL-serverens proces har læse-/skriveadgang til den angivne (mål)mappe, som indeholder CSV-filen.
- Den angivne CSV-fil burde ikke eksistere i systemet.
For at eksportere tabellen til en CSV-fil, bruger vi VÆLG INTO....UDFIL udmelding. Denne udtalelse er et kompliment fra INDLÆS DATA kommando, som bruges til at skrive data fra en tabel og derefter eksportere den til et specificeret filformat på serverværten. Det er for at sikre, at vi har et filprivilegium til at bruge denne syntaks.
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å bruge denne syntaks med en værdisætning til at eksportere data direkte til en fil. Følgende erklæring forklarer det mere tydeligt:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Hvis vi vil eksportere alle tabelkolonner , vil vi bruge nedenstående syntaks. Med denne erklæring vil rækkefølgen og antallet af rækker blive kontrolleret af BESTIL EFTER og BEGRÆNSE klausul.
objekt i java-programmering
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 AFSLUTTET AF ',' : Det bruges til at angive linjerne af rækker i en fil, der afsluttes af en kommaoperator. Hver linje indeholder hver kolonnes data i filen.
FELTER OMKUNDET AF ''' : Det bruges til at angive feltet for filen omgivet af dobbelte anførselstegn. Det forhindrer de værdier, der indeholder kommaseparatorer. Hvis værdierne indeholdt i dobbelte anførselstegn, genkender den ikke komma som et skilletegn.
Lagerplacering af eksporteret fil
Lagerplaceringen af hver eksporteret fil i MySQL er gemt i standardvariablen sikker_fil_priv . Vi kan udføre nedenstående kommando for at få standardstien til en eksporteret fil.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Efter udførelse vil det give resultatet som følger, hvor vi kan se denne sti: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ som standard filplacering. Denne sti vil blive brugt på tidspunktet for kørsel af en eksportkommando.
Hvis vi ønsker at ændre standard eksportplacering af CSV-filen angivet i sikker_fil_priv variabel, skal vi redigere min.ini konfigurationsfil. På Windows-platformen er denne fil placeret på denne sti: C:ProgramDataMySQLMySQL Server X.Y .
noget hurtig sortering
Hvis vi vil eksportere MySQL-data, skal vi først oprette en database med mindst én bord . Vi vil bruge denne tabel som et eksempel.
Vi kan skabe en database og tabel ved at udføre koden nedenfor i de editorer, vi bruger:
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 udfører VÆLG erklæring, vil vi se følgende output:
Eksporter MySQL-data i CSV-format ved hjælp af SELECT INTO ... OUTFILE-sætningen
For at eksportere tabeldataene til en CSV-fil skal vi udføre forespørgslen som følger:
parameter i shell-script
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 får følgende output, hvor vi kan se, at seks rækker er påvirket. Det er fordi den angivne tabel kun indeholder seks rækker.
Hvis vi udfører den samme sætning igen, producerer MySQL en fejlmeddelelse, der kan ses i nedenstående output:
Fejlmeddelelsen fortæller os, at det angivne filnavn allerede findes på den angivne placering. Hvis vi således eksporterer den nye CSV-fil med samme navn og placering, kan den ikke oprettes. Vi kan løse dette enten ved at slette den eksisterende fil på den angivne placering eller omdøbe filnavnet for at oprette den samme sted.
Vi kan bekræfte den CSV-fil, der er oprettet på den angivne placering eller ej, ved at navigere til en given sti som følger:
Når vi åbner denne fil, vil den se ud som nedenstående billede:
På billedet kan vi se, at de numeriske felter er i anførselstegn. Vi kan ændre denne stil ved at tilføje VALGTIG klausul før INKLUDET AF :
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 ' ';
Eksport af data med kolonneoverskrift
Nogle gange ønsker vi at eksportere data sammen med kolonneoverskrifter, der gør filen praktisk. Den eksporterede fil er mere forståelig, hvis den første linje i CSV-filen indeholder kolonneoverskrifterne. Vi kan tilføje kolonneoverskrifterne ved at bruge UNION ALLE erklæring 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 forespørgsel kan vi se, at vi har tilføjet overskrift for hvert kolonnenavn. Vi kan verificere outputtet ved at navigere til den angivne URL, hvor den første linje indeholder overskriften for hver kolonne:
Eksporter MySQL-tabel i CSV-format
MySQL OUTFILE giver os også mulighed for at eksportere tabellen uden at angive noget kolonnenavn. Vi kan bruge nedenstående syntaks til at eksportere tabel 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 udfører ovenstående sætning, producerer vores kommandolinjeværktøj følgende resultat. Det betyder, at den angivne tabel indeholder seks rækker, som eksporteres ind medarbejder_backup.csv fil.
powershell mindre end eller lig med
Håndtering af nulværdier
Nogle gange har felterne i resultatsættet NULL-værdier, så vil målfilen (eksporteret filtype) indeholde N i stedet for NULL. Vi kan løse dette problem ved at erstatte NULL-værdien med 'ikke relevant (Ikke relevant)' bruger IFNULL fungere. Nedenstående erklæring forklarer det mere tydeligt:
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 tabel til CSV-format ved hjælp af MySQL Workbench
Hvis vi ikke ønsker at få adgang til databaseserveren for at eksportere CSV-filen, tilbyder MySQL en anden måde, dvs. ved at bruge MySQL Workbench. Workbench er et GUI-værktøj til at arbejde med MySQL-database uden at bruge et kommandolinjeværktøj. Det giver os mulighed for at eksportere resultatsættet af en erklæring til et CSV-format i vores lokale system. For at gøre dette skal vi følge nedenstående trin:
- Kør sætningen/forespørgslen og få dens resultatsæt.
- Klik derefter i resultatpanelet 'eksportér postsæt til en ekstern fil' mulighed. Rekordsættet bruges til resultatsættet.
- Til sidst vil en ny dialogboks blive vist. Her skal vi angive et filnavn og dets format. Når du har udfyldt detaljerne, skal du klikke på Gemme knap. Følgende billede forklarer det mere tydeligt:
Nu kan vi verificere resultatet ved at navigere til den angivne sti.