MySQL で UDF を定義しよう - にょきにょきブログ

概要

MySQL の関数は自前で定義出来る。この記事では、MySQL からシェルコマンドを叩けるようにする関数を作成する。

関数を自前で定義する方法は、MySQL が用意している UDF(ユーザー定義関数、User Defined Function) の仕組みを使って行う。UDF は C や C++ で記述する。

今回試した環境は Ubuntu 12.04, MySQL 5.5.18.

今回作成するもの

SQL 上から echo とか curl とかのコマンドが叩けたら便利ですよね。
え？便利じゃない？発想力が乏しいようですね。
え？コマンド叩けると危険？危険性と利便性の差は紙一重です。

目標はこんな感じ。exec 関数に文字列でコマンド書けば実行され、@ret にはコマンドの返り値を入れるようにする。

mysql> SET @ret = exec("curl http://localhost:8080/hoge/huga");

UDF の実装

では早速実装。今回は C++ で実装することにする。ファイル名は exec.cpp で。

実装は簡単だ。作りたい関数名を $FUNC とすると、下記の3つの関数を実装すれば良い。

$FUNC_init
$FUNC
$FUNC_deinit

これを実装するのにインクルードするヘッダは2つ。mysql.h と m_string.h だ。これらは MySQL インストールディレクトリの include ディレクトリ内に配置されているのでコンパイル時にそのディレクトリを指定する。

ソースコード。たったこれだけで UDF が実装できる。

#include <mysql.h>
#include <m_string.h>

extern "C" {
  my_bool exec_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
  my_ulonglong exec(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
  void exec_deinit(UDF_INIT *initid);
}

// exec関数実行前に呼ばれる
my_bool exec_init(UDF_INIT *initid, UDF_ARGS *args, char *message){
    // 引数が文字列1つであるかどうかを調べる
    if(args->arg_count == 1 && args->arg_type[0]==STRING_RESULT){
        return 0;
    } else {
        strcpy(message, "Expected exactly one string type parameter" );
        return 1;
    }
}

// exec関数呼び出し終了後に呼ばれる
void exec_deinit(UDF_INIT *initid){
    // 今回は後処理は何もなし
}

// exec関数本体
my_ulonglong exec(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error){
    // 引数をそのまま system 関数に渡す
    return system(args->args[0]);
}

メインとなる関数は exec 関数。中身はこれだけだ。

// exec関数本体
my_ulonglong exec(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error){
    // 引数をそのまま system 関数に渡す
    return system(args->args[0]);
}

system 関数は与えられた文字列をコマンドとして実行する関数。

コンパイル

上記 exec.cpp をコンパイル。 -I オプションで MySQL のヘッダが格納されてるディレクトリを指定している。

$ g++ -Wall -I/usr/local/mysql/include -shared -o exec.so -fPIC exec.cpp

MySQL 上で exec 関数を使えるようにする

コンパイルに成功すると exec.so というシェアードライブラリがカレントディレクトリにできるのでこいつを MySQL の lib/plugin ディレクトリに移動する。

$ cp exec.so /usr/local/mysql/lib/plugin

その後 MySQL を立ち上げ、関数の登録を行う。

mysql> CREATE FUNCTION exec RETURNS int SONAME 'exec.so';
Query OK, 0 rows affected (0.00 sec)

これで exec 関数を利用する準備OK.

使う

exec 関数を登録すれば、もう SQL でシェルが叩けるようになった。

こんな感じでコマンドを exec 関数に渡すと

mysql> SELECT exec("echo hello world! > /tmp/helloworld");

こんな風にちゃんと実行される！

$ cat /tmp/helloworld
hello world!

もちろん当初の目標通り、 curl を叩くことだってできます。

mysql> SET @ret = exec("curl http://localhost:8080/hoge/huga");

実用的な使い方

単純な SELECT クエリ内でシェルを叩いてもあまり実用的ではないが、トリガと組み合わせて、特定のテーブルにレコードが挿入されたらシェルコマンドを叩いて挿入されたことを通知してくれるなんて使い方は実用的だろう。
他にも挿入のたびに一定の確率でゴミ掃除を行うスクリプトを叩くなんてもの使えるだろう。上記 UDF を改良してプロセスの標準出力をSQL内で扱えたらバリデーションやSQLでは対応できない変換ルールに基づいた変換なんてもの出来るようになるだろう。SQLドリブンなアプリケーションを作ることだってできるはず。

トリガの注意点として、参照や更新が激しいテーブルにトリガを仕掛けるのは性能的にあまりおすすめできない。注意されたし。