Jak exportovat výsledky dotazů MySQL do formátu CSV v systému Linux

Dotazování z MySQL databázový shell je vždy zábavný a technický, dokud nepotřebujete někde uložený databázový výstup pro snadný přístup a odkaz; zejména při práci s velkými datovými soubory.

Rychlý přístup k datům vám ušetří nutnost pokaždé se přihlašovat k serveru MySQL přes shell terminálu, abyste mohli odkazovat na konkrétní výstupy související s dotazem MySQL. Soubor CSV (Hodnota oddělená čárkami ) je ideálním kandidátem pro řešení těchto typů opakujících se interakcí mezi uživatelem a databází.

Formát souboru CSV je nejlépe použitelný pro ukládání výstupů MySQL, protože má výrazné atributy, mezi které patří:

• Je to široce přijatelný formát pro ukládání dat oddělených čárkami.
• Jeho přidaná výhoda čitelná pro člověka.
• Snadný import do jakékoli aplikace díky povaze prostého textu.
• Jeho přizpůsobivost při správě a organizaci velkých datových sad.

Předpoklady

• Soubor CSV, který bude spojen s MySQL výstupy dotazů by ještě neměly existovat, protože budou automaticky generovány během provádění cíleného výstupu dotazu MySQL.
• Mějte práva root v databázi MySQL i v systému Linux.

Vytvoření vzorové databázové tabulky s více hodnotami řádků

Aby byl tento tutoriál poutavý a lépe srozumitelný, musí existovat databázová tabulka s některými hodnotami. V tomto tutoriálu můžete být buď pod MySQL nebo MariaDB RDBMS. Od MariaDB je open-source fork MySQL , tyto dva RDBMS odkazovat na stejnou implementaci jejich příkazů databázového shellu.

Přihlaste se do MySQL databáze jako uživatel root DB nebo se stávajícím pověřením uživatele databáze.

$ sudo mysql -u root -p

Vytvoříme novou databázi, která bude hostit naši novou databázovou tabulku.

MariaDB[(none)]> zobrazit databáze;MariaDB[(none)]> vytvořit databázi lst_db;MariaDB[(none)]> použijte lst_db;

Dále vytvořte databázi s několika tabulkami, jak je znázorněno.

MariaDB[(none)]> CREATE TABLE lst_projects( project_id INT AUTO_INCREMENT, project_name VARCHAR(100) NOT NULL, project_category VARCHAR(100) NOT NULL, project_manager VARCHAR(100) NOT NULL, start_date DATE, end_date DATE, PRIMARY_id KEY(project_preject KEY));  
Naplnění databázové tabulky MySQL daty
 

 Ověřili jsme, že námi vytvořená databázová tabulka MySQL existuje. Je čas jej naplnit nějakými daty.
 
MariaDB[(none)]> zobrazit tabulky;MariaDB[(none)]> INSERT INTO lst_projects(project_name, project_category, project_manager, start_date, end_date)VALUES ('Marketing','AI','David Guitar','2021-08-01','2021-12-31'), ('Psaní kopií ','AI','Viola Guin','2022-01-01','2022-03-31'), ('Modeling','Robotics','Mary Atkins','2023-04-01', '2023-07-31'), ('API','ML','Duncan Reeves','2024-02-01','2024-06-20'), ('Prodej','ML',' Anthony Luigi','2025-05-15','2025-11-20');
  

 Dovolte nám potvrdit existenci našich lst_projects tabulkové hodnoty.
 
MariaDB[(none)]> SELECT * FROM lst_projects;
  
Export výsledků dotazů MySQL do formátu CSV
 

 Dočasný adresář „/var/tmp“ poskytuje MySQL potřebná oprávnění pro čtení a zápis. Použijeme jej k hostování všech souborů CSV automaticky generovaných z dotazů MySQL.
 

 Několik podmínek určuje, jak exportujeme výsledek dotazu MySQL do formátu souboru CSV.
 
Export všech dotazů MySQL do CSV
 

 Chcete-li exportovat tento dotaz db, „SELECT * FROM lst_projects; ” do souboru CSV, implementovali bychom jej následujícím způsobem:
 
MariaDB[(none)]> SELECT * FROM lst_projectsINTO OUTFILE '/var/tmp/get_all_queries.csv'FIELDS UZAVŘENÉ '"'UKONČENO ';'UKONČENO '"' ŘÁDKY UKONČENÉ '\r\n';
  

 Pokusme se získat vygenerovaný soubor:
  
Exportujte tabulky MySQL do CSV s hlavičkami
 

 Tento přístup dává vygenerovanému souboru CSV profesionální vzhled.
 
MariaDB[(none)]> (SELECT 'Název projektu', 'Kategorie projektu', 'Manažer projektu', 'Datum zahájení', 'Datum ukončení')UNION (SELECT název_projektu, kategorie_projektu, manažer_projektu, datum_zahájení, datum_ukončeníFROM lst_projectsINTO OUTFILE '/var/tmp/included_column_headings.csv 'POLE UZAVŘENÉ '"' UKONČENO ';' UKONČENO '"' ŘÁDKY UKONČENÉ '\r\n');
 

 Pojďme znovu získat vygenerovaný soubor:
  

 Jak bylo uvedeno, export CSV dotazu MySQL je nyní dobře organizován se záhlavími sloupců.
 
Zacházení s hodnotami Null u exportovaných dotazů MySQL
 

 Pojďme přidat sloupec, který přijímá Null hodnoty do naší databáze lst_projects .
 
MariaDB[(none)]> ALTER TABLE lst_projects ADD COLUMN project_status VARCHAR(15) AFTER end_date;
 

 Do tohoto nového sloupce nevložíme žádné hodnoty, abychom zajistili, že zůstane prázdný. Dotaz MySQL se exportuje pomocí Null hodnoty jsou předem zaznamenány pomocí “"N” na vygenerovaný soubor CSV. Chcete-li tento problém vyřešit, můžeme nahradit “"N” hodnotu s něčím příbuznějším, jako je “N/A” .
 
MariaDB[(none)]> (SELECT 'Název projektu', 'Datum zahájení', 'Datum ukončení', 'Stav projektu')UNION (SELECT název_projektu, datum_zahájení, datum_ukončení, IFNULL(stav_projektu, 'N/A')FROM lst_projects INTO OUTFILE '/var/tmp /with_null.csv' POLE UZAVŘENÁ '"' UKONČENO ';' UKONČENO '"' ŘÁDKY UKONČENÉ '\r\n');
 

 Pojďme zkontrolovat vygenerovaný soubor CSV.
  
Exportujte tabulky MySQL do CSV s názvem souboru s časovým razítkem
 

 Vytváří přesnější rutinu správy, pokud jde o to, kdy byly vaše soubory CSV vygenerovány.
 
MariaDB[(none)]> SET @TS =DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');SET @FOLDER ='/var/tmp/';SET @PREFIX ='lst_projects';SET @EXT ='.csv';SET @CMD =CONCAT("VYBRAT * Z lst_projects DO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,"' POLE UZAVŘENÁ '\"' UKONČENO ';' UKONČENO '\"'"," ŘÁDKY UKONČENÉ '\r\n';");PREPARE příkaz FROM @CMD;EXECUTE;
 

 Vygenerovaný soubor CSV by nyní měl mít časové razítko.
  

 Výstup vašeho MySQL dotazování výsledků do souboru CSV je efektivní způsob správy velkých datových sad, protože vám šetří čas i peníze, zejména při správě dat pro velké organizace.