MySQLで、サブクエリーの結果を共通で使えるwith句が使えるのをご存じでしょうか。しかし、コマンドラインのMySQLクライアントツールで、with句を含む複雑なSQLのデバックは容易ではありません。GUIな管理ツールが必要です。そこで、比較的簡単に使えるMySQLの管理ツール、phpMyAdminを使ったwith句を含むSQLの実行手順を紹介します。
SQLにおけるwith句とは
Oracle SQLやDB2といったエンタープライズ用途向けのSQLに慣れたベテランエンジニアなら、複数のサブクエリーを使う複雑なSQLでも、with句を使ってスマートに書いています。しかし、以前のMySQLではwith句が使えませんでした。しかし、最新のMySQLなら、withが使えます。
エンタープライズ用途向けのSQLをフリーのデータベースで利用するなら、ぜひ、MySQLのバージョン8以降の利用を検討してください。MySQLのバージョン8では、Oracle SQLやDB2などのエンタープライズ用途向けだった多くの機能が導入されており、with句も使えるようになりました。まずは、MySQLで使えるようになったwith句について解説します。
SQLのwith句とは
with句は、1つのSQL文の中で共通したテーブルを簡略的に呼び出す機能です。そして、with句で呼び出すテーブルのことを、再帰共通表式（テーブル）と呼びます。
なお、with句がよく使われるのはサブクエリーです。サブクエリーとは、クエリーの中でデータベースに格納されたテーブルの代わりに、クエリーによって作られたテーブルを使うための機能で、SQLの中に幾つものサブクエリーを記述することが可能です。
そのため、サブクエリーが入れ子になるなど、複雑なSQLが使われるケースがあり、その中には同じサブクエリーを幾つも使っていることも珍しくありません。そのような場合、同じサブクエリーをwith句で共通化することで、複雑なSQLをシンプルに書くことが可能です。
MySQLの8.0以降ではwith句が使える
ただし、with句は全てのデータベース管理ソフトで使えた訳ではありません。MySQLのバージョン5では、使えませんでした。しかし、MySQLのバージョン8にからは、それまで使えなかったエンタープライズ向けの機能が使えるようになりました。そして、使えるようになった機能の一つが、再帰共通表式（テーブル）を定義するwith句です。
なお、MySQLは、開発していた会社がエンタープライズ向けデータベース管理ソフトで有名なOracleに買収されたことで、Oracle社が開発するMySQLとオープンソースのmariadbに分かれました。残念ながらオープンソースのmariadbの最新版は、MySQLの最新版との完全な互換性はありません。とはいえ、mariadb最新版なら、with句をサポートしています。ぜひ、そちらのも利用も検討してください。
with句の使用例
次にwith句の使用例を紹介します。
with句を使ったSQLの例 with w1 as ( select * from score ) select T1.mon, T1.name, T1.subtotal, ( select avg(w1.subtotal) from w1 ) as avg, ( select max(w1.subtotal) from w1 ) as max from score T1;
この例では、１行目のwith句でサブクエリーを宣言しています。そして、その検索結果のw1を使って平均値と最大値を計算し、テーブルに格納されたデータといっしょに出力します。
上記のSQLをwith句を使わないで書いた場合、次のようになります。
サブクエリーのみで書いたSQLの例 select T1.mon, T1.name, T1.subtotal, ( select avg(sub.subtotal) from ( select * from score ) as sub ) as avg, ( select max(sub.subtotal) from ( select * from score ) as sub ) as max from score T1;
with句を使うことで、サブクエリーを共通化できることから、簡潔に書くことが可能です。
MySQLを試すならphpMyAdminが最適
MySQLに限らず、SQLが使えるデータベース管理ソフトを学ぼうとしたら、以前は、コマンドラインから専用のクライアントツールを実行するのが一般的でした。具体的には、MySQLならmysqlコマンドです。
しかし、mysqlコマンドは、使いやすいとは言えません。MySQLを学ぶなら、格納されたデータが見やすく、SQLを実行できるグラフィカルな管理システムの利用をお勧めします。中でも無料で比較的簡単に使えるのがphpMyAdminです。次から、phpMyAdminを使ってSQLを実行する方法についてご紹介します。
phpMyAdminはWebブラウザで使う
phpMyAdminは、Webサービスの構築によく利用されるプログラム言語のPHPで実装されたMySQL専用の管理ツールです。そして、主にLinuxで構築されたWebサーバーに設置して利用します。また、WordPressが使えるレンタルサーバーでは標準で使えることが多いことから、身近なMySQLの管理ツールと言えるでしょう。
なお、phpMyAdminを利用するには、WebブラウザからphpMyAdminのURLを指定して、MySQLのアカウントでログインする必要があります。ただし、Webブラウザで利用することから、通信できるデータの量に制限があり、全てのデータが表示できなかったり、大きなテーブルをファイル書き出せない、などの制限があります。
phpMyAdminでSQLを実行する
phpMyAdminはデータベースに格納されたデータを直接編集する機能の他、SQLを実行し、その結果を表示する機能も備えています。
SQLを実行するには、phpMyAdminにログイン後、左側のタブでデータベースを選択して、右側の「SQL」タグを選んでください。画面が切り替わり、SQLを実行するための画面が表示されます。そこに、SQLを書くか、メモ帳などのテキストエディタに書いたSQLをコピーしてください。そして、実行ボタンを押すと、SQLが実行されます。その実行結果は、テーブル形式で表示されます。
ただし、SQLを書くエリアは、SQL専用のエディタではありません。さらにファイルに保存する機能の無いので、with句を含むSQLのようなサブクエリーを幾つも使ったSQLのデバックには向いていません。そのため、別のエディタでSQLを編集に、コピーして実行するという使い方をおすすめします。
SQLのwith構文をphpMyAdminで試してみる
次から、MySQLでも使えるようになったwith構文を使ったSQLを、phpMyAdminのSQL実行環境で実行する手順を紹介します。
まずはデータベースを選択する
phpMyAdminにログインすると左側にデータベースの一覧が表示され、右側にシステム情報などが表示されます。しかし、この状態では、まだ、データベースが選択されていません。SQLの編集画面を表示できますが、この状態でSQLを書き、実行ボタンを押してもエラーになるので注意しましょう。
まずは、操作対象となるデータベースを選んでください。データベースを選ぶと、画面右側の上部に、サーバー名をデータベース名が表示されます。必ずこの状態で、SQLタブで画面を切り替えてください。
SQLを書いて実行する
phpMyAdminのSQLタブをクリックして切り替えた画面には、SQLを編集するエリアと、各種スイッチなどが表示されます。もちろん、ここでSQLをゼロから書くことも可能ですが、ファイルに保存する機能がありません。あらかじめテキストエディタで作っておき、ファイルに保存してからコピーするようにしましょう。
SQLが書けたら画面下部にある実行ボタンを押してください。SQLの実行結果が表示されます。また、SQLにエラーがあれば、コンソールに表示されるのと同じメッセージが表示されるので、SQLを修正して再度実行してください。
phpMyAdminでのデバック方法
今回実行するwith構文を含んだ複雑なSQLは、予定している結果が出るまでに何度もSQLを書き直すのが一般的です。そういったSQLのデバックにphpMyAdminのSQL実行環境を利用する場合は、「クエリボックスを保持する」スイッチを利用し、SQLと実行結果を同時に表示する機能を利用しましょう。
また、どうしてもエラーが見つけられない場合は、フォーマットボタンを利用してSQLを整形してみてください。間違いが見つけられやすくなります。
さらにSQLの実行結果が大きい場合は、「クリップボードにコピー」を利用しましょう。これを使って表計算ソフトなどにコピーすれば、チェックが簡単になります。
まとめ
MySQLのwith句は、複雑なSQLを解り易く作れる便利な機能です。しかし、それをコマンドラインでデバックするのは、簡単ではありません。それなら、比較的簡単に使えるMySQLの管理ツールのphpMyAdminを活用しましょう。phpMyAdminは、データベース管理ソフトとしては機能が少なく制限も多いものの、無料で設置が容易などのメリットがあり、SQLのデバックなどにも使えます。ぜひ、phpMyAdminを利用して、複雑なwith構文を含むSQLをマスターしましょう。
