MySQLのストアドプロシージャとファンクションの違い

mysql

ストアドプロシージャ

  • callで呼び出す。
  • 戻り値がないストアドファンクションみたいなもの

ストアドファンクション

  • selectなどのSQLに組み込んで呼び出す。(select count(*) みたいな)
  • 戻り値がある
  • binlog を有効にしていると(defaultの設定では)作成できない

binlog が有効時にcreate function できない理由

  • 同じ引数を与えても、実行のたびに戻り値が変わる可能性がある
  • データを更新する際に同じ引数を与えても、異なる値で更新する可能性があるので、問題になる
  • CALL で実行するprocedure の場合はCALL文 ではなくて、展開されたSQL文がバイナリログに残るから問題ないらしい

対策1

このため、CREATE FUNCTION をするには SUPER権限 に加えて以下のような明示的な指定が必要になる。

DETERMINISTIC

同じ引数の場合には、同じ値を返す場合。

データを更新しない場合

mysql> CREATE FUNCTION foo(s varchar(16)) RETURNS varchar(32)
mysql> DETERMINISTIC
mysql> RETURN CONCAT('foo ', s);

ただし、これらの指定はあくまで自己申告なので、毎回値が変わる関数でもDETERMINISTICを指定することは可能。

対策2

SUPER権限を与えたくない場合 or CREATE FUNCTION 実行する人を信頼できる場合は

log_bin_trust_function_creators = 1

とmy.cnf に設定しておく。