はじめに
この手段が果たしてベストなのか、本当はもっとスマートなやり方があるんじゃないのか、
そんな気持ちはありつつも、思い通りの結果が得られたのはこのやり方だけだった。という大を小に納める悲しい作業の末に得た、
嘆きに似たメモ。それがこの文章です。
要件
mysql 5.6
encoding=utf8
この環境のとあるテーブルに格納されているマルチバイト文字を格納したカラム(住所なんかをイメージ)からn バイト 文字を切り出す。
実装
そして出来上がったそびえ立つクソがこれである。
SELECT
CASE
WHEN LENGTH(multi_byte_string)>15 THEN
CASE WHEN LENGTH(LEFT(multi_byte_string,15)) < 15 THEN LEFT(multi_byte_string,15) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,14)) < 15 THEN LEFT(multi_byte_string,14) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,13)) < 15 THEN LEFT(multi_byte_string,13) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,12)) < 15 THEN LEFT(multi_byte_string,12) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,11)) < 15 THEN LEFT(multi_byte_string,11) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,10)) < 15 THEN LEFT(multi_byte_string,10) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,9)) < 15 THEN LEFT(multi_byte_string,9) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,8)) < 15 THEN LEFT(multi_byte_string,8) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,7)) < 15 THEN LEFT(multi_byte_string,7) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,6)) < 15 THEN LEFT(multi_byte_string,6) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,5)) < 15 THEN LEFT(multi_byte_string,5) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,4)) < 15 THEN LEFT(multi_byte_string,4) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,3)) < 15 THEN LEFT(multi_byte_string,3) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,2)) < 15 THEN LEFT(multi_byte_string,2) ELSE
CASE WHEN LENGTH(LEFT(multi_byte_string,1)) < 15 THEN LEFT(multi_byte_string,1) ELSE
multi_byte_string
END
END
END
END
END
END
END
END
END
END
END
END
END
END
END
ELSE
multi_byte_string
END
FROM
mytable
これはQiita用に短くしたもの。実際は50バイトとかがターゲットなので、これの2.5倍くらいある(クソ)
なぜこんな事になったのか?
MySQLのリファレンスにある文字列をバシバシする関数たち。
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html
この中のLEFT(str,len)
や SUBSTRING(str,pos,len)
には マルチバイトセーフ という言葉が並んでいる。
これは、この関数を使えばマルチバイト文字を中途半端な所で切って、文字化けたりしないですよ~というスーパー親切仕様のことを謳っている。 但し、こいつらがlenに指定できるのは文字数なのである (NOT バイト数)
そして、同じく文字関数に LENGTH
と CHAR_LENGTH
が存在する。これらはそれぞれ バイト数を返す 、文字数を返す 関数である。試しに以下のクエリを実行してみる。
SELECT
'123abc789d',
LENGTH('123abc789d'),
CHAR_LENGTH('123abc789d'),
LEFT('123abc789d',5),
LENGTH(LEFT('123abc789d',5)),
CHAR_LENGTH(LEFT('123abc789d',5))
すると、以下のような結果が帰ってくる。
+------------------+----------------------------+---------------------------------+----------------------------+------------------------------------+-----------------------------------------+
| 123abc789d | LENGTH('123abc789d') | CHAR_LENGTH('123abc789d') | LEFT('123abc789d',5) | LENGTH(LEFT('123abc789d',5)) | CHAR_LENGTH(LEFT('123abc789d',5)) |
+------------------+----------------------------+---------------------------------+----------------------------+------------------------------------+-----------------------------------------+
| 123abc789d | 22 | 10 | 123ab | 11 | 5 |
+------------------+----------------------------+---------------------------------+----------------------------+------------------------------------+-----------------------------------------+
見ての通り、バイト数を返すLENGTHとCHAR_LENGHTは一致せず、また各文字列の バイト数 は半角全角、漢字記号、雨霰が混ざることでより複雑化する。
これを回避するために、巷にはBINARY型へ変換してから切り落とすという手順もあるようだが、 マルチバイトセーフではない ので処理を行うと、文字化けや尻切れが発生する。
SELECT
/* この変換はマルチバイトセーフではない */
CONVERT(LEFT(CAST(multi_byte_string AS BINARY),15) USING utf8)
FROM
mytable
ではどうするか。
一回ずつ切り出して、規定バイト数以下になるかチェックすることにしたのだ
最後に
もしこれを見た奇特なMySQLerの方、マルチバイトセーフでUTF8の文字列をnバイトで切り出せるスッマートな方法をご存知でしたら、教えて下さい。
私からは以上です。