mysqlに独自関数を組み込む（UDF）

mysqlには独自のユーザ定義関数を組み込むことができる「UDF」（User Defined Function）って機能があります。
例えばsennaのsnippetなどもUDFとして書かれてるみたいです。
mysqlに詳しい方々の間では結構メジャーなもののようですが、実際に使ってみたことがなかったので、ちょっと試してみました。

作ってみる関数

mysqlには現在時刻をミリ秒まで表示してくれる機能がないようなので、それを作ります。
ということで現在時刻をミリ秒まで表示する関数をUDF(User Defined Function)として組み込んでみます。

関数名は「ミリ秒」をあらわす直感的なフレーズで「msec」としました。

ゴールイメージ

select msec();　とすると現在時刻をミリ秒まで表示。
select msec("epoch"); とするとunix timeをミリ秒まで表示。

環境

RedHat Linux ES4
mysql 5.0.27(source build)
gcc 3.4.6

ソース

Cは素人同然なので自信ないけど、とりあえず晒します。
指摘ないし教育的指導などありましたらありがたく頂戴しますので、よろしくドウゾ。

#include <stdio.h>
#include <string.h>
#include <time.h>
#include <sys/time.h>
#include <mysql.h>

my_bool msec_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
    return 0;
}

void msec_deinit(UDF_INIT *initid __attribute__((unused)))
{
}

char *msec(UDF_INIT *initid __attribute__((unused)),
    UDF_ARGS *args, char *result, unsigned long *length,
    char *is_null, char *error __attribute__((unused)))
{

    struct timeval tv;
    struct tm *tmptr = NULL;

    is_null = 0;

    gettimeofday(&tv, NULL);
    tmptr = localtime(&tv.tv_sec);

    if(args->arg_count > 0 && ! strcmp( args->args[0], "epoch")){
        sprintf(result, "%d.%03d",
            tv.tv_sec,
            tv.tv_usec / 1000);
    }
    else{
        sprintf(result, "%04d/%02d/%02d %02d:%02d:%02d:%03d",
            tmptr->tm_year + 1900, tmptr->tm_mon + 1,
            tmptr->tm_mday, tmptr->tm_hour,
            tmptr->tm_min, tmptr->tm_sec,
            tv.tv_usec / 1000);
    }

    *length = strlen(result);
    return result;

}

コンパイル

gcc -Wall -I/usr/local/mysql/include/mysql -shared -o msec.so msec.c

デプロイ

どこか適当な場所にディレクトリ用意しておいて、mesc.soをコピー。

sudo cp msec.so /usr/local/lib/mysql/

面倒ならすでにライブラリのパスが通ってる場所に直接おいても良いと思います。
その場合は次の「環境変数変更」はやらなくてOKです。

環境変数変更

/etc/init.d/mysqld の中でmysqld_safeを呼び出している部分の上に以下の一行を挿入

           pid_file=$server_pid_file
   ここ→  env LD_LIBRARY_PATH=/usr/local/lib/mysql \
           $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

編集できたらmyslqd を再起動する

mysqlで関数登録

mysqlサーバに入って以下を叩く。
ちゃんと上記の環境変数部分でLD_LIBRARY_PATHが通っていればうまくいくはず。

CREATE FUNCTION msec RETURNS STRING SONAME 'msec.so';

使ってみる

msec()とたたくと現在時刻をミリ秒まで表示してくれました。感激！

    mysql> select msec();
    +-------------------------+
    | msec()                  |
    +-------------------------+
    | 2007/11/22 20:39:25:201 |
    +-------------------------+
    1 row in set (0.00 sec)

引数にepochとつけるとunixtimeをミリ秒まで表示。これもうまくできてさらに感激！

    mysql> select msec('epoch');
    +----------------+
    | msec('epoch')  |
    +----------------+
    | 1195731580.897 |
    +----------------+
    1 row in set (0.00 sec)

感想

C言語に詳しくないので初歩的なところでつっかえたりしましたが、UDF自体は結構シンプルな手順でできあがるので思ったよりも簡単でした。
他にもアイデア次第でいろいろmysqlを拡張できそう。
もうちょっとCを勉強して他にもいろいろ試してみようと思ったです。

参考

http://www.irori.org/tool/mregexp.html
↑とてもわかりやすかったです