MySQL má funkci exportu tabulky do souboru CSV. Formát souboru CSV je hodnota oddělená čárkami, kterou používáme k výměně dat mezi různými aplikacemi, jako je Microsoft Excel , Goole Docs a Open Office. Je užitečné mít data MySQL ve formátu souboru CSV, který nám umožňuje analyzovat a formátovat je tak, jak chceme. Jedná se o prostý textový soubor, který nám pomáhá velmi snadno exportovat data.
MySQL poskytuje snadný způsob exportu libovolné tabulky do souborů CSV umístěných na databázovém serveru. Před exportem dat MySQL musíme zajistit následující věci:
- Proces serveru MySQL má přístup pro čtení/zápis do určené (cílové) složky, která obsahuje soubor CSV.
- Zadaný soubor CSV by v systému neměl existovat.
Pro export tabulky do souboru CSV použijeme soubor VYBERTE DO....SOUBORU prohlášení. Toto prohlášení je komplimentem NAČÍST DATA příkaz, který se používá k zápisu dat z tabulky a jejich následnému exportu do zadaného formátu souboru na hostitelském serveru. Je to zajistit, abychom měli oprávnění k použití této syntaxe.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Tuto syntaxi můžeme také použít s příkazem values k exportu dat přímo do souboru. Následující prohlášení to vysvětluje jasněji:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Pokud chceme exportovat všechny sloupce tabulky , použijeme níže uvedenou syntaxi. Pomocí tohoto příkazu bude řazení a počet řádků řízeno SEŘADIT PODLE a OMEZIT doložka.
c# ukázkový kód
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Z výše uvedeného
ŘÁDKY UKONČENÉ ',' : Používá se k označení řádků řádků v souboru, které jsou ukončeny operátorem čárka. Každý řádek obsahuje data každého sloupce v souboru.
POLE UZAVŘENÁ ''' : Používá se k určení pole souboru uzavřeného do dvojitých uvozovek. Zabraňuje hodnotám, které obsahují oddělovače čárek. Pokud jsou hodnoty obsažené ve dvojitých uvozovkách, nerozpozná čárku jako oddělovač.
Umístění úložiště exportovaného souboru
Umístění úložiště každého exportovaného souboru v MySQL je uloženo ve výchozí proměnné secure_file_priv . Můžeme provést níže uvedený příkaz a získat výchozí cestu k exportovanému souboru.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Po provedení to dá výsledek takto, kde můžeme vidět tuto cestu: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ jako výchozí umístění souboru. Tato cesta bude použita v době spuštění příkazu exportu.
Pokud chceme změnit výchozí umístění exportu souboru CSV uvedeného v secure_file_priv proměnnou, musíme upravit my.ini konfigurační soubor. Na platformě Windows je tento soubor umístěn na této cestě: C:ProgramDataMySQLMySQL Server X.Y .
Pokud chceme exportovat data MySQL, musíme nejprve vytvořit a databáze s alespoň jedním stůl . Tuto tabulku použijeme jako příklad.
10 milionů
Můžeme vytvořit a databáze a tabulky spuštěním kódu níže v editorech, které používáme:
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');
Pokud provedeme VYBRAT příkazu, uvidíme následující výstup:
Exportujte data MySQL ve formátu CSV pomocí příkazu SELECT INTO ... OUTFILE
Chcete-li exportovat data tabulky do souboru CSV, musíme provést dotaz následovně:
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 ' ';
Dostaneme následující výstup, kde můžeme vidět, že je ovlivněno šest řádků. Je to proto, že uvedená tabulka obsahuje pouze šest řádků.
jiskra tutoriál
Pokud provedeme stejný příkaz znovu, MySQL vytvoří chybovou zprávu, kterou lze vidět v následujícím výstupu:
Chybová zpráva nám říká, že zadaný název souboru již v zadaném umístění existuje. Pokud tedy exportujeme nový soubor CSV se stejným názvem a umístěním, nelze jej vytvořit. Můžeme to vyřešit buď odstraněním existujícího souboru v zadaném umístění, nebo přejmenováním názvu souboru, aby byl vytvořen na stejném místě.
Soubor CSV vytvořený v zadaném umístění můžeme ověřit nebo ne tím, že přejdeme na danou cestu následovně:
Když tento soubor otevřeme, bude vypadat jako na obrázku níže:
Na obrázku vidíme, že číselná pole jsou v uvozovkách. Tento styl můžeme změnit přidáním OPTIONALLY doložka před 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 ' ';
Export dat se záhlavím sloupce
Někdy chceme exportovat data spolu se záhlavími sloupců, díky nimž je soubor pohodlný. Exportovaný soubor je srozumitelnější, pokud první řádek souboru CSV obsahuje záhlaví sloupců. Záhlaví sloupců můžeme přidat pomocí UNION VŠECHNY prohlášení takto:
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 tomto dotazu vidíme, že jsme přidali záhlaví pro každý název sloupce. Výstup můžeme ověřit přechodem na zadanou adresu URL, kde první řádek obsahuje záhlaví každého sloupce:
Export tabulky MySQL ve formátu CSV
MySQL OUTFILE nám také umožňuje exportovat tabulku bez uvedení jakéhokoli názvu sloupce. Pro export tabulky ve formátu CSV můžeme použít níže uvedenou syntaxi:
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 ' ';
Pokud provedeme výše uvedený příkaz, náš nástroj příkazového řádku vytvoří následující výsledek. To znamená, že zadaná tabulka obsahuje šest řádků, které se exportují zamestnanec_zaloha.csv soubor.
Práce s nulovými hodnotami
Někdy mají pole ve výsledné sadě hodnoty NULL, pak cílový soubor (exportovaný typ souboru) bude obsahovat N místo NULL. Tento problém můžeme vyřešit nahrazením hodnoty NULL za 'nepoužije se (N/A)' za použití IFNULL funkce. Níže uvedené prohlášení to vysvětluje jasněji:
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 ' ';
Export tabulky do formátu CSV pomocí MySQL Workbench
Pokud nechceme přistupovat k databázovému serveru pro export CSV souboru, MySQL nabízí jiný způsob, tedy pomocí MySQL Workbench. Workbench je nástroj GUI pro práci s databází MySQL bez použití nástroje příkazového řádku. Umožňuje nám exportovat sadu výsledků výpisu do formátu CSV v našem lokálním systému. Chcete-li to provést, musíme postupovat podle následujících kroků:
- Spusťte příkaz/dotaz a získejte jeho sadu výsledků.
- Poté na panelu výsledků klikněte na 'exportovat sadu záznamů do externího souboru' volba. Sada záznamů se používá pro sadu výsledků.
- Nakonec se zobrazí nové dialogové okno. Zde musíme zadat název souboru a jeho formát. Po vyplnění detailu klikněte na Uložit knoflík. Následující obrázek to vysvětluje jasněji:
Nyní můžeme ověřit výsledek přechodem na zadanou cestu.
v java regulárním výrazu