MySQL heeft een functie om een tabel naar het CSV-bestand te exporteren. Een CSV-bestandsformaat is een door komma's gescheiden waarde die we gebruiken om gegevens uit te wisselen tussen verschillende applicaties zoals Microsoft Excel, Goole Docs en Open Office. Het is handig om MySQL-gegevens in CSV-bestandsformaat te hebben, zodat we ze kunnen analyseren en opmaken zoals wij dat willen. Het is een tekstbestand waarmee we heel gemakkelijk gegevens kunnen exporteren.
MySQL biedt een eenvoudige manier om elke tabel naar CSV-bestanden te exporteren die zich op de databaseserver bevinden. We moeten voor het volgende zorgen voordat we MySQL-gegevens exporteren:
- Het proces van de MySQL-server heeft lees-/schrijftoegang tot de opgegeven (doel)map, die het CSV-bestand bevat.
- Het opgegeven CSV-bestand mag niet in het systeem voorkomen.
Om de tabel naar een CSV-bestand te exporteren, gebruiken we de SELECTEER IN....OUTFILE stelling. Deze verklaring is een compliment van de GEGEVENS LADEN opdracht, die wordt gebruikt om gegevens uit een tabel te schrijven en deze vervolgens naar een opgegeven bestandsindeling op de serverhost te exporteren. Het is om ervoor te zorgen dat we het bestandsrecht hebben om deze syntaxis te gebruiken.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
We kunnen deze syntaxis ook gebruiken met een waardeninstructie om gegevens rechtstreeks naar een bestand te exporteren. De volgende verklaring legt het duidelijker uit:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Als we willen exporteren alle tabelkolommen , zullen we de onderstaande syntaxis gebruiken. Met deze verklaring wordt de volgorde en het aantal rijen bepaald door de BESTEL DOOR En BEGRENZING clausule.
vergelijk met methode java
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Uit het bovenstaande,
REGELS BEËINDIGD DOOR ',' : Het wordt gebruikt om de regels van rijen in een bestand aan te geven die worden afgesloten door een komma-operator. Elke regel bevat de gegevens van elke kolom in het bestand.
VELDEN OMGESLOTEN DOOR ''' : Het wordt gebruikt om het veld van het bestand op te geven dat tussen dubbele aanhalingstekens staat. Het voorkomt de waarden die kommascheidingstekens bevatten. Als de waarden tussen dubbele aanhalingstekens staan, wordt de komma niet als scheidingsteken herkend.
Opslaglocatie van geëxporteerd bestand
De opslaglocatie van elk geëxporteerd bestand in MySQL wordt opgeslagen in de standaardvariabele secure_file_priv . We kunnen de onderstaande opdracht uitvoeren om het standaardpad van een geëxporteerd bestand te verkrijgen.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Na uitvoering zal het het volgende resultaat opleveren, waar we dit pad kunnen zien: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ als de standaard bestandslocatie. Dit pad wordt gebruikt wanneer een exportopdracht wordt uitgevoerd.
rr-algoritme
Als we de standaard exportlocatie willen wijzigen van het CSV-bestand dat is opgegeven in het secure_file_priv variabele, moeten we de mijn.ini configuratiebestand. Op het Windows-platform bevindt dit bestand zich op dit pad: C:ProgramDataMySQLMySQL Server X.Y .
Als we MySQL-gegevens willen exporteren, moeten we eerst een database met minstens één tafel . We gaan deze tabel als voorbeeld gebruiken.
Wij kunnen een database en tabel door de onderstaande code uit te voeren in de editors die we gebruiken:
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');
Als we de SELECTEER statement, zullen we de volgende uitvoer zien:
Exporteer MySQL-gegevens in CSV-formaat met behulp van de instructie SELECT INTO ... OUTFILE
Om de tabelgegevens naar een CSV-bestand te exporteren, moeten we de query als volgt uitvoeren:
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 ' ';
We krijgen de volgende uitvoer, waarin we kunnen zien dat zes rijen worden beïnvloed. Dit komt omdat de opgegeven tabel slechts zes rijen bevat.
Als we dezelfde instructie opnieuw uitvoeren, produceert MySQL een foutmelding die te zien is in de onderstaande uitvoer:
Het foutbericht vertelt ons dat de opgegeven bestandsnaam al bestaat op de opgegeven locatie. Als we dus het nieuwe CSV-bestand met dezelfde naam en locatie exporteren, kan het niet worden gemaakt. We kunnen dit oplossen door het bestaande bestand op de opgegeven locatie te verwijderen of de bestandsnaam te hernoemen om het op dezelfde plaats te maken.
We kunnen het CSV-bestand dat op de opgegeven locatie is gemaakt, verifiëren of niet door als volgt naar een bepaald pad te navigeren:
Wanneer we dit bestand openen, ziet het er als volgt uit:
In de afbeelding kunnen we zien dat de numerieke velden tussen aanhalingstekens staan. We kunnen deze stijl veranderen door toe te voegen OPTIONEEL-clausule vóór ENCLOSED BY :
open een bestand met java
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 ' ';
Gegevens exporteren met kolomkop
Soms willen we gegevens exporteren samen met kolomkoppen die het bestand handig maken. Het geëxporteerde bestand is begrijpelijker als de eerste regel van het CSV-bestand de kolomkoppen bevat. We kunnen de kolomkoppen toevoegen met behulp van de UNIE ALLEMAAL verklaring als volgt:
tupels python sorteren
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 ' ';
In deze query kunnen we zien dat we een kop hebben toegevoegd voor elke kolomnaam. We kunnen de uitvoer verifiëren door naar de opgegeven URL te navigeren, waar de eerste regel de kop voor elke kolom bevat:
Exporteer MySQL-tabel in CSV-formaat
Met MySQL OUTFILE kunnen we de tabel ook exporteren zonder een kolomnaam op te geven. We kunnen de onderstaande syntaxis gebruiken om de tabel in een CSV-bestandsindeling te exporteren:
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 ' ';
Als we de bovenstaande instructie uitvoeren, levert ons opdrachtregelprogramma het volgende resultaat op. Het betekent dat de opgegeven tabel zes rijen bevat, die worden geëxporteerd medewerker_backup.csv bestand.
Nulwaarden verwerken
Soms hebben de velden in de resultatenset NULL-waarden, dan zal het doelbestand (geëxporteerd bestandstype) N bevatten in plaats van NULL. We kunnen dit probleem oplossen door de NULL-waarde te vervangen door 'niet van toepassing n.v.t)' de ... gebruiken INDIEN NUL functie. De onderstaande verklaring legt het duidelijker uit:
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 ' ';
Tabel exporteren naar CSV-formaat met behulp van MySQL Workbench
Als we geen toegang willen hebben tot de databaseserver voor het exporteren van het CSV-bestand, biedt MySQL een andere manier, namelijk met behulp van MySQL Workbench. Workbench is een GUI-tool om met de MySQL-database te werken zonder een opdrachtregelprogramma te gebruiken. Hiermee kunnen we de resultatenset van een verklaring exporteren naar een CSV-formaat in ons lokale systeem. Om dit te doen, moeten we de onderstaande stappen volgen:
- Voer de instructie/query uit en haal de resultatenset op.
- Klik vervolgens in het resultatenpaneel op 'Exporteer recordset naar een extern bestand' keuze. De recordset wordt gebruikt voor de resultatenset.
- Ten slotte wordt een nieuw dialoogvenster weergegeven. Hier moeten we een bestandsnaam en het formaat ervan opgeven. Nadat u de details heeft ingevuld, klikt u op de Redden knop. De volgende afbeelding legt het duidelijker uit:
Nu kunnen we het resultaat verifiëren door naar het opgegeven pad te navigeren.