MySQL:ssä on ominaisuus viedä taulukko CSV-tiedostoon. CSV-tiedostomuoto on pilkuilla erotettu arvo, jota käytämme tietojen vaihtamiseen eri sovellusten, kuten Microsoft Excelin, Goole Docsin ja Open Officen, välillä. On hyödyllistä, että MySQL-tiedot ovat CSV-tiedostomuodossa, jotta voimme analysoida ja muotoilla niitä haluamallamme tavalla. Se on pelkkä tekstitiedosto, jonka avulla voimme viedä tietoja erittäin helposti.
MySQL tarjoaa helpon tavan viedä mikä tahansa taulukko CSV-tiedostoiksi, joka sijaitsee tietokantapalvelimessa. Meidän on varmistettava seuraavat asiat ennen MySQL-tietojen vientiä:
- MySQL-palvelimen prosessilla on luku-/kirjoitusoikeus määritettyyn (kohde)kansioon, joka sisältää CSV-tiedoston.
- Määritettyä CSV-tiedostoa ei pitäisi olla järjestelmässä.
Käytämme taulukon viemiseen CSV-tiedostoon SELECT INTO...OUT FILE lausunto. Tämä lausunto on kohteliaisuus LATAA TIEDOT -komento, jota käytetään tietojen kirjoittamiseen taulukosta ja viemiseen sitten määritettyyn tiedostomuotoon palvelimen isännässä. Sen tarkoituksena on varmistaa, että meillä on tiedostooikeudet käyttää tätä syntaksia.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Voimme myös käyttää tätä syntaksia arvolausekkeen kanssa viedäksemme tietoja suoraan tiedostoon. Seuraava lausunto selittää sen selkeämmin:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Jos haluamme viedä kaikki taulukon sarakkeet , käytämme alla olevaa syntaksia. Tällä lausekkeella rivien järjestystä ja lukumäärää hallitsee TILAA ja RAJA lauseke.
merkkijonoa verrattuna javaan
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Ylläolevasta,
RIVIT, JOTKA PÄÄTTEE ',' : Sitä käytetään osoittamaan tiedoston rivit, jotka päätetään pilkkuoperaattorilla. Jokainen rivi sisältää kunkin sarakkeen tiedot tiedostossa.
KENTÄT, JOITA ''' : Sitä käytetään lainausmerkkien sisällä olevan tiedoston kentän määrittämiseen. Se estää arvot, jotka sisältävät pilkkuerottimia. Jos lainausmerkeissä olevat arvot ovat, se ei tunnista pilkkua erottimeksi.
Viedyn tiedoston tallennuspaikka
Jokaisen viedyn tiedoston tallennuspaikka MySQL:ssä on tallennettu oletusmuuttujaan suojattu_tiedosto_priv . Voimme suorittaa alla olevan komennon saadaksesi viedyn tiedoston oletuspolun.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Suorituksen jälkeen se antaa tuloksen seuraavasti, missä voimme nähdä tämän polun: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ oletustiedoston sijaintina. Tätä polkua käytetään vientikomennon suorittamisen aikana.
Jos haluamme muuttaa CSV-tiedoston oletusvientisijaintia, joka on määritetty kohdassa suojattu_tiedosto_priv muuttuja, meidän on muokattava my.ini asetustiedosto. Windows-alustalla tämä tiedosto sijaitsee tällä polulla: C:ProgramDataMySQLMySQL Server X.Y .
Jos haluamme viedä MySQL-tietoja, meidän on ensin luotava a tietokanta ainakin yhden kanssa pöytä . Käytämme tätä taulukkoa esimerkkinä.
Voimme luoda a tietokanta ja taulukko suorittamalla alla oleva koodi käyttämissämme muokkausohjelmissa:
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');
Jos toteutamme VALITSE lausunto, näemme seuraavan tulosteen:
inurl:.git/head
Vie MySQL-tiedot CSV-muodossa käyttämällä SELECT INTO ... OUTFILE -käskyä
Jos haluat viedä taulukkotiedot CSV-tiedostoon, meidän on suoritettava kysely seuraavasti:
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 ' ';
Saamme seuraavan lähdön, jossa voimme nähdä, että se vaikuttaa kuuteen riviin. Tämä johtuu siitä, että määritetty taulukko sisältää vain kuusi riviä.
Jos suoritamme saman käskyn uudelleen, MySQL tuottaa virheilmoituksen, joka näkyy alla olevassa lähdössä:
Virheilmoitus kertoo, että määritetty tiedostonimi on jo olemassa määritetyssä paikassa. Jos siis viemme uuden CSV-tiedoston samalla nimellä ja sijainnilla, sitä ei voida luoda. Voimme ratkaista tämän joko poistaa olemassa olevan tiedoston määritetystä sijainnista tai nimetä tiedoston nimen uudelleen luodaksesi sen samaan paikkaan.
postinkantaja
Voimme tarkistaa CSV-tiedoston, joka on luotu määritettyyn paikkaan vai ei, navigoimalla annettuun polkuun seuraavasti:
Kun avaamme tämän tiedoston, se näyttää alla olevalta kuvalta:
Kuvassa näemme, että numerokentät ovat lainausmerkeissä. Voimme muuttaa tätä tyyliä lisäämällä VALINNAISESTI lauseke ennen LIITTEETTÄ :
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 ' ';
Tietojen vienti sarakeotsikolla
Joskus haluamme viedä tietoja sarakeotsikoiden kanssa, jotka tekevät tiedostosta kätevän. Vietyä tiedostoa on helpompi ymmärtää, jos CSV-tiedoston ensimmäisellä rivillä on sarakeotsikot. Voimme lisätä sarakeotsikot käyttämällä UNIONI KAIKKI lausunto seuraavasti:
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 ' ';
Tässä kyselyssä voimme nähdä, että olemme lisänneet otsikon jokaiselle sarakkeen nimelle. Voimme tarkistaa tulosteen siirtymällä määritettyyn URL-osoitteeseen, jossa ensimmäinen rivi sisältää kunkin sarakkeen otsikon:
Vie MySQL-taulukko CSV-muodossa
MySQL OUTFILE mahdollistaa myös taulukon viemisen ilman sarakkeen nimeä. Voimme käyttää alla olevaa syntaksia viedäksesi taulukon CSV-tiedostomuotoon:
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 ' ';
Jos suoritamme yllä olevan käskyn, komentorivityökalumme tuottaa seuraavan tuloksen. Se tarkoittaa, että määritetty taulukko sisältää kuusi riviä, jotka on viety sisään työntekijä_varmuuskopio.csv tiedosto.
Nolla-arvojen käsittely
Joskus tulosjoukon kentillä on NULL-arvoja, jolloin kohdetiedosto (viety tiedostotyyppi) sisältää N NULL:n sijaan. Voimme korjata tämän ongelman korvaamalla NULL-arvon arvolla 'ei sovellettavissa (ei käytössä)' käyttämällä IFNULL toiminto. Alla oleva lausunto selittää sen selkeämmin:
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 ' ';
Vie taulukko CSV-muotoon MySQL Workbenchin avulla
Jos emme halua käyttää tietokantapalvelinta CSV-tiedoston vientiä varten, MySQL tarjoaa toisen tavan, eli MySQL Workbenchin avulla. Workbench on GUI-työkalu, joka toimii MySQL-tietokannan kanssa ilman komentorivityökalua. Sen avulla voimme viedä lauseen tulosjoukon CSV-muotoon paikallisessa järjestelmässämme. Tätä varten meidän on noudatettava alla olevia vaiheita:
ota kiinni ja kokeile javaa
- Suorita lauseke/kysely ja hanki sen tulosjoukko.
- Napsauta sitten tulospaneelissa 'vie tietuejoukko ulkoiseen tiedostoon' vaihtoehto. Ennätysjoukkoa käytetään tulosjoukossa.
- Lopuksi uusi valintaikkuna tulee näkyviin. Tässä meidän on annettava tiedostonimi ja sen muoto. Kun olet täyttänyt tiedot, napsauta Tallentaa -painiketta. Seuraava kuva selittää asian selkeämmin:
Nyt voimme tarkistaa tuloksen navigoimalla määritettyyn polkuun.