※こちらのページは技術評論社刊「WEB+DB PRESS Vol.36」に掲載された内容になります。
前回の情報スキーマとビューに続き、今回もMySQL 5の新機能、
を開拓していきたいと思います。ストアドプロシージャとストアドファンクションはなにかと似ているところがあり、両方まとめて言及するときはMySQL ABのドキュメントにならって「ストアドルーチン」と呼ぶことにします。
開拓に入る前に、5.0系の最新情報を軽くお届けします。
執筆時点の最新版は5.0.27です。が、ちょっと紆余曲折がありました。
まず、5.0.26がリリースされました。（5.0.25はコマーシャルライセンスのユーザのみに公開されたようです）しかし後日、5.0.26に互換性の問題が発見されたため、5.0.27と5.0.28が公開されました。5.0.27と5.0.28の中身は同じですが、次で述べるようにライセンスが異なります。
詳しい変更履歴は、MySQL KKの松信氏の記事やMySQL ABのドキュメントを参照してください。
MySQL ABは、MySQL EnterpriseとCommunity Serverというものを発表しました。詳しくは（これまた）松信氏のレポートとMySQL ABのサイトを参照して欲しいのですが、かいつまむと
ということのようです。
では開拓に入りましょう。まずはストアドルーチン（ストアドプロシージャ＋ストアドファンクション）です。
ストアドルーチンとは、SQL文を使った一連の処理をひとつの塊とみたてて名前をつけ、サーバに保存（store）しておくものです。そして、その名前を呼ぶだけで一連の処理を実行することができます。
では、ストアドプロシージャとファンクションとでは何が違うかというと、呼び出す方法が違います。
プロシージャの呼び出しにはCALL文を使います。そして、プロシージャとの入出力は、CALLするときに指定した変数を介してやりとりします。
一方、ファンクションは、組み込みのSQL関数と同じように、SQL文の中で使うことができ、ファンクションが返す値はSELECT文の値やWHERE句の条件として使うことができます。
ストアドルーチンを使うとなにがうれしいのか？ そのメリットをいくつかあげてみます。
ストアドルーチンに複雑な処理を詰め込むことにより、使用する言語やプラットフォームに関わらず同じ処理を実行することができます。
もうちょっと具体的な例として、会員状態を判別する処理を考えます。単純に有効フラグを見るだけで会員状態が判別できれば苦はないのですが、おぞましい歴史的経緯があり、複雑なルールに基づいたSQL文でないと判別できないとします。そして会員状態の判別をしたいのは、Webアプリであったりバッチプログラムであったり複数存在し、さらに悪いことにそれぞれ別々の言語で作られているとします。
こんなときこそストアドルーチンの出番です。
ストアドルーチンの内部におぞましく複雑な判別ルールを押し込めば、それを使う側はストアドルーチンを呼べばいいだけなので、実はプログラム間で判別ルールの実装が違っていた、とか、ルールを変更する際にあちこちのコードを書き換えないといけない、といった惨事が回避できます。
ストアドルーチンを呼ぶにはEXECUTE権限が必要ですが、ストアドルーチンが参照や変更するテーブルへの権限は設定によっては一切必要ありません。
また、MySQL 5.0.6から、個々のストアドルーチンごとにEXECUTE権限を与えることができるようになったので、ユーザごとにかなりこまかなアクセス制限を課すことができるようになりました。
この機能を活用すれば、用途の異なるユーザ（例えば更新用と参照用とか）ごとにそれぞれ異なるストアドルーチンをAPI的に公開し、直接テーブルを操作するSELECT文やUPDATE文は許可しない、といったことができます。
ストアドルーチンを呼ぶだけで複雑なSQLを実行できるため、クライアントとサーバ間のやりとりが減ります。その結果、（やりとりが減る分だけ）総体としてパフォーマンスの向上が期待できます。
が、落とし穴もあります。
まず、サーバ側の負荷が増えます。具体的には、ストアドルーチンを実行するコストだとか、ストアドルーチン内で行っているフロー制御や条件分岐など、クライアントでもできる処理の分の負荷が増えます。Webサーバがたくさんある場合にはストアドルーチンの実行頻度が高くなるので注意が必要かもしれません。
ふたつめの落とし穴は、現時点でのMySQLのストアドルーチンはプリコンパイルされないという点です。RDBMSによっては（PostgreSQLのPL/pgSQLなど）、ストアドルーチンはサーバ側にプリコンパイルされて保存されるのでその実行速度は高速なのですが、MySQLではプリコンパイルされません。ですので、この点でのパフォーマンスの向上は期待できません。
まずはストアドファンクションを作ってみたいと思います。
ただの例ではつまらないので、より実用に近い例として、会員状態を判別するストアドファンクションを作ってみたいと思います。
会員の情報を管理するテーブルとして、リスト1の「member」テーブルがあり、データは図1の通りとします。
そして会員状態にあるとみなす条件を以下のように定めます。
この条件にのっとって会員状態を判別するストアドファンクションをこれから作ります。名前は「is_active_user」にしましょう。入力と出力は表1のようにしたいと思います。
<リスト1：memberテーブルの定義>
CREATE TABLE IF NOT EXISTS member (
id SMALLINT UNSIGNED
,name VARCHAR(32) NOT NULL
,age TINYINT UNSIGNED
,team_id SMALLINT UNSIGNED
,register_date DATETIME
,quit_date DATETIME
,expire_date DATETIME
,PRIMARY KEY (id)
) ENGINE=InnoDB;
<図1：memberテーブルのデータ>
+----+---------+------+---------+--------------+------------+------------+
| id | name | age | team_id | register_date| quit_date | expire_date|
+----+---------+------+---------+--------------+------------+------------+
| 1 | ichirou | 16 | 10 | 2006-10-01 | NULL | 2007-10-01 |
| 2 | jirou | 25 | 20 | 2006-10-02 | NULL | 2007-10-02 |
| 3 | saburou | 27 | 10 | 2006-10-03 | 2006-11-03 | 2007-10-03 |
| 4 | shirou | 19 | 20 | 2004-10-04 | NULL | 2005-10-04 |
| 5 | gorou | 32 | 20 | 2006-10-05 | NULL | 2007-10-05 |
| 6 | rokurou | 38 | 10 | 2006-10-06 | NULL | 2007-10-06 |
+----+---------+------+---------+--------------+------------+------------+
| 表1：is_active_userのインターフェース | ||
| 引数 | id | 検査対象の会員ID |
| 返値 | 1かNULL | 1ならば会員状態 |
ストアドルーチンに関係する権限には表2のものがあります。
CREATE ROUTINEとALTER ROUTINE権限はプロシージャとファンクションの両方に影響しますが、EXECUTEはプロシージャもしくはファンクション個別に実行許可を与えることができます。（注1）
例えば、
場合には、リスト2のGRANT文を発行します。この状態だと、ユーザwdappはis_active_user以外のファンクションやプロシージャは実行できません。
| 表2：ストアドルーチンに関係する権限 | |
| 権限名 | 与えられる権限 |
| CREATE ROUTINE | 作成 |
| ALTER ROUTINE | 変更 |
| EXECUTE | 実行 |
<リスト2：ストアドファンクション関連のGRANT文>
GRANT CREATE ROUTINE
,ALTER ROUTINE
,EXECUTE
ON wd.*
TO wd@'%';
GRANT EXECUTE
ON FUNCTION wd.is_active_user
TO wdapp@'%';
ストアドファンクションを作るには、CREATE FUNCTION文を使います。is_active_userの場合、リスト3のようになります。
<リスト3：is_active_userの定義>
DROP FUNCTION IF EXISTS is_active_user; ←(1)
DELIMITER // ←(2)
CREATE FUNCTION is_active_user (in_user_id SMALLINT) ←(3)
RETURNS TINYINT ←(4)
BEGIN ←(5)
DECLARE is_active TINYINT DEFAULT NULL; ←(6)
SELECT id IS NOT NULL INTO is_active FROM member
WHERE id = in_user_id
AND quit_date IS NULL
AND expire_date > NOW();
RETURN is_active;
END;
// ←(7)
DELIMITER ; ←(8)
ストアドファンクションは、組み込みのSQL関数のように条件節やSELECT文のカラム指定の箇所で使えます。実行例を図2に示します。
<図2：ファンクションの実行例>
● id=1の人は会員かどうか
wd@my50-1[wd]> SELECT is_active_user(1);
+-------------------+
| is_active_user(1) |
+-------------------+
| 1 |
+-------------------+
● id=3の人は会員かどうか
wd@my50-1[wd]> SELECT is_active_user(3);
+-------------------+
| is_active_user(3) |
+-------------------+
| NULL |
+-------------------+
● 全員の会員状態を見る
wd@my50-1[wd]> SELECT is_active_user(id),id,name FROM member;
+--------------------+----+---------+
| is_active_user(id) | id | name |
+--------------------+----+---------+
| 1 | 1 | ichirou |
| 1 | 2 | jirou |
| NULL | 3 | saburou |
| NULL | 4 | shirou |
| 1 | 5 | gorou |
| 1 | 6 | rokurou |
+--------------------+----+---------+
● 会員状態の人のみを一覧する
wd@my50-1[wd]> SELECT id,name FROM member WHERE is_active_user(id);
+----+---------+
| id | name |
+----+---------+
| 1 | ichirou |
| 2 | jirou |
| 5 | gorou |
| 6 | rokurou |
+----+---------+
ここまでで首尾よくファンクションの作成と実行ができたわけですが、リスト2でwdappというユーザにEXECUTE権限を与えたのを覚えているでしょうか。この権限が正しく付与されているか確認してみましょう。
まず、wdappユーザでMySQLに接続します。このとき、DB名を指定していない点に注意してください。接続したら、DB名を添えてwd.is_active_user()というようにストアドファンクションを実行します。（図3の(1)）
うまく実行できました。しかし、wdappユーザはmemberテーブルへのSELECT権はおろか、wdデータベースへ移動する権限すら持っていません。（図3の(2)）
<図3：wdappユーザでファンクションを実行>
$ mysql -uwdapp -hmy50-1 ┐
wdapp@my50-1[(none)]> SELECT wd.is_active_user(1); │
+----------------------+ │
| wd.is_active_user(1) | │(1)
+----------------------+ │
| 1 | │
+----------------------+ ┘
wdapp@my50-1[(none)]> SELECT * FROM wd.member; ┐
ERROR 1142 (42000): SELECT command denied │
to user 'wdapp'@'my50-1' for table 'member' │
│(2)
wdapp@my50-1[(none)]> use wd; │
ERROR 1044 (42000): Access denied │
for user 'wdapp'@'%' to database 'wd' ┘
では、wdappユーザが実行したis_active_userから実行されるmemberテーブルへのSELECT文はどのユーザの権限で実行されているのでしょうか？
リスト3のCREATE FUNCTION文の場合だと、CREATE FUNCTION文を発行したユーザで実行されていることになります。このへんのしくみはストアドプロシージャのところでまとめて解説したいと思います。
ファンクションに続いて、ストアドプロシージャを試してみましょう。
ファンクションのときと同く、会員状態を判別するcheck_active_userというプロシージャを作ってみます。
ファンクションのときと同じ権限（表2）が必要です。
ただし、プロシージャ個別にEXECUTE権限をGRANTする場合は、ON FUNCTIONではなくON PROCEDUREになります。（リスト4）
<リスト4：ストアドプロシージャ関連のGRANT文>
GRANT EXECUTE
ON PROCEDURE wd.check_active_user
TO wdapp@'%';
ストアドプロシージャを作るには、CREATE PROCEDURE文を使います。check_active_userの場合、リスト5のようになります。
ファンクションのときと同じように、check_active_userプロシージャが存在しているときは削除して(1)、SQL文の終端子を「//」に変更しています。(2)
(3)でプロシージャに渡す引数を指定しているのはファンクションと同じですが、ファンクションと違い、プロシージャは返値を持つことができません。ですので、値を返したい場合はプロシージャを呼ぶときに指定された引数に格納するかたちになります。これが(4)です。(3)と(4)を見るとわかるように、プロシージャに渡す値には「IN」を、プロシージャが返す値には「OUT」という修飾子をつけます。
(5)のBEGIN以降がストアドプロシージャの本体です。見慣れないSELECT ... INTOという構文がありますが、これはSELECTの結果を変数に格納するための構文で、ストアドプロシージャでなくても使えます。
最後もファンクションと同じように、(6)の「//」がCREATE PROCEDURE文の終わりで、(7)で終端子を元に戻しています。
<リスト5：check_active_userの定義>
DROP PROCEDURE IF EXISTS check_active_user; ←(1)
DELIMITER // ←(2)
CREATE PROCEDURE check_active_user (IN in_user_id SMALLINT ←(3)
,OUT is_active TINYINT) ←(4)
BEGIN ←(5)
SELECT id IS NOT NULL INTO is_active FROM member
WHERE id = in_user_id
AND quit_date IS NULL
AND expire_date > NOW();
END;
// ←(6)
DELIMITER ; ←(7)
ストアドプロシージャは、ファンクションと違い、実行するにはCALL文を使います。例えば、id=1の人の会員状態を調べその結果をユーザ変数@is_activeに格納するには、図4のようにします。
<図4：プロシージャの実行例>
wd@my50-1[wd]> CALL check_active_user(1,@is_active);
Query OK, 0 rows affected (0.01 sec)
wd@my50-1[wd]> SELECT @is_active;
+------------+
| @is_active |
+------------+
| 1 |
+------------+
ここまでで、簡単なストアドファンクションとプロシージャを見てきましたが、ストアドルーチンではSQL文だけでなく以下のこともできます。
そこで、これらの機能を使ったちょっと複雑なストアドプロシージャを作ってみたいと思います。
memberテーブルには、年齢（age）とチームID（team_id）というカラムがあるので、指定されたIDのチームに18歳未満の人がいたら「young」という文字列を返し、そうでなければ「adult」を返す、age_group_by_teamというストアドプロシージャを作ってみたいと思います。
リスト6のようになります。
(1)(2)がプロシージャの引数の定義で、(1)がプロシージャに渡すチームのID、(2)がプロシージャから返される結果（「young」か「adult」）です。
BEGIN以降がプロシージャの本体です。
(3)は本体で使用する変数の宣言です。
(4)はカーソルの宣言で、このようにカーソルで参照するSELECT文を書きます。ここのSELECT文では先ほど作ったストアドファンクションis_active_userをこっそり使っています。このように、ストアドプロシージャの中でストアドファンクションを使うこともできます。さて、カーソルですが、(4)では宣言しているだけなので、使う前には(7)のようにまずOPENします。OPENしたあとに、(9)のようにFETCHすると、SELECTの結果を1件ずつ得ることができます。そして使い終わったら、(13)のようにCLOSEします。
(5)は変数宣言と同じようにDECLAREで始まっていますが、ここは変数宣言ではなくイベント制御を規定しています。(5)の内容を噛み砕くと、02000番のSQLSTATEが発生したときは、doneに1を代入して、そのまま処理を継続（CONTINUE）する、となります。SQLSTATEが0200番の意味は、
Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA) Message: No data - zero rows fetched, selected, or processed
で、カーソルでもうFETCHするデータがなくなったときに発生するイベントです。そのほかのコード番号は、MySQL ABのドキュメント『Server Error Codes and Messages』（注2）にその一覧があります。CONTINUEのほかにはEXITが使えます。（注3）
続く(6)は、先にDECLAREで宣言した変数への初期値の代入です。
(8)のREPEATは繰り返すブロックの宣言で、CHECK_YOUNGというラベルをつけています。このREPEATブロックのおわりは(12)のEND REPEATで、(11)のUNTILの条件節が真になるまで繰り返されます。この例だと、UNTIL doneでdoneの初期値は(3)で指定しているように0で、(5)で指定したようにカーソルが最後に達するとdoneが1になります。MySQLでは1が真で0が偽を示すので、カーソルが最後に達するとCHECK_YOUNGのREPEATブロックを抜けることになります。
(10)がcheck_active_userのキモの部分で、ループの終了条件を満たしていなければ、FETCHで得た年齢（_age）と閾値（adult_age）を比較しています。そしてもしYOUNGな場合は、変数age_groupに「young」を格納して、LEAVEで（CHECK_YOUNGとラベルづけした）REPEATループを抜けるようにしています。
ちょっとsyntaxがスマートじゃない感がありますが、ここでみたように変数、フロー制御、カーソルを駆使して複雑なストアドルーチンを作ることもできます。
<リスト6：age_group_by_teamの定義>
DROP PROCEDURE IF EXISTS age_group_by_team;
DELIMITER //
CREATE PROCEDURE age_group_by_team (IN in_team_id SMALLINT ←(1)
,OUT age_group VARCHAR(8)) ←(2)
BEGIN
DECLARE done TINYINT DEFAULT 0; ┐
DECLARE adult_age TINYINT UNSIGNED; │(3)
DECLARE _name VARCHAR(32); │
DECLARE _age TINYINT UNSIGNED; ┘
DECLARE cur CURSOR FOR ┐
SELECT name, age │
FROM member │(4)
WHERE team_id = in_team_id │
AND is_active_user(id); ┘
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; ←(5)
SET age_group = 'adult'; ┐(6)
SET adult_age = 18; ┘
OPEN cur; ←(7)
CHECK_YOUNG: REPEAT ←(8)
FETCH cur INTO _name, _age; ←(9)
IF NOT done THEN ┐
IF _age < adult_age THEN │
SET age_group = 'young'; │(10)
LEAVE CHECK_YOUNG; │
END IF; │
END IF; ┘
UNTIL done ←(11)
END REPEAT; ←(12)
CLOSE cur; ←(13)
END;
//
DELIMITER ;
ストアドルーチンの名前や定義を確認するには、（前回紹介した）情報スキーマを使います。
メタ情報が格納されているのはINFORMATION_SCHEMA.ROUTINESで、このテーブルには表3のカラムがあります。
| 表3：INFORMATION_SCHEMA.ROUTINESのカラム | |
| カラム名 | 内容 |
| SPECIFIC_NAME | ストアドルーチンの名前 |
| ROUTINE_CATALOG | 常にNULL |
| ROUTINE_SCHEMA | ストアドルーチンが属するデータベース |
| ROUTINE_NAME | ストアドルーチンの名前 |
| ROUTINE_TYPE | 「PROCEDURE」か「FUNCTION」 |
| DTD_IDENTIFIER | FUNCTIONの場合、返値の型 |
| ROUTINE_BODY | 常に「SQL」 |
| ROUTINE_DEFINITION | ストアドルーチンの本体 |
| EXTERNAL_NAME | 常にNULL |
| EXTERNAL_LANGUAGE | 常にNULL |
| EXTERNAL_NAME | 常にNULL |
| PARAMETER_STYLE | 常に「SQL」 |
| IS_DETERMINISTIC | 同じ入力ならば常に同じ出力を返すか |
| SQL_DATA_ACCESS | ストアドルーチン内でデータを読み書きするか |
| SQL_PATH | 常にNULL |
| SECURITY_TYPE | ストアドルーチンをどの権限で実行するか |
| CREATED | 作成日時 |
| LAST_ALTERED | 最終変更日時 |
| SQL_MODE | 実行時のsql_modeの種別 |
| ROUTINE_COMMENT | コメント |
| DEFINER | ストアドルーチンを定義したユーザ |
さて、ストアドファンクションの項で後回しにした、ストアドルーチン内のSQLを実行したときの権限（実効ユーザ）について説明します。
まず、先のCREATE PROCEDURE/FUNCTION文の例では省略した構文があります。それはDEFINER（注4）とSQL SECURITYで（リスト7）、実効ユーザを語る上でキモになってきます。
<リスト7：DEFINERとSQL SECURITYの構文>
CREATE
DEFINER = { CURRENT_USER | user_name@host_name }
{ PROCEDURE | FUNCTION }
SQL SECURITY { DEFINER | INVOKER }
...
DEFINERはストアドルーチンを「作った人」を指定します。デフォルトはCURRENT_USERで、CREATE文を発行したユーザになるのですが、明示的にuser_name@host_nameの形式で指定することもできます。ただし、自分以外のユーザを指定するにはSUPER権限が必要な点に注意してください。なぜわざわざ「作った人」を指定できるのかは、SQL SECURITYに関係してきます。
SQL SECURITYは次のふたつの働きを持っています。
SQL SECURITYに指定可能なのはDEFINERとINVOKERで、これで間接的にユーザを指定します。デフォルトのDEFINERの場合はDEFINER構文で指定したユーザとなり、INVOKERの場合はストアドルーチンを実行したユーザとなります。
ちょっとややこしいので具体例をあげます。表4のユーザがいて、図5のようなストアドプロシージャについて、DEFINERとSQL SECURITYの組み合わせがどう影響するかみていきましょう。
| 表4：wdデータベースのユーザ | |
| ユーザ名 | 保持している権限 |
| wd | 全権限を持っている。 |
| wdapp | プロシージャの実効権限だけを持っている。 |
| wddef | どの権限も持っていない。 |
<図5：実効ユーザ検証用のプロシージャ> CREATE DEFINER = { CURRENT _USER | wddef@'%' } PROCEDURE sp_DEFINER_SQLSECURITY () SQL SECURITY { DEFINER | INVOKER } BEGIN INSERT INTO who_is_invoker VALUES ('DEFINER_SQLSECURITY', USER(), CURRENT_USER()); END; // ただし、 ● DEFINER はDEFINER構文の値に応じて、currentかspecify ● SQLSECURITY はSQL SECURITY構文の値に応じて、definerかinvoker とします。
wdユーザでCREATEした場合、実効ユーザはwdとなります。EXECUTE権限しか持っていないwdappユーザがこのプロシージャを実行しても正常終了します。なぜなら、実効ユーザであるwdはEXECUTE権限もテーブルへのINSERT権限も持っているからです。
CREATEしたユーザに関係なく、実効ユーザはプロシージャを実行したユーザになります。wdappユーザが実行した場合、EXECUTE権限を持っているのでプロシージャの実行はできますが、INSERT権限を持っていないのでテーブルへのINSERTで失敗します。
DEFINER構文がほかのユーザを指しているため、このCREATE PROCEDUREを実行するにはSUPER権限が必要です。
wdappユーザがこのプロシージャを実行した場合、実効ユーザはDEFINER構文で指定したようにwddefユーザになります。
さて、プロシージャを実行するユーザ（今回はwdapp）にはEXECUTE権限が必要なのですが、先に述べたように、SQL SECURITYのユーザ（今回はwddef）にもEXECUTE権限が必要です。しかし、wddefにはEXECUTE権限が与えられていないので、wdappユーザが実行したプロシージャは権限不足で失敗します。（図6-(1)）では、wddefユーザにEXECUTE権限を与えるとどうなるかというと、今度は図6-(2)のように、テーブルへのINSERT権限がないのでエラーになります。
<図6：権限不足のエラーメッセージ>
(1) EXECUTE権限不足
ERROR 1370 (42000) at line 1: execute command denied to user
'wddef'@'%' for routine 'wd.sp_specify_definer'
(2) INSERT権限不足
ERROR 1044 (42000) at line 1: Access denied for user
'wddef'@'%' to database 'wd'
DEFINER構文がほかのユーザを指しているため、このCREATE PROCEDUREを実行するにはSUPER権限が必要です。
wdappユーザが実行した場合、「DEFINER = CURRENT_USER / SQL SECURITY INVOKER」のときと同じ理由で、テーブルへのINSERTで失敗します。
レプリケーションや差分バックアップのために、バイナリログ（--log-bin）を有効にしている場合に限り、注意点があります。
ストアドファンクションは関数呼び出しのまま、バイナリログに記録されるのですが、これが問題になるケースをふたつあげます。
MySQLのバイナリログはステートメントベースなので、SQL文中のファンクションの呼び出しがそのままバイナリログに記録されます。しかし、そのファンクションが呼び出しのたびに返値が違ったり更新するデータが異なったりすると、バイナリログを使ったスレーブやリカバリのときに、結果が不安定になってしまうわけです。
一方、ストアドプロシージャにはこのような問題はありません。なぜなら、CALL文で実行したストアドプロシージャは、CALL文がバイナリログに記録されるのではなくプロシージャ本体のSQL文に展開されてバイナリログに記録されるからです。
さて、このようなストアドファンクションのトラブルを未然に防ぐために、バイナリログが有効になっている場合は、たとえSUPER権限をもっていても、CREATE FUNCTION文で明示的に次のいずれかの句が指定されていないとストアドファンクションを作ることができません。
しかしこれらは自己申告に過ぎず、実行する度に返値が変わるとしても、DETERMINISTICを指定できてしまいます。
他方、『CREATE FUNCTIONする人はこれらの問題をよく理解していて信頼できるので、いちいち自己申告は要らない』という場合は、my.cnfにlog_bin_trust_function_creatorsと書けば、この制限を外すことができます。
最後にもうひとつの回避策を紹介します。MySQL 5.1からステートメンスベースのバイナリログに加え、行ベースのバイナリログも実装されました。行ベースの場合は、実際に更新されたデータがバイナリログに記録されるので、ここで説明したストアドファンクションの問題は発生しません。ステートメントベースと行ベースのバイナリログのメリット／デメリットの比較については、MySQL ABのドキュメント（注5）を参照してください。
トリガとは、テーブルに対してINSERT、UPDATE、DELETEが発生したタイミングで自動的に一連の処理を実行するしくみです。（注6）
使用者に意識させずに、処理の自動化ができます。
例えば、会員テーブルに新しい会員情報をINSERTしたら、自動的にポイントテーブルにも当該会員の初期値をINSERTすることができます。
使用者の環境（mysqlコマンドや言語）に関わらず、処理の自動化と統一化ができるのですが、乱用するとアプリケーションのコードを追っただけでは処理の全容がつかめず、「なぜか魔法のようにデータが更新される」状態に陥るので注意してください。
トリガを作るには、5.0ではSUPER権限が、5.1.6以降ではTRIGGER権限が必要です。
トリガはCREATE TRIGGER文で作ります。CREATE TRIGGER文の骨格を図7に示します。
trigger_eventではトリガ実行の契機となる文の種類を指定します。表5の3種類ありますが、注意点を何点かあげます。
trigger_timeで指定できるのは「BEFORE」か「AFTER」で、トリガを実行するのを、契機となる文が適用される前にするか後にするかを指定できます。
trigger_stmtはトリガの処理の本体部分です。この本体部分では「NEW.col_name」もしくは「OLD.col_name」で、変更前、変更後のカラムの値を参照できます。また、trigger_timeがBEFOREの場合は、NEW.col_nameに値を代入して変更後の値をトリガ内で変更することもできます。（表6）
<図7：CREATE TRIGGER>
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
| 表5：trigger_event | |
| trigger_event | 契機となる文 |
| INSERT | INSERT、LOAD DATA、REPLACE |
| UPDATE | UPDATE |
| DELETE | DELETE、REPLACE |
| 表6：NEWとOLD | |
| trigger_event | 使用可能な修飾子 |
| INSERT | NEW |
| UPDATE | OLD、NEW |
| DELETE | OLD |
トリガを使った具体例として、memberテーブルが変更されたら自動的に変更点を記録する監査システムを作ってみたいと思います。
リスト8のテーブルを用意します。このテーブルには、memberテーブルで変更のあったデータと、そのSQL文の種別（INSERT、UPDATE、DELETE）と変更日時を格納します。格納するのはトリガのお仕事で、次のようなデータを保存することにします。
このトリガの定義はリスト9になります。実行例は割愛しますが、memberテーブルにINSERT、UPDATE、DELETEすると、member_auditテーブルにもそれに応じたデータが記録されるはずです。
<リスト8：member_auditテーブルの定義>
DROP TABLE IF EXISTS member_audit;
CREATE TABLE IF NOT EXISTS member_audit (
id SMALLINT UNSIGNED
,name VARCHAR(32) NOT NULL
,age TINYINT UNSIGNED
,team_id SMALLINT UNSIGNED
,register_date DATETIME
,quit_date DATETIME
,expire_date DATETIME
,op VARCHAR(8) NOT NULL
,change_date DATETIME
) ENGINE=InnoDB;
<リスト9：トリガの定義>
DROP TRIGGER member_after_insert;
DROP TRIGGER member_after_update;
DROP TRIGGER member_after_delete;
DELIMITER //
CREATE TRIGGER member_after_insert
AFTER INSERT ON member FOR EACH ROW
BEGIN
INSERT INTO member_audit
VALUES (NEW.id, NEW.name, NEW.age, NEW.team_id
,NEW.register_date,NEW.quit_date,NEW.expire_date
,'INSERT'
,NOW());
END;
//
CREATE TRIGGER member_after_update
AFTER UPDATE ON member FOR EACH ROW
BEGIN
INSERT INTO member_audit
VALUES (NEW.id, NEW.name, NEW.age, NEW.team_id
,NEW.register_date,NEW.quit_date,NEW.expire_date
,'UPDATE'
,NOW());
END;
//
CREATE TRIGGER member_after_delete
AFTER DELETE ON member FOR EACH ROW
BEGIN
INSERT INTO member_audit
VALUES (OLD.id, OLD.name, OLD.age, OLD.team_id
,OLD.register_date,OLD.quit_date,OLD.expire_date
,'DELETE'
,NOW());
END;
//
DELIMITER ;
最後に、ストアドルーチンとトリガの制限事項をいくつかピックアップします。総じて、ストアドファンクションとトリガの制限事項は似ていて、ストアドプロシージャとはちょっと違います（注7）。詳しくはMySQL ABのドキュメント（注8）を参照してください。
ストアドルーチンとトリガでは以下のSQL文が使えません。
ただし、5.0.13以降のストアドプロシージャでは使用可能。
ストアドファンクションとトリガは上記に加え、以下のSQL文も使えません。
今回はストアドプロシージャ、ファンクションとトリガについて開拓しました。
どれもほかのRDBMSにはよく実装されている機能ですが、MySQLでは5.0ではじめて実装されました。そのせいかまだまだこなれていない点が多い印象を受けました。具体的には、ストアドファンクションやトリガの制限事項や、レプリケーションとの関係です。
いずれも効果的に使うと便利な機能なのですが、もう少しこなれるまでは注意して使う必要があるのではないかと思います。
