【MySQL】SQLの実行結果をファイル出力する方法とは？！

SQLの実行結果は、通常ターミナルやSQLクライアントに表示されますが、ファイルに出力したいケースも存在します。

本記事では、MySQLを使用してSQLの実行結果をファイル出力する方法についてご紹介していきたいと思います。

ポテパンダの一言メモ

今回ご紹介するコマンドはMac環境でMySQLを利用した際のコマンドです。
Windows環境やMySQL以外のデータベースをご利用の方は適宜それぞれの環境に対応したコマンドに読み替えてみてください。

ターミナルからSQLの実行結果をファイル出力

まずはターミナルからSQLの実行結果をファイル出力する方法についてご紹介していきます。

リダイレクト機能を使用して、ファイル出力することが可能です。

SQL文を直接記述する

SQL文を直接記述する際の基本構文は下記の通りです。

mysql -u [ユーザー名] -p [データベース名] -e 'SQL文' > [ファイルパス]

サンプルでは、「test_db」というデータベースの「user」テーブルのデータを取得し、「/Users/[ユーザー名]/Documents/sql」フォルダに「sql_output1.txt」というファイル名で出力しています。

mysql -u root -p test_db -e 'SELECT * FROM user;' > /Users/[ユーザー名]/Documents/sql/sql_output1.txt

ターミナルで実行するとパスワードの入力を求められますので、MySQLに設定したログイン用のパスワードを入力します。

処理が完了すると下記の画像のようにターミナル上には何も表示されませんが問題ありません。

出力パスとして指定したファイルを「cat」コマンドで確認してみましょう。

cat /Users/[ユーザー名]/Documents/sql/sql_output1.txt

実行すると「user」テーブルの内容がファイルに出力されていることをご確認頂けます。

SQLファイルから実行する

上記の直接SQL文を記述する以外にも、SQLファイルから実行した結果をファイル出力することも可能です。

mysql -u [ユーザー名] -p [データベース名]  [ファイルパス]

サンプルでは、「test.sql」と言うSQLファイルを作成し、userテーブルから「id=1」のデータのみを抽出して「sql_output2.txt」ファイルに出力しています。

mysql -u root -p test_db  /Users/[ユーザー名]/Documents/sql/sql_output2.txt

実行した結果のターミナル表示は、直接SQL文を指定した場合と変わりません。

では出力パスとして指定したファイルを「cat」コマンドで確認してみましょう。

cat /Users/[ユーザー名]/Documents/sql/sql_output2.txt

SQLファイルを実行した結果が、アウトプット出来ていることをご確認頂けます。

SQLファイルには下記のコマンドを記述しています。

SELECT *
FROM user
WHERE id = 1;

オプション指定

上記サンプルをご確認頂くと、MySQLクライアントで結果を出力した場合のように表形式で出力されていないことが分かります。

表形式で出力するためには「-t」オプションを付与する必要があります。

また、合わせて「-N」オプションのフィールド名を出力しない出力方法についても確認しておきましょう。

mysql -t -N -u [ユーザー名] -p [データベース名]  [ファイルパス]

実際にサンプルで動きを確認してみましょう。

mysql -t -N -u root -p test_db -e 'SELECT * FROM user;' > /Users/[ユーザー名]/Documents/sql/sql_output3.txt

出力したファイルを確認してみると、表形式に変更された上で、フィールド名が出力されていないことをご確認頂けます。

MySQLクライアントからSQLの実行結果をファイル出力

次にMySQLクライアントからSQLの実行結果をファイル出力する方法についてご紹介していきます。

ここまでは、ターミナルから直接MySQLへのログインを含めた実行コマンドを入力して実行していました。

ここからは、MySQLへログインした状態での説明となります。

まずは下記のコマンドでMySQLへログインしておきましょう。

mysql -u [ユーザー名] -p

さらにUSEコマンドで利用するデータベースを選択しておいてください。

USE [データベース名]

ファイル出力

MySQLクライアントでファイル出力するには下記のコマンドを利用します。

[SELECT文] INTO OUTFILE [ファイルパス];

では実際にファイル出力してみましょう。

サンプルでは上記のターミナルから実行した際に、出力したフォルダと同じ階層に出力パスを指定しています。

SELECT * FROM user INTO OUTFILE '/var/tmp/sql_output4.txt';

一度MySQLクライアントからログアウトして、出力されたファイルの内容を確認してみましょう。

SQL文で指定した結果がファイルに出力されていることをご確認頂けます。

注意して頂きたいのが、今回の主旨と少し外れるため詳細な解説は省きますが、MySQLクライアントからファイル出力する場合、出力パスによっては、書き込み権限に応じて下記のような出力エラーが起きることがあります。

ERROR 1 (HY000): Can't create/write to file '/Users/[ユーザー名]/Documents/sql/sql_output4.txt' (OS errno 13 - Permission denied)

権限に関するエラーが出た場合には、MySQLユーザーが書き込み権限を保持していることはもちろん、出力先ファイルのファイル書き込み権限の変更などを行う必要があります。

オプション指定

上記のサンプルでは、出力結果としてタブ区切りがデフォルトとなっていますが、カンマ区切りなどに変更することも可能です。

[SELECT文] INTO OUTFILE [ファイルパス]　FIELDS TERMINATED BY ',';

「FIELDS TERMINATED BY」で区切り文字を指定し、「LINE TERMINATED BY」で改行コードを指定することが可能です。

実際に上記サンプルに当てはめてみると下記のようなコマンドになります。

SELECT * FROM user INTO OUTFILE '/var/tmp/sql_output5.txt' FIELDS TERMINATED BY ',';

ログアウトして出力されたファイルを確認してみましょう。

カンマ区切りで出力されていることがご確認頂けます。

さいごに：SQLの実行結果をファイル出力して利用するケースは多い！

本記事では、SQLの実行結果をファイル出力する方法について、Mac環境でMySQLを利用するケースをサンプルとして挙げながらご紹介してきました。

実務において、SQLの実行結果をファイルとして欲しいといった要望は意外と多いものです。

SQLでのファイル出力方法を知らなかった方は、この機会にぜひ使い方を覚えてみてください。