LinuxでMySQLクエリ結果をCSV形式にエクスポートする方法

MySQLからのクエリ簡単にアクセスして参照できるようにデータベース出力をどこかに保存する必要があるまで、データベースシェルは常に楽しくて技術的です。特に大規模なデータセットを扱う場合。

クイックデータアクセスにより、ターミナルシェルを介してMySQLサーバーにログインするたびに、特定のMySQLクエリ関連の出力を参照する必要がなくなります。 CSV （カンマ区切り値 ）ファイルは、これらのタイプの反復的なユーザーとデータベースの相互作用を解決するための理想的な候補です。

CSVのファイル形式は、次のような顕著な属性があるため、MySQL出力の保存に最適です。

広く受け入れられているカンマ区切りのデータ保存形式です。
人間が読める形式の追加の利点。
プレーンテキストであるため、どのアプリケーションにも簡単にインポートできます。
大規模なデータセットの管理と整理における適応性。

前提条件

MySQLに関連付けられるCSVファイルクエリ出力は、ターゲットのMySQLクエリ出力の実行中に自動生成されるため、まだ存在していないはずです。
MySQLデータベースとLinuxシステムの両方でroot権限を持っている。

複数の行値を持つサンプルデータベーステーブルの作成

このチュートリアルを魅力的で理解しやすくするには、いくつかの値を持つデータベーステーブルが存在する必要があります。このチュートリアルでは、 MySQLの下にいることができますまたはMariaDB RDBMS。 MariaDB以降 MySQLのオープンソースフォークです、これら2つの RDBMS データベースシェルコマンドの同じ実装を参照してください。

MySQLにログインしますルートDBユーザーとして、または既存のデータベースユーザー資格を持つデータベース。

$ sudo mysql -u root -p

新しいデータベーステーブルをホストするための新しいデータベースを作成します。

MariaDB[(none)]> show databases;
MariaDB[(none)]> create database lst_db;
MariaDB[(none)]> use lst_db;

次に、図のようにいくつかのテーブルを使用してデータベースを作成します。

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 KEY(project_id)
);

MySQLデータベーステーブルにデータを入力する

作成したMySQLデータベーステーブルが存在することを確認しました。いくつかのデータを入力するときが来ました。

MariaDB[(none)]> show tables;
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'),
            ('Copy writing','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'),
	('Sales','ML','Anthony Luigi','2025-05-15','2025-11-20');

lst_projectsの存在を確認しましょうテーブル値。

MariaDB[(none)]> SELECT * FROM lst_projects;

MySQLクエリ結果のCSV形式へのエクスポート

一時ディレクトリ“ / var / tmp” MySQLに必要な読み取りおよび書き込み権限を与えます。これを使用して、MySQLクエリから自動生成されたすべてのCSVファイルをホストします。

MySQLクエリ結果をCSVファイル形式にエクスポートする方法はいくつかの条件によって決まります。

すべてのMySQLクエリをCSVにエクスポートする

このデータベースクエリをエクスポートするには、「 SELECT * FROM lst_projects; 」をCSVファイルに変換するには、次の方法で実装します。

MariaDB[(none)]> SELECT * FROM lst_projects
INTO OUTFILE '/var/tmp/get_all_queries.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

生成されたファイルを取得してみましょう：

MySQLテーブルをヘッダー付きのCSVにエクスポート

このアプローチにより、生成されたCSVファイルがプロフェッショナルな外観になります。

MariaDB[(none)]> (SELECT 'Project Name','Project Category','Project Manager','Start Date','End Date')
UNION 
(SELECT project_name,project_category, project_manager, start_date, end_date
FROM lst_projects
INTO OUTFILE '/var/tmp/included_column_headings.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

生成されたファイルをもう一度取得しましょう：

前述のように、MySQLクエリのCSVエクスポートは、列見出しで適切に整理されています。

エクスポートされたMySQLクエリでのNull値の処理

Nullを受け入れる列を追加しましょうデータベーステーブルの値lst_projects 。

MariaDB[(none)]> ALTER TABLE lst_projects ADD COLUMN project_status VARCHAR(15) AFTER end_date;

空のままにするために、この新しい列に値を挿入することはありません。 MySQLクエリはNullでエクスポートされます値は“"N”で事前に記録されています生成されたCSVファイル。この問題を修正するために、“"N”を置き換えることができます “N/A”のようなより関連性の高い値。

MariaDB[(none)]> (SELECT 'Project Name','Start Date','End Date','Project Status')
UNION 
(SELECT 
    project_name, start_date, end_date, IFNULL(project_status, 'N/A')
FROM
    lst_projects INTO OUTFILE '/var/tmp/with_null.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n');

生成されたCSVファイルを確認しましょう。

MySQLテーブルをタイムスタンプファイル名でCSVにエクスポート

CSVファイルがいつ生成されたかという点でより正確な管理ルーチンを作成します。

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("SELECT * FROM lst_projects INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
"  LINES TERMINATED BY '\r\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

生成されたCSVファイル名にはタイムスタンプが付いているはずです。

MySQLを出力する CSVファイルへのクエリ結果は、特に大規模な組織のデータを管理する場合に時間と費用の両方を節約できるため、大規模なデータセットを管理する効率的な方法です。