※こちらのページは技術評論社刊「WEB+DB PRESS Vol.35」に掲載された内容になります。
前々回と前回は新旧ストレージエンジンについて開拓しました。今回は、まずはMySQL5.0系の最新情報を紹介してから、MySQL 5の新機能である情報スキーマとビューについて開拓してゆきます。
情報スキーマもビューもRDBMSの世界では一般的な機能ですが、ほかのRDBMSとは異なるMySQL独自の拡張やクセなどの情報をおりまぜつつお届けしたいと思います。
今回の情報スキーマとビューの開拓は、執筆時点の5.0系最新版の5.0.24aで動作確認を行いました。開拓団流のMySQLのインストール方法は、この連載の第2回（注1）で紹介しましたので参考にしてください。
開拓の前に、5.0系の最新情報をお届けします。誌上で最後にインストールしたのが5.0.21ですので、これより新しいバージョンの情報を紹介します。
5.0.22ではセキュリティホールの修正が、5.0.24aではmysqldがクラッシュする問題の修正が行われているので、最新版の5.0.24aにバージョンアップするのをおすすめします。
ここではかいつまんでの紹介になりますので、修正点の完全な一覧はMySQL ABのサイトのChange History（注2）を参照してください。
テンポラリテーブルがクローズされないまま放置される問題が修正されました。これはバイナリログが無効になっている場合に限る問題なのですが、テンポラリテーブルの作成と削除を繰り返しているとmysqldがクラッシュしてしまいます。
長いユーザ名やホスト名を指定された場合にバッファオーバーフローが発生する可能性があったのが修正されました。
ただし、この問題を引き起こすにはSUPER権限が必要なので、緊急性の高い致命的な問題とまではいかないと思います。
5.0.24でバイナリ互換性が失われてしまったのが回復しました。具体的には、MySQLのライブラリとリンクしている各言語のドライバに影響します。この修正により、5.0.23以前と5.0.24a以降とでは互換性が保たれるようになりましたが、逆に、5.0.24にリンクしているバイナリはほかのどのバージョンとも互換性がなくなることになるので注意してください。
多くの修正がなされていますが、サービスに直結するような大きな問題の修正はないと思います。
以下の修正は、5.0.23に含まれる予定だったのですが、結局、5.0.23というバージョンはリリースされませんでした。便宜的にここ（5.0.23）で修正点を紹介しますが、これらが反映されたバージョンは5.0.24からとなります。
MySQLをバージョンアップした際に使用するmysql_upgradeコマンドが、シェルスクリプトからCプログラムになりました。これにより、Windowsのような非UNIX環境でもmysql_upgradeコマンドが使えるようになりました。
mysqlbinlogコマンドに--set-charset=charset_nameと指定すると、その出力にSET NAMES charset_nameと追加されるようになりました。
部分インデックスがついているカラムに対してMIN、MAX関数を実行した際に、カラム値全体ではなく部分インデックスがついている範囲のみで比較されていた問題が修正されました。
また、UTF-8を含むマルチバイト文字に限り、部分インデックスがついていると、SELECT文などの比較結果が期待したものにならない問題も修正されました。
オプティマイザの問題で、MySQL 3.23と比べてORDER BYやLIMITが遅かった問題が解消されました。
Linuxでスレッド実装としてNPTLを使っている場合、FLUSH TABLES WITH READ LOCKを発行するとデッドロックが発生する場合がある問題が修正されました。
mysqlコマンドの出力で、NULL値の場合に「NULL」と表示すべきところをスペースで表示してしまっていた問題が修正されました。
Shift_JISのようにコードポイントに0されましx5C（「\」）が含まれる文字エンコーディングでSQLインジェクションが発生する可能性があったのが修正されました。
5.0の最新情報を確認したところで、今回最初の開拓としてInformation Schema（情報スキーマ）を取り上げます。
情報スキーマとは、データベースやテーブルの名前やユーザ権限情報などのいわゆるメタデータへのアクセスを提供する仕組みです。
ほかのRDBMSでは、「データディクショナリ」とか「システムカタログ」と呼ばれているものと同じようなものです。
MySQL 5.0より前は、メタデータへアクセスするのにSHOW DATABASESやSHOW TABLESといったSHOW文を使っていました。
どうしてSHOW文があったのに、似たような情報スキーマという仕組みが新しく実装されたのでしょうか？その理由はいくつかあります。
情報スキーマはMySQL独自のものではなく、SQLの標準規格であるSQL:2003（ISO/IEC 9075:2003）に定められているものです。
これまでメタデータへのアクセスはRDBMSごとにその方法がばらばらで、「テーブルの一覧」を得るにしても、表1のようにRDBMSごとに異なるコマンドを覚える必要がありました。しかし、情報スキーマという標準化された方法を使えば、RDBMSが異なっても同じ方法でメタデータにアクセスできるようになるわけです。
| 表1：テーブルの一覧を得る方法 | |
| RDBMS | コマンド |
| MySQL | SHOW TABLES; |
| PostgreSQL | SELECT * FROM pg_tables; |
| Oracle | SELECT * FROM tab; |
MySQL以外でも、PostgreSQLをはじめ、情報スキーマはすでにいくつかのRDBMSで実装されています。将来的にはRDBMSが異なっても同じ方法でメタデータにアクセスできるようになるのではないかと思います。
SHOW文を使った場合、出力の加工する方法といえば、LIKE句で絞り込みができる程度でした。
一方、情報スキーマはどうでしょうか。このすぐ後に紹介しますが、情報スキーマはテーブルに対するのと同じようにSELECT文でアクセスします。これはSELECT文でできることは全てできるということを意味し、
といったことができます。
情報スキーマへのアクセスは、「INFORMATION_SCHEMA」という名前のデータベース（のようなもの）のテーブル（のようなもの）へSELECT文を発行することで行います。
「のようなもの」と書いたのは、通常のデータベースとテーブルに対応するディレクトリとファイルは作られないからです。このように物理構成の違いはありますが、MySQLの世界からは通常のデータベース、テーブルと同じようにSELECT文でアクセスすることができます。
SELECT文で参照するのに必要な権限はSHOW文と同じルールです。つまり、同じSELECT文でもユーザの権限によって得られるものが異なります。これは次の使用例のところで実例を示します。
まずは情報スキーマを使ってテーブルの一覧を表示してみましょう。
これまでは、テーブルの一覧表示にはSHOW TABLES文を使っていましたが、情報スキーマの場合は、information_schema.tablesテーブルをSELECT文で参照します。
mysqlデータベースのテーブル名の一覧を得るには、リスト1のSELECT文を発行します。GRANTされている権限が異なるユーザでこのSELECT文を実行してみたのが図1です。このように、SHOW文と同様、与えられた権限によってSELECTの結果は変わってきます。
<リスト1：テーブル名の一覧を得る>
<図1：権限によってテーブルの一覧が異なる> root@my50-1[wd]> SELECT table_name -> FROM information_schema.tables -> WHERE table_schema = 'mysql'; +---------------------------+ | table_name | +---------------------------+ | columns_priv | (省略) | user | +---------------------------+ 17 rows in set (0.00 sec) wd@my50-1[wd]> SELECT table_name -> FROM information_schema.tables -> WHERE table_schema = 'mysql'; Empty set (0.01 sec)
さてさて、これだとSHOW TABLESと大差ないわりにはタイプ数が多くて使いづらそうですね。でも「情報スキーマなんてツカエネー」と切り捨てる前に、次の使用例を見てください。
まずは、カレントデータベースのテーブルについて、主キーとそのほかのインデックスの数を数えてみます。(図2) WHERE句を調整すれば、ほかのデータベースや特定のテーブルについて調べることもできます。
<図2：インデックスの数を数える>
root@my50-1[wd]> SELECT
-> t.table_schema
-> ,t.table_name
-> ,COUNT( IF(c.column_key = 'PRI',1,NULL) ) AS primary_key
-> ,COUNT( IF(c.column_key != 'PRI',1,NULL) ) AS other_key
-> FROM information_schema.tables t
-> LEFT JOIN information_schema.columns c
-> ON t.table_schema = c.table_schema
-> AND t.table_name = c.table_name
-> AND c.column_key != ''
-> WHERE t.table_type != 'VIEW'
-> AND t.table_schema = database()
-> GROUP BY t.table_name
-> ORDER BY table_schema, table_name;
+--------------+---------------------------+-------------+-----------+
| table_schema | table_name | primary_key | other_key |
+--------------+---------------------------+-------------+-----------+
| wd | member | 1 | 0 |
| wd | team | 1 | 0 |
| wd | zipcode | 1 | 3 |
+--------------+---------------------------+-------------+-----------+
次は、情報スキーマを使って、
の一覧を表示してみます。（図3）
<図3：オブジェクトの一覧> root@my50-1[wd]> ( SELECT -> table_schema AS object_schema -> ,table_name AS object_name -> ,table_type AS object_type -> FROM information_schema.tables ) -> UNION ALL -> ( SELECT -> routine_schema -> ,routine_name -> ,routine_type -> FROM information_schema.routines ) -> UNION ALL -> ( SELECT -> trigger_schema -> ,trigger_name -> ,'TRIGGER' -> FROM information_schema.triggers ) -> ORDER BY object_schema, object_type, object_name; +--------------------+---------------------------------------+-------------+ | object_schema | object_name | object_type | +--------------------+---------------------------------------+-------------+ | information_schema | CHARACTER_SETS | SYSTEM VIEW | | information_schema | COLLATIONS | SYSTEM VIEW | (省略) | wd | member | BASE TABLE | | wd | team | BASE TABLE | | wd | hello | FUNCTION | | wd | count_member | PROCEDURE | | wd | log_member_change | TRIGGER | | wd | detailed_member | VIEW | +--------------------+---------------------------------------+-------------+
情報スキーマのちょっと手が込んだ使用例を2つあげました。いかがでしたでしょうか？ ちょっとSQLは複雑になりますが、SELECT文でメタデータにアクセスできることにより、このようにSHOW文ではできなかった問い合わせができるようになるのがおわかりいただけたと思います。このほかでは、例えば、既存のスキーマを元にテーブル定義のドキュメントを起こしたりする、といったときなどで有益ではないかと思います。
表2は情報スキーマのテーブルの一覧表です。これはMySQL 5.0.24aでのもので、ほかのバージョンでは異なっている可能性がありますので、お使いのバージョンに注意してください。
また、オフィシャルなドキュメントではないのですが、MySQL 5.0と5.1の情報スキーマのER図（注3）がありますので、情報スキーマの構成をつかむ際には参考にするとよいでしょう。
| 表2：情報スキーマのテーブル一覧 | ||
| テーブル名 | 説明 | 他の参照方法 |
| CHARACTER_SETS | 使用可能な文字コード | SHOW CHARACTER SET |
| COLLATIONS | 使用可能な照合順序 | SHOW COLLATION |
| COLLATION_CHARACTER_SET_ APPLICABILITY |
使用可能な照合順序 | SHOW COLLATIONの最初の2カラム |
| COLUMNS | 各テーブルのカラム | SHOW COLUMNS |
| COLUMN_PRIVILEGES | カラムの権限情報 | mysql.columns_priv |
| KEY_COLUMN_USAGE | カラムの制約の情報 | |
| ROUTINES | ストアドプロシージャなどの情報 | mysql.proc |
| SCHEMATA | データベースの情報 | SHOW DATABASES |
| SCHEMA_PRIVILEGES | データベースの権限情報 | mysql.db |
| STATISTICS | インデックスの情報 | SHOW INDEX |
| TABLES | テーブルの情報 | SHOW TABLES |
| TABLE_CONSTRAINTS | テーブルの制約の情報 | |
| TABLE_PRIVILEGES | テーブルの権限情報 | mysql.tables_priv |
| TRIGGERS | トリガの情報 | SHOW TRIGGERS |
| USER_PRIVILEGES | ユーザの権限情報 | mysql.user |
| VIEWS | ビューの情報 | SHOW CREATE VIEW |
今回2つめはビューを開拓します。
ビューとは、SELECT文の結果をあたかもひとつのテーブルのように見せる機能です。実体のテーブルを実テーブル（実表）というのに対し、ビューを仮想テーブル（仮想表）といったりもします。
ご存じの方も多いと思いますが、ビューはほかの多くのRDBMSではあたりまえのように実装されている機能です。「MySQLも5.0になってようやく使えるようになったか」という感がありますね。
一般的にみた、ビューの使いどころを2点あげてみます
先ほどのべたようにビューはSELECT文の結果をテーブルのように見せる機能です。これをうまく使うと、ビューを通して複雑なSELECT文（例えば、WHERE句に多数の条件をともなうものや複数の実テーブルを結合するものなど）を簡単に発行することができます。これが使いどころその1です。
続いて使いどころその2です。
ビューを参照するには、そのビューに対する参照権限を持っている必要があります。ここで注意してほしいのは、必要なのはビューに対する参照権限であって、ビューを構成する実テーブルに対する参照権限ではないということです。
これをうまく使うと、実テーブルには直接SELECTできないが、ビューを通して特定の条件を満たす行のみのアクセスを許可する、といったアクセス制限をすることができます。
では早速、簡単なビューを作って試してみましょう。
ビューの元となる実テーブルは、表3と表4のようなmemberテーブルとteamテーブルとします。
| 表3：memberテーブル | |||
| id | name | age | team_id |
| 1 | ichirou | 18 | 10 |
| 2 | jirou | 25 | 20 |
| 3 | saburou | 27 | 10 |
| 4 | shirou | 19 | 20 |
| 5 | gorou | 32 | 20 |
| 表4：memberテーブル | |
| id | name |
| 10 | usagi |
| 20 | kame |
| 30 | hitsuji |
ビューを作るにはCREATE VIEW文を使います。
ここでは、memberテーブルとteamテーブルを結合したdetailed_memberという名前のビューを作ってみます。では、リスト2のCREATE VIEW文を実行してみましょう。
あれれ。エラーが発生してしまいました。（図4）
<リスト2：detailed_memberビュー>
<図4：CREATE VIEWを発行> wd@my5-1[wd]> CREATE OR REPLACE VIEW detailed_member AS (省略) ERROR 1142 (42000): CREATE VIEW command denied to user 'wd'@'my50-1' for table 'detailed_member'
実は、ビューを作るには「CREATE VIEW」という権限が必要なのですが、この権限が与えられていないとこのようなエラーが発生します。
では気をとりなおして、権限を与えて（リスト3）、接続しなおして、再度、リスト2のCREATE VIEW文をします。
<リスト3：CREATE VIEW権限の付与>
これでdetailed_memberビューが作られたと思います。試しにSELECTしてみましょう。（図5）memberテーブルとteamテーブルが結合された結果が得られるはずです。また、図5の実行例のように、ビューの場合でもWHERE句で絞り込みをすることができます。
<図5：ビューをSELECTする>
wd@my50-1[wd]> SELECT * FROM detailed_member;
+----+---------+------+---------+-----------+
| id | name | age | team_id | team_name |
+----+---------+------+---------+-----------+
| 1 | ichirou | 18 | 10 | usagi |
| 2 | jirou | 25 | 20 | kame |
| 3 | saburou | 27 | 10 | usagi |
| 4 | shirou | 19 | 20 | kame |
| 5 | gorou | 32 | 20 | kame |
+----+---------+------+---------+-----------+
wd@my50-1[wd]> SELECT * FROM detailed_member WHERE age >= 20;
+----+---------+------+---------+-----------+
| id | name | age | team_id | team_name |
+----+---------+------+---------+-----------+
| 2 | jirou | 25 | 20 | kame |
| 3 | saburou | 27 | 10 | usagi |
| 5 | gorou | 32 | 20 | kame |
+----+---------+------+---------+-----------+
ビューが使い方を覚えたので、次はビューの情報を見る方法を紹介します。
ビューの定義（CREATE VIEW文）を見る方法は2つあります。information_schema.viewsを参照する（図6）か、SHOW CREATE VIEW文を使う（図7）かです。
どちらも実行するには「SHOW VIEW」権限が必要です。たとえ、自分で作ったビューでもこの権限がないとエラーが発生してしまうので、GRANTするのを忘れないようにしましょう。
<図6：ビューの定義を見る（1）>
wd@my50-1[wd]> SELECT *
-> FROM information_schema.views
-> WHERE table_name = 'detailed_member'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: wd
TABLE_NAME: detailed_member
VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */
select `m`.`id` AS `id`,`m`.`name` AS `name`,`m`.`age` AS `age`,`t`.
`id` AS `team_id`,`t`.`name` AS `team_name` from (`wd`.`member` `m` left join `wd`.
`team` `t` on((`m`.`team_id` = `t`.`id`)))
CHECK_OPTION: NONE
IS_UPDATABLE: NO
DEFINER: wd@%
SECURITY_TYPE: DEFINER
<図7：ビューの定義を見る（2）>
wd@my50-1[wd]> SHOW CREATE VIEW detailed_member\G
*************************** 1. row ***************************
View: detailed_member
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`wd`@`%` SQL SECURITY DEFINER VIEW
`detailed_member` AS select `m`.`id` AS `id`,`m`.`name` AS `name`,`m`.`age` AS `age`,`t`.
`id` AS `team_id`,`t`.`name` AS `team_name` from (`member` `m` left join `team` `t` on
((`m`.`team_id` = `t`.`id`)))
もうひとつ覚えておいた方がよい構文があります。それはCHECK TABLESです。
「ビューなのに『TABLES』なの？」と思うかもしれませんが、名前の是非はおいておきましょう。CHECK TABLESは、対象がテーブルではなくビューの場合は、ビューに関する情報を表示してくれます。
どんな情報を表示してくれるかというと、CHECK TABLESでそのビューが正しく機能しているかどうかが確認できます。
具体例をあげましょう。CHECK TABLESを実行すると図8のように出力されます。各カラムの意味は表5の通りです。ここで試しにteamテーブルをDROPして、ビューの構成を破壊してから再度CHECK TABLESを実行すると、図9のようにエラーメッセージが表示され、ビューが期待したように機能していないことが確認できます。
<図8：CHECK TABLES（正常な場合）>
wd@my50-1[wd]> CHECK TABLES detailed_member\G
*************************** 1. row ***************************
Table: wd.detailed_member
Op: check
Msg_type: status
Msg_text: OK
| 表5：CHECK TABLESのカラム | |
| カラム | 説明 |
| Table | ビューの名前です。 |
| Op | 常に「check」です。 |
| Msg_type | 「status」「error」「info」「warning」のいずれかです。 |
| Msg_text | Msg_typeに応じたメッセージです。正常時は「OK」です。 |
<図9：CHECK TABLES（異常な場合）>
wd@my50-1[wd]> CHECK TABLES detailed_member\G
*************************** 1. row ***************************
Table: wd.detailed_member
Op: check
Msg_type: error
Msg_text: View 'wd.detailed_member' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use them
MySQLのビューには制限事項があります。重要と思われるものをいくつかを紹介します。
ビューの定義のSELECT文のFROM句ではサブクエリは使えません。例えばリスト4の文を実行すると、図10のエラーメッセージが表示されます。
一方で、リスト5のようなWHERE句でのサブクエリは使用できます。
このサブクエリの制限ですが、MySQL ABのドキュメントによれば、将来的にはなくなりFROM句でもサブクエリが使えるようになるだろうとされています。
<リスト4：FROM句にサブクエリを使ったビュー>
<図10：FROM句にサブクエリを使った場合のエラー>
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
<リスト5：WHERE句にサブクエリを使ったビュー>
ビュー定義のSELECT文で、システム変数やユーザ変数を参照することはできません。リスト6はシステム変数@@global.versionを参照するSELECT文を使ったビュー定義ですが、これを実行すると図11のエラーが発生します。
<リスト6：変数を参照したビュー>
<図11：変数を参照した場合のエラー>
ERROR 1351 (HY000): View's SELECT contains a variable or parameter
ビューのカラムにはインデックスを作成できません。
しかし、ビューを構成する実テーブルのインデックスは、ビューが参照される際に利用されます。
ビューは参照できるだけでなく、ビューに対してUPDATE文やINSERT文を発行することにより、ビューを構成する実テーブルを更新することもできます。
ただし、全てのビューが更新可能というわけではなく、UPDATE可能なビューとINSERT可能なビューとでいくつかの条件があります。
UPDATE可能なビューの条件は、ビューの結果と実テーブルの行が1対1に対応する場合です。
代表的なこの条件の反例は、集約関数を使ったビューです。
例えばSUM()を使ったビューを考えると、UPDATE文に与えるのは合計された値になるわけで、これをどのように実テーブルの行に分解していいかはわかりませんね。
まとめると、次のものが含まれている場合はUPDATE可能ビューにはなりません。
INSERT可能なビューは、UPDATE可能なビューの条件に加え、以下の条件を満たしている必要があります。
今回は情報スキーマとビューについて開拓しました。
情報スキーマはSHOWコマンドに比べると複雑になりますが、SELECT文で参照できるため、さまざまな用途に応用がききます。よく使う情報スキーマのSELECT文は常にポケットに忍ばせておいて、ここぞというときにさっと取り出せるとステキですね。
ビューは特にMySQL固有の機能拡張はなく、オーソドックスに実装された感があります。とはいえビュー自体は非常に便利な機能ですので、今後、効果的に使っていきたい機能ですね。
MySQLでCUIのモニタプログラムというと、mytop（注A）が有名ですが、2003年以来バージョンアップされておらず、Vol.32の本連載でも触れたように、そのままではMySQL 5では正常に動作しません。
こういう状況に業を煮やした方がいて、新しいモニタプログラムが誕生しました。それがinnotop（注B）です。
名前だけ見るとInnoDBに特化したツールの印象を受けますがさにあらず。次にあげるような豊富な機能を持っていて、もちろん、mytopの機能もカバーしています。
最近の筆者イチオシのツールです。
