MySQL har en funktion för att exportera en tabell till CSV-filen. Ett CSV-filformat är ett kommaavgränsat värde som vi använder för att utbyta data mellan olika applikationer som Microsoft Excel , Goole Docs och Open Office. Det är användbart att ha MySQL-data i CSV-filformat som gör att vi kan analysera och formatera dem på det sätt vi vill. Det är en klartextfil som hjälper oss att exportera data mycket enkelt.
MySQL ger ett enkelt sätt att exportera valfri tabell till CSV-filer som finns på databasservern. Vi måste säkerställa följande saker innan vi exporterar MySQL-data:
- MySQL-serverns process har läs-/skrivåtkomst till den angivna (mål)mappen, som innehåller CSV-filen.
- Den angivna CSV-filen bör inte finnas i systemet.
För att exportera tabellen till en CSV-fil använder vi VÄLJ IN TILL....UTFIL påstående. Detta uttalande är en komplimang av LADDA DATA kommando, som används för att skriva data från en tabell och sedan exportera den till ett specificerat filformat på servervärden. Det är för att säkerställa att vi har en filrättighet att använda denna syntax.
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 också använda denna syntax med en värdesats för att exportera data direkt till en fil. Följande uttalande förklarar det tydligare:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Om vi vill exportera alla tabellkolumner , kommer vi att använda nedanstående syntax. Med detta uttalande kommer ordningen och antalet rader att kontrolleras av SORTERA EFTER och BEGRÄNSA klausul.
exempel på mooremaskin
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Från ovan,
RADER AVSLUTADE AV ',' : Den används för att indikera raderna i en fil som avslutas av en kommaoperator. Varje rad innehåller varje kolumns data i filen.
Fält som omges av ''' : Den används för att ange fältet för filen som omges av dubbla citattecken. Det förhindrar värden som innehåller kommaavgränsare. Om värdena som finns inom dubbla citattecken, känner den inte igen kommatecken som avgränsare.
Lagringsplats för exporterad fil
Lagringsplatsen för varje exporterad fil i MySQL lagras i standardvariabeln secure_file_priv . Vi kan utföra kommandot nedan för att få standardsökvägen för en exporterad fil.
reagera inline stil
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Efter exekvering kommer det att ge resultatet enligt följande där vi kan se denna väg: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ som standardfilplats. Den här sökvägen kommer att användas när ett exportkommando körs.
Om vi vill ändra standardexportplatsen för CSV-filen som anges i secure_file_priv variabeln måste vi redigera min.ini konfigurationsfil. På Windows-plattformen finns den här filen på denna sökväg: C:ProgramDataMySQLMySQL Server X.Y .
Om vi vill exportera MySQL-data måste vi först skapa en databas med minst en tabell . Vi kommer att använda den här tabellen som ett exempel.
Vi kan skapa en databas och tabell genom att köra koden nedan i de redigerare vi använder:
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');
Om vi utför VÄLJ uttalande kommer vi att se följande utdata:
Exportera MySQL-data i CSV-format med hjälp av SELECT INTO ... OUTFILE-satsen
För att exportera tabelldata till en CSV-fil måste vi köra frågan enligt följande:
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 kommer att få följande utdata, där vi kan se att sex rader påverkas. Det beror på att den angivna tabellen endast innehåller sex rader.
Om vi kör samma sats igen, producerar MySQL ett felmeddelande som kan ses i nedanstående utdata:
Felmeddelandet talar om för oss att det angivna filnamnet redan finns på den angivna platsen. Om vi exporterar den nya CSV-filen med samma namn och plats kan den alltså inte skapas. Vi kan lösa detta genom att antingen ta bort den befintliga filen på den angivna platsen eller byta namn på filnamnet för att skapa den på samma plats.
Vi kan verifiera CSV-filen som skapats på den angivna platsen eller inte genom att navigera till en given sökväg enligt följande:
När vi öppnar den här filen ser den ut som nedanstående bild:
exempel på binära träd
På bilden kan vi se att de numeriska fälten står inom citattecken. Vi kan ändra denna stil genom att lägga till VALFRITT klausul före OMSLUTAD 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 ' ';
Exportera data med kolumnrubrik
Ibland vill vi exportera data tillsammans med kolumnrubriker som gör filen bekväm. Den exporterade filen är mer förståelig om den första raden i CSV-filen innehåller kolumnrubrikerna. Vi kan lägga till kolumnrubrikerna genom att använda UNION ALLA uttalande enligt följande:
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 den här frågan kan vi se att vi har lagt till rubrik för varje kolumnnamn. Vi kan verifiera resultatet genom att navigera till den angivna webbadressen där den första raden innehåller rubriken för varje kolumn:
Exportera MySQL-tabell i CSV-format
MySQL OUTFILE tillåter oss också att exportera tabellen utan att ange något kolumnnamn. Vi kan använda syntaxen nedan för att exportera tabell i ett 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 ' ';
Om vi kör ovanstående sats ger vårt kommandoradsverktyg följande resultat. Det betyder att den angivna tabellen innehåller sex rader som exporteras in anställd_backup.csv fil.
Hantera nollvärden
Ibland har fälten i resultatuppsättningen NULL-värden, då kommer målfilen (exporterad filtyp) att innehålla N istället för NULL. Vi kan åtgärda det här problemet genom att ersätta NULL-värdet med 'ej tillämpligt (N/A)' använda IFNULL fungera. Nedanstående uttalande förklarar det tydligare:
np.unik
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 ' ';
Exportera tabell till CSV-format med MySQL Workbench
Om vi inte vill komma åt databasservern för att exportera CSV-filen, tillhandahåller MySQL ett annat sätt, d.v.s. att använda MySQL Workbench. Workbench är ett GUI-verktyg för att arbeta med MySQL-databas utan att använda ett kommandoradsverktyg. Det tillåter oss att exportera resultatuppsättningen av ett uttalande till ett CSV-format i vårt lokala system. För att göra detta måste vi följa stegen nedan:
- Kör satsen/frågan och få dess resultatuppsättning.
- Klicka sedan på i resultatpanelen 'exportera postuppsättning till en extern fil' alternativ. Rekorduppsättningen används för resultatuppsättningen.
- Slutligen kommer en ny dialogruta att visas. Här måste vi ange ett filnamn och dess format. När du har fyllt i detalj klickar du på Spara knapp. Följande bild förklarar det tydligare:
Nu kan vi verifiera resultatet genom att navigera till den angivna sökvägen.