MySQL ima funkcijo izvoza tabele v datoteko CSV. Format datoteke CSV je vrednost, ločena z vejico, ki jo uporabljamo za izmenjavo podatkov med različnimi aplikacijami, kot so Microsoft Excel, Goole Docs in Open Office. Koristno je imeti podatke MySQL v formatu datoteke CSV, ki nam omogoča, da jih analiziramo in oblikujemo na želeni način. Gre za datoteko z navadnim besedilom, ki nam pomaga pri zelo preprostem izvozu podatkov.
MySQL ponuja enostaven način za izvoz katere koli tabele v datoteke CSV, ki se nahajajo v strežniku baze podatkov. Pred izvozom podatkov MySQL moramo zagotoviti naslednje:
- Proces strežnika MySQL ima dostop za branje/pisanje do določene (ciljne) mape, ki vsebuje datoteko CSV.
- Navedena datoteka CSV ne bi smela obstajati v sistemu.
Za izvoz tabele v datoteko CSV bomo uporabili IZBERI INTO....OUTFILE izjava. Ta izjava je kompliment za NALOŽI PODATKE ukaz, ki se uporablja za pisanje podatkov iz tabele in nato izvoz v določeno obliko datoteke na gostitelju strežnika. Zagotoviti je, da imamo privilegij datoteke za uporabo te sintakse.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
To sintakso lahko uporabimo tudi s stavkom vrednosti za izvoz podatkov neposredno v datoteko. Naslednja izjava to pojasnjuje bolj jasno:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Če želimo izvažati vse stolpce tabele , bomo uporabili spodnjo sintakso. S to izjavo bo vrstni red in število vrstic nadzoroval NAROČI PO in OMEJITEV klavzula.
če drugače če java
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Iz zgoraj navedenega
VRSTICE, KI SE KONČAJO Z ',' : Uporablja se za označevanje vrstic vrstic v datoteki, ki se končajo z vejico. Vsaka vrstica vsebuje podatke vsakega stolpca v datoteki.
POLJA OBJETA Z ''' : Uporablja se za določitev polja datoteke, obdanega z dvojnimi narekovaji. Preprečuje vrednosti, ki vsebujejo ločila z vejicami. Če so vrednosti v dvojnih narekovajih, ne prepozna vejice kot ločila.
Lokacija shranjevanja izvožene datoteke
Lokacija shranjevanja vsake izvožene datoteke v MySQL je shranjena v privzeti spremenljivki varna_datoteka_priv . Izvedemo lahko spodnji ukaz, da dobimo privzeto pot izvožene datoteke.
amplitudna modulacija
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Po izvedbi bo dal rezultat, kot sledi, kjer lahko vidimo to pot: C:/ProgramData/MySQL/MySQL Server 8.0/Nalaganja/ kot privzeto mesto datoteke. Ta pot bo uporabljena v času izvajanja ukaza za izvoz.
Če želimo spremeniti privzeto izvozno lokacijo datoteke CSV, navedeno v varna_datoteka_priv spremenljivko, moramo urediti moj.ini konfiguracijsko datoteko. Na platformi Windows se ta datoteka nahaja na tej poti: C:ProgramDataMySQLMySQL Server X.Y .
Če želimo izvoziti podatke MySQL, moramo najprej ustvariti a zbirka podatkov z vsaj enim tabela . To tabelo bomo uporabili kot primer.
Ustvarimo lahko a baza podatkov in tabela tako, da izvedete spodnjo kodo v urejevalnikih, ki jih uporabljamo:
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');
Če izvedemo IZBERI stavek, bomo videli naslednji rezultat:
Izvoz podatkov MySQL v formatu CSV z uporabo stavka SELECT INTO ... OUTFILE
Za izvoz podatkov tabele v datoteko CSV moramo izvesti poizvedbo na naslednji način:
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 ' ';
Dobili bomo naslednji rezultat, kjer lahko vidimo, da je prizadetih šest vrstic. To je zato, ker navedena tabela vsebuje samo šest vrstic.
Če znova izvedemo isti stavek, MySQL ustvari sporočilo o napaki, ki ga lahko vidite v spodnjem izpisu:
Sporočilo o napaki nam pove, da določeno ime datoteke že obstaja na navedenem mestu. Če torej izvozimo novo datoteko CSV z istim imenom in lokacijo, je ni mogoče ustvariti. To lahko rešimo tako, da izbrišemo obstoječo datoteko na določeni lokaciji ali preimenujemo ime datoteke, da jo ustvarimo na istem mestu.
Lahko preverimo, ali je datoteka CSV ustvarjena na določeni lokaciji ali ne, tako da se pomaknemo na dano pot, kot sledi:
Ko odpremo to datoteko, bo videti kot spodnja slika:
preklopna metoda java
Na sliki lahko vidimo, da so številska polja v narekovajih. Ta slog lahko spremenimo z dodajanjem NEOBVEZNO stavek pred 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 ' ';
Izvažanje podatkov z naslovom stolpca
Včasih želimo izvoziti podatke skupaj z naslovi stolpcev, zaradi katerih je datoteka priročna. Izvožena datoteka je bolj razumljiva, če prva vrstica datoteke CSV vsebuje naslove stolpcev. Naslove stolpcev lahko dodamo z uporabo UNION VSI izjava, kot sledi:
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 ' ';
V tej poizvedbi lahko vidimo, da smo dodali naslov za vsako ime stolpca. Izhod lahko preverimo tako, da se pomaknemo na navedeni URL, kjer prva vrstica vsebuje naslov za vsak stolpec:
Izvozi tabelo MySQL v formatu CSV
MySQL OUTFILE nam omogoča tudi izvoz tabele brez navedbe imena stolpca. Za izvoz tabele v obliki datoteke CSV lahko uporabimo spodnjo sintakso:
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 ' ';
Če izvedemo zgornji stavek, naše orodje ukazne vrstice ustvari naslednji rezultat. To pomeni, da navedena tabela vsebuje šest vrstic, ki so izvožene v delavec_backup.csv mapa.
Ravnanje z ničelnimi vrednostmi
Včasih imajo polja v nizu rezultatov vrednosti NULL, potem bo ciljna datoteka (izvožena vrsta datoteke) vsebovala N namesto NULL. To težavo lahko odpravimo tako, da vrednost NULL zamenjamo z 'se ne uporablja (N/A)' uporabljati IFNULL funkcijo. Spodnja izjava to pojasnjuje bolj jasno:
np pika
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 ' ';
Izvozite tabelo v format CSV z uporabo MySQL Workbench
Če ne želimo dostopati do strežnika baze podatkov za izvoz datoteke CSV, MySQL ponuja drug način, to je z uporabo MySQL Workbench. Workbench je orodje GUI za delo z bazo podatkov MySQL brez uporabe orodja ukazne vrstice. Omogoča nam izvoz niza rezultatov izjave v format CSV v našem lokalnem sistemu. Če želite to narediti, moramo slediti spodnjim korakom:
- Zaženite stavek/poizvedbo in pridobite njegov niz rezultatov.
- Nato na plošči z rezultati kliknite 'izvozi nabor zapisov v zunanjo datoteko' možnost. Nabor zapisov se uporablja za nabor rezultatov.
- Nazadnje se bo prikazalo novo pogovorno okno. Tukaj moramo zagotoviti ime datoteke in njeno obliko. Po izpolnitvi podrobnosti kliknite na Shrani gumb. Naslednja slika to bolj jasno pojasni:
Zdaj lahko preverimo rezultat tako, da se pomaknemo na navedeno pot.