MySQLでUnicode Escape SequenceをDecodeするストアドファンクションを実装してみた。

はじめに

好物はインフラとフロントエンドのかじわらゆたかです。
Unicode Escape Sequenceされた文字列を含むCSVファイル等をMySQL/AuroraのLOADコマンドのみで取り込もうとすると、
ストアドファンクションを実装する必要が出てきます。
今回はそんなストアドファンクションを実装してみたといった内容です。

環境構築

今回用いた環境は下記となります。
* MySQL : 5.7.11
* Aurora : 5.6.10a

実装内容

DELIMITER //
CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8)
RETURNS text CHARSET utf8 DETERMINISTIC
BEGIN
declare pos         int;
declare escape      char(6) charset utf8;
declare unescape    char(3) charset utf8;
set pos = locate('\u', str);
while pos > 0 do
    set escape =  substring(str, pos, 5);
    set unescape = char(conv(substring(escape,2),16,10) using utf16);
    set str = replace(str, escape, unescape);
    set pos = locate('\u', str, pos+1);
end while;
return str;
END;
//

検索していたところ、\u で始まる文字列をDecodeする実装例はあったのですが、
\uで始まる文字列をDecodeする実装例はなかったため、その点に対して手を入れてます。
参考にした\uで始まる文字列をDecodeするのは下記になります。
STRINGDECODE: MySQL function to decode unicode escapes to utf8

実際にやっていることとしては単純です。 まず、\uのがどこにあるかその場所を探します。

次に、見つけたその場所から５文字を抽出し、escapeという変数に格納します。
なお、下記の例にある\u30d5 の場合escapeに格納されるのは、u30d5となります。

変数に格納したescapeからuを取り除いた値(30d5)を16進数から10進数に変換します。
変換すると12501となります。
その後文字列関数charを用いて、12501をutf16として変換した文字列を変数unescapeに格納します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

文字列全体から、変数escapeを変数unescapeに置き換えます。

その後、\uの検索からの流れを全体に実施し、実施後の文字列を返すといった流れになります。

確認

以下の様なテキストファイルをLOAD DATA INFILEを用いて取り込みたいと思います。
なお、内容は弊社横田のPythonでUnicodeエスケープシーケンスをUnicodeキャラクタに変換する ｜ Developers.IOの記事から用いました。

UnicodeEscapeSequence.txt

\u30d5\u30a1\u30a4\u30eb\u30b5\u30a4\u30ba\u304c\u5927\u304d\u3044

上記がデコードされれば、ファイルサイズが大きいといった文字列となります。

MySQLで普通には取り込めないことを確認します。

drop table if exists LoadUnicodeEscapeSequence cascade;
create table LoadUnicodeEscapeSequence (
    message  character varying(1000)
);
LOAD DATA LOCAL INFILE '/tmp/UnicodeEscapeSequence.txt' INTO TABLE LoadUnicodeEscapeSequence;
SELECT * FROM LoadUnicodeEscapeSequence;

結果

Decodeされていないそのままの文字列で取り込めてしまっています。

最後に、作成したストアドファンクションを用いて取り込みたいと思います。

drop table if exists LoadUnicodeEscapeSequence cascade;
create table LoadUnicodeEscapeSequence (
    message  character varying(1000)
);
LOAD DATA LOCAL INFILE '/tmp/UnicodeEscapeSequence.txt' INTO TABLE LoadUnicodeEscapeSequence 
FIELDS TERMINATED BY ',' (@message) SET message = STRINGDECODE(@message);
SELECT * FROM LoadUnicodeEscapeSequence;

結果

Decodeされて取り込まれていることが確認できました。

もちろんAuroraでも動きます。
なお、Auroraでの結果は上記のMySQLと同一なので省略します。

まとめ

VPC内に配置したLambdaでS3に配置したCSVを取り込ませようとした時に、この問題に遭遇しました。
同じ問題に悩んでいる人の一助になれば幸いです。