MySQLに限らずRDBMSを利用・運用していると、よくDBのデータを変換したくなることがあると思います。その際に、クライアントから人手で実行すると、オペレーションのミスやコピペのミスなどで問題が発生してしまうことがあります。また、複数行に渡りSQLを実行したい場合などにTransaction処理を利用して作業を行う際、ロック待ちの時間をなるべく少なくしたいといった要望もあるかと思います。

そこで今回は、第51回　MySQLのクライアントプログラムいろいろ［その1］や第77回　mysqlコマンドラインクライアントで紹介されたmysqlコマンドラインクライアントを使って、バッチ的に処理を行う方法を紹介したいと思います。

検証環境

今回は、macOS（High Sierra）上にhomebrewを利用してインストールを行ったMySQL（8.0.16）を利用して検証を行っています。テストデータは第2回　MySQLにはじめてのデータを入れてみるで紹介されている「サンプルデータその3、KEN_ALL.CSV」のデータを利用して行いました。

バッチモードでSQLを実行してみる

それでは以下のようなSQLを実行してみたいと思います。今回は単純に、渋谷区に該当する郵便番号のデータを取得するSQLとなっています。

$ cat shibuya.sql
select  zip_code from zipcode where city like "%渋谷区%" order by zip_code;

mysqlコマンドラインクライアントでログインした状態で実行をしてみると、以下のような結果が得られます。

$ mysql -uroot zipcode
mysql> select zip_code from zipcode where city like "%渋谷区%";
+----------+
| zip_code |
+----------+
| 1500000  |

 ～省略～

| 1510073  |
+----------+
73 rows in set (0.06 sec)

続いてバッチモードでSQLを実行していきます。バッチモードで実行する場合には、以下のようにmysqlクライアントでいつもどおりにログインをして、最後にリダイレクトで先ほど作成したshibuya.sqlファイルを指定します。実際に動かしてみましょう。

$ mysql -uroot zipcode < shibuya.sql
zip_code
1500000

～省略～

1510073

このようにファイルを指定することで、mysqlクライアントにファイルに書かれたSQLが入力として渡り、実行されたことがわかりました。

バッチモードで動かした際には、少し簡潔な表記になっていることがわかると思います。複数の列がある場合にはタブ区切りのTSVとなっていて、取得してきた値を活用しやすい形で出力されます。今度は複数の要素を取得する以下のようなSQLをshibuya2.sqlとして実行して確認してみましょう。

$ vim shibuya2.sql
$ mysql -uroot zipcode < shibuya2.sql
code    old_zipcode     zip_code        prefecture_kana city_kana       town_kana       prefecture      city    town
13113   150             1500000         ﾄｳｷｮｳﾄ  ｼﾌﾞﾔｸ                   ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ 東京都          渋谷区  以下に掲載がない場合

～省略～

13113   151             1510073         ﾄｳｷｮｳﾄ  ｼﾌﾞﾔｸ                   ｻｻﾂﾞｶ           東京都          渋谷区  笹塚

また、現在はコンソールに出力されていると思いますが、こちらはリダイレクトを利用することでファイルに出力することや、出力が長くなる場合にパイプを利用してmoreやless等を利用することもできます。

$ mysql -uroot zipcode < shibuya.sql > result.tsv
$ cat result.tsv
zip_code
1500000

～省略～

1510073

バッチモードで実行した場合にも、普段mysqlクライアントでログインした後の罫線で囲まれた出力が欲しい場合もあるかと思います。その場合は別途-t（--table）オプションを指定することで、バッチモードでも罫線を表示ができます。このようにmysqlクライアントのオプションを組み合わせて利用することができるので、活用していきましょう。

$ mysql -uroot -t zipcode < shibuya.sql
+----------+
| zip_code |
+----------+
| 1500000  |

～省略～

| 1510073  |
+----------+

逆にクライアントで接続してSQLを実行する時に、バッチモードと同じようにタブ区切り文字で表示したい場合には、ログイン時にmysqlクライアントに-B（--batch）オプションを指定することで、表示を変更することができます。

$ mysql -uroot -B zipcode
select  zip_code from zipcode where city like "%渋谷区%" order by zip_code;
zip_code
1500000

～省略～

1510073
exit
$

また、ファイルからという趣旨から少し外れてはしまうのですが、第51回　MySQLのクライアントプログラムいろいろ［その1］でも説明があるように、-e(--execute)オプションを利用することで指定を行ったSQLを実行することもできます。こちらも-Bオプションと組み合わせるとTSVでファイルを取得できるので便利に扱えます。

$ mysql -uroot -B -e 'select  zip_code from zipcode where city like "%渋谷区%" order by zip_code;' zipcode
zip_code
1500000

～省略～

1510073

その他にも便利なものとして、-vvvオプションや--forceオプションなどがあります。-vvvオプションは実行したSQLをエコーしてくれるオプションです。出力された結果がどんなSQLによって生成されているのかがひと目でわかるようになるので、バッチで動かす際などには考慮にいれても良いかもしれません。ただし、上述の-Bオプションを付けてもTSVで出力することができないのは注意が必要です。

$ mysql -uroot -vvv zipcode < shibuya.sql
--------------
select zip_code from zipcode where city like "%渋谷区%" order by zip_code
--------------

+----------+
| zip_code |
+----------+
| 1500000  |

～省略～

| 1510073  |
+----------+
73 rows in set (0.06 sec)

--forceオプションですが、こちらはファイル内にミスが有った場合でも実行が継続されるオプションになります。以下のようなSQLを作成して実行してみましょう。

$ cat shibuya3.sql
select machigai from zipcode;
select zip_code from zipcode where city like "%渋谷区%" order by zip_code;

この例では1行目のmachigaiカラムが存在しないので、何もオプションを付けずに実行をすると、以下のようにエラーになってその行で実行が止まります。

$ mysql -uroot  zipcode < shibuya3.sql
ERROR 1054 (42S22) at line 1: Unknown column 'machigai' in 'field list'
$

しかし--forceオプションを組み合わせると、エラーが発生しても実行が継続されます。

$ mysql -uroot --force zipcode < shibuya3.sql
ERROR 1054 (42S22) at line 1: Unknown column 'machigai' in 'field list'
zip_code
1500000

～省略～

1510073
$

あまりお行儀が良いとは言えないのですが、役に立つこともあるかもしれません。

接続完了後にクライアントからファイルを実行してみる

今回はmysqlクライアントで接続が完了した後に実行してみましょう。クライアントが接続済みの場合はsourceまたは\.コマンドを利用することで、SQLをファイルから実行できます。前項で作成したSQLファイルを実行してみましょう。

mysql> source shibuya.sql
+----------+
| zip_code |
+----------+
| 1500000  |

～省略～

| 1510073  |
+----------+
73 rows in set (0.05 sec)

以上のように、すでに接続済みのクライアントからでもファイルに書いたSQLを実行できます。

まとめ

今回はファイルに書いたSQLを実行する方法に関して紹介させていただきました。

Webアプリケーションなどでバッチ用の環境などがすでに提供されている場合はそちらを利用したほうが良いとは思いますが、そういった環境がない場合に、一時的に集計処理を行うにはかなり便利なのではないかと思います。また、それ以外でもインフォメーションスキーマやパフォーマンススキーマなどの状態を監視する目的などに活用することができます。

この他にもSQLをファイルの状態にしておくと、可搬性が高くなったりレビューも行いやすいですので、運用に関わるSQLをファイルにしておくと良いかもしれません。