Y-Ken Studio

新しもの好きのデータエンジニアが四方山話をお届けします。

MySQLのユーザ定義関数(UDF)のbase64encode / base64decodeを使ってみた

MySQLのユーザ定義関数(UDF)には淡い夢を感じます。
この可能性を探るべく、Base64エンコード・デコードを題材に触れあってみたいと思います。

動機としては、MySQLのユーザ定義関数(UDF)を作りたいと思い、雰囲気を知りたかったから。
手短なものであれば何でも良かった。今では反省してい・・・(ry

UDFの使いどころ

MySQLのビルドイン関数である abs()concat()のようなものを独自に追加したいときに便利です。ストアドプロシージャーにも限度があるので、無理そうならUDFという流れですかね。 また、ストアドであれば結果を複数返せますが、UDFだと1つの値しか返せないため、複数の結果を返したい場合にはトリッキーな対応が必要です。
複数の結果を扱うには、シリアライズしたり別テーブルに結果を保存して後で取り出すといった対応をする必要となってきます。

手法 実行速度 利用言語 開発の手間
Stored Procedure 遅い SQL 小さなものであれば数分
UDF 速い C 数時間
Native Function 速い C 一大プロジェクト

速度の相対比較も分かりづらいので、とあるグリー勉強会でのベンチマークを引用します。
ストアドよりも実に8.5倍もUDFの方が速いという結果でした。

  • SQL :
    1137リクエスト/秒
  • Stored Procedure:
    1772リクエスト/秒
  • UDF:
    14983リクエスト/秒

引用元:勉強会「MySQL Hackingの手引き」を終えて

利用するコード一式

mysql-udf-base64
It provides base64 encode/decode function for MySQL-5.1/5.5/5.6 as UDF.
https://github.com/y-ken/mysql-udf-base64

トラブル無く使えるかと思いきや、参考記事にあったbase64のコードのコンパイルが通りません!
仕方ないので少し手を入れて動くようにしましたので、GitHubに上げておきました。
MySQL-5.6.11での動作を確認していますが、MySQL-5.1MySQL-5.5でも動くと思います。

利用例

mysql> SELECT base64encode('data,binary,text,...');
+--------------------------------------+
| base64encode('data,binary,text,...') |
+--------------------------------------+
| ZGF0YSxiaW5hcnksdGV4dCwuLi4=         |
+--------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT base64decode('ZGF0YSxiaW5hcnksdGV4dCwuLi4=');
+----------------------------------------------+
| base64decode('ZGF0YSxiaW5hcnksdGV4dCwuLi4=') |
+----------------------------------------------+
| data,binary,text,...                         |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t1 (body text);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (body) VALUES (base64encode('something'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT body, base64decode(body) from t1;
+--------------+--------------------+
| body         | base64decode(body) |
+--------------+--------------------+
| c29tZXRoaW5n | something          |
+--------------+--------------------+
1 row in set (0.00 sec)

インストール方法

ビルドします

$ git clone https://github.com/y-ken/mysql-udf-base64.git
$ cd mysql-udf-base64
$ gcc -Wall -fPIC -I/usr/local/include -shared base64.c -o base64.so
$ sudo install -m 755 base64.so `mysql_config --plugindir`

登録します

mysql> CREATE FUNCTION base64encode RETURNS STRING SONAME 'base64.so';
mysql> CREATE FUNCTION base64decode RETURNS STRING SONAME 'base64.so';

アンインストール方法

DROP FUNCTION base64encode;
DROP FUNCTION base64decode;

以上。

参考記事