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となっています。
mysqlコマンドラインクライアントでログインした状態で実行をしてみると、以下のような結果が得られます。
続いてバッチモードでSQLを実行していきます。バッチモードで実行する場合には、以下のようにmysqlクライアントでいつもどおりにログインをして、最後にリダイレクトで先ほど作成したshibuya.sql
ファイルを指定します。実際に動かしてみましょう。
このようにファイルを指定することで、mysqlクライアントにファイルに書かれたSQLが入力として渡り、実行されたことがわかりました。
バッチモードで動かした際には、少し簡潔な表記になっていることがわかると思います。複数の列がある場合にはタブ区切りのTSVとなっていて、取得してきた値を活用しやすい形で出力されます。今度は複数の要素を取得する以下のようなSQLをshibuya2.sql
として実行して確認してみましょう。
また、現在はコンソールに出力されていると思いますが、こちらはリダイレクトを利用することでファイルに出力することや、出力が長くなる場合にパイプを利用してmoreやless等を利用することもできます。
バッチモードで実行した場合にも、普段mysqlクライアントでログインした後の罫線で囲まれた出力が欲しい場合もあるかと思います。その場合は別途-t
(--table
)オプションを指定することで、バッチモードでも罫線を表示ができます。このようにmysqlクライアントのオプションを組み合わせて利用することができるので、活用していきましょう。
逆にクライアントで接続してSQLを実行する時に、バッチモードと同じようにタブ区切り文字で表示したい場合には、ログイン時にmysqlクライアントに-B
(--batch
)オプションを指定することで、表示を変更することができます。
また、ファイルからという趣旨から少し外れてはしまうのですが、第51回 MySQLのクライアントプログラムいろいろ[その1]でも説明があるように、-e
(--execute
)オプションを利用することで指定を行ったSQLを実行することもできます。こちらも-B
オプションと組み合わせるとTSVでファイルを取得できるので便利に扱えます。
その他にも便利なものとして、-vvv
オプションや--force
オプションなどがあります。-vvv
オプションは実行したSQLをエコーしてくれるオプションです。出力された結果がどんなSQLによって生成されているのかがひと目でわかるようになるので、バッチで動かす際などには考慮にいれても良いかもしれません。ただし、上述の-B
オプションを付けてもTSVで出力することができないのは注意が必要です。
--force
オプションですが、こちらはファイル内にミスが有った場合でも実行が継続されるオプションになります。以下のようなSQLを作成して実行してみましょう。
この例では1行目のmachigaiカラムが存在しないので、何もオプションを付けずに実行をすると、以下のようにエラーになってその行で実行が止まります。
しかし--force
オプションを組み合わせると、エラーが発生しても実行が継続されます。
あまりお行儀が良いとは言えないのですが、役に立つこともあるかもしれません。
接続完了後にクライアントからファイルを実行してみる
今回はmysqlクライアントで接続が完了した後に実行してみましょう。クライアントが接続済みの場合はsource
または\.
コマンドを利用することで、SQLをファイルから実行できます。前項で作成したSQLファイルを実行してみましょう。
以上のように、すでに接続済みのクライアントからでもファイルに書いたSQLを実行できます。
まとめ
今回はファイルに書いたSQLを実行する方法に関して紹介させていただきました。
Webアプリケーションなどでバッチ用の環境などがすでに提供されている場合はそちらを利用したほうが良いとは思いますが、そういった環境がない場合に、一時的に集計処理を行うにはかなり便利なのではないかと思います。また、それ以外でもインフォメーションスキーマやパフォーマンススキーマなどの状態を監視する目的などに活用することができます。
この他にもSQLをファイルの状態にしておくと、可搬性が高くなったりレビューも行いやすいですので、運用に関わるSQLをファイルにしておくと良いかもしれません。