Door komma ‘ s Gescheiden Waarden bestanden (CSV) zijn een manier van het overbrengen van gegevens tussen applicaties. Databases zoals MySQL en spreadsheetprogramma zoals Excel ondersteuning voor het importeren en exporteren via een CSV, zodat u kunt gebruik maken van het CSV-bestanden om gegevens uit te wisselen tussen de twee.
CSV-bestanden leesbare tekst, dus ze zijn van nature lichtgewicht en gemakkelijk te exporteren van MySQL.
Uit De Database Server
Als u toegang hebt tot de server MySQL werkt, kunt u het exporteren van een selectie met het INTO OUTFILE opdracht.
SELECT id, column1, column2 UIT tabel
IN OUTFILE ‘/tmp/mysqlfiles/tabel.csv’
FIELDS TERMINATED BY ‘,’
INGESLOTEN DOOR ‘”‘
LINES TERMINATED BY ‘n’
Dit resultaat naar een CSV-bestand /tmp/mysqlfiles/tabel.csv -, of waar u geconfigureerd. Je moet ervoor zorgen dat de gebruiker MySQL draait (meestal mysql of root) heeft het eigendom en schrijf toegang tot de map.
U moet ook ervoor zorgen dat de secure_file_priv instelling maakt het mogelijk MySQL om toegang te krijgen tot de directory. Deze standaard blokkeert toegang tot lezen en schrijven van SQL-query ‘ s. Dit is een goede zaak; als uw code is kwetsbaar voor SQL-injectie, een potentiële aanvaller zou hebben alleen toegang tot MySQL, en niet op de rest van het bestandssysteem.
U kunt een witte lijst van specifieke directory aan door het bewerken van uw MySQL configuratie bestand (meestal te vinden in /etc/mijn.cnf) op te nemen:
[mysqld]
secure-bestand-priv = “/tmp/mysqlfiles”
Wat zal toelaten om MySQL te lezen en te schrijven naar /tmp/mysqlfiles/ (die heb je te maken met mkdir). Zodra MySQL kan bestanden exporteren, moet u in staat zijn om de query uitvoeren en de output van CSV-bestanden.
Met de GESLOTEN DOOR de instelling, komma ‘ s worden correct ontsnapt, bijvoorbeeld:
“3”,”Escape, deze”,”ook, dit”
Die u kunt nemen en importeren direct in een spreadsheet-programma of andere software.
Houd in gedachten dat het geëxporteerde CSV-bestand geen kolom koppen, maar de kolommen worden in dezelfde volgorde als de SELECT-instructie. Ook null-waarden worden geëxporteerd als N, dat is normaal, maar als u graag om dit te wijzigen kunt u de selectie door het wikkelen van ifnull(veld, “”) om uw velden in de SELECT-instructie.
Uit De MySQL Command Line
Als u alleen de command line toegang tot de MySQL-exemplaar, en niet de toegang tot de server zelf (bijvoorbeeld als het niet in geslaagd door u, in het geval van Amazon RDS), het probleem is een beetje lastiger. Terwijl u kunt gebruik maken van FIELDS TERMINATED BY ‘,’ op de server genereert een door komma ‘ s gescheiden lijst, MySQL CLI zal scheiden met tabs standaard.
Voer gewoon een zoekopdracht in de command line, en de slang naar een bestand:
mysql -u root -e “select * from database;” > de output.tsv
Omdat MySQL output is gescheiden door tabs, dit is wel een TSV-bestand, voor de “tab-gescheiden waarden” en het kan werken in plaats van uw CSV-bestand in sommige programma ‘ s zoals werkblad invoer. Maar het is niet een CSV-bestand en converteren naar een ingewikkeld is.
Je kan gewoon vervangen op elk tabblad met een komma, die zou werken, maar zou ervoor dat het niet als er komma ‘ s in de invoergegevens. Als je bent helemaal 100% zeker dat er geen komma ‘ s in uw TSV-bestand (controleer met grep), kunt u de tabbladen met sed:
sed ‘s/t/,/g” – uitgang.tsv – > output.csv
Maar als je komma ‘ s in uw gegevens, moet u gebruik maken van een veel langer regex:
sed “s/’/’/;s/s/”,”/g;s/^/”/;s/$/”/;s/n//g” – uitgang.tsv – > output.csv
Dit zal goed ontsnappen velden met aanhalingstekens, die de oplossing van de komma probleem.
Opmerking: de tab-teken t is niet standaard. Op macOS en BSD, het is niet beschikbaar, wat leidt tot een puinhoop van elke kleine letter “t” waardoor sed invoegen foute komma ‘ s. Om dit op te lossen, moet u gebruik maken van een letterlijke tab-teken in plaats van t:
sed “s/ //g” – uitgang.tsv – > output.csv
Als uw invoer bevat tabs, heb je pech en zal het hebben voor het genereren van een CSV-bestand dat je met een scripting taal.
Het Handmatig doen Met een Echte programmeertaal
MySQL (en de meeste databases) zijn ontworpen om interactie met, zodat u waarschijnlijk een soort van programmeer taal verbonden aan de MySQL-al. De meeste talen kunt ook schrijven naar de schijf, zodat u kunt uw eigen CSV-uitvoer van scripts door het lezen van velden uit de database rechtstreeks, ontsnappen ze goed, en het schrijven van een komma-gescheiden bestand.
Een voorbeeld in Python.