MYSQLに日本語のデータを保存する場合、大抵はそのまま保存します。
ですが、まれに文字化けなどを起こす場合、テクニックの一つとしてbase64でエンコード（変換）した状態で保存します。
base64で保存すれば、半角英数字化されるため、文字化けしなくなるためです。
戻すときはbase64にデコード（復号化）すれば元に戻ります。
ただ、base64形式での保存には欠点があります。
それは、「日本語で正しくソートされない」という点です。
半角英数字で保存された状態でソートするので、当然といえば当然です。
今回、base64形式で保存されたデータを日本語ベースでソートできないか調べて実践してみました。
忘備録として記録いたします。
今回の前提
今回の前提は以下の通りです。
- base64エンコードはPHPで行う。
- base64デコードははMYSQLで行う。
検証用MYSQLテーブル
検証用プログラム
日本語をbase64でエンコードして、MYSQLに追加します。
テーブル名がbase64です。ちょっとややこしいですが、関数名ではありません。
$add_name = array("かきくけこ", "たちつてと", "あいうえお", "さしすせそ");
foreach($add_name AS $name)
{
mysqli_query( $db, "INSERT INTO base64 (name) VALUES ('". base64_encode(mb_convert_encoding($name, "UTF-8", "SJIS-win")) . "');");
}
検証テーブルの中身
MYSQLの中身は、base64でエンコードされた状態で保存されています。
そもそもPHPでbase64エンコードしたデータをMYSQLでデコードできるのか？
そもそも、PHPでエンコードしたら、PHPでなければデコードできないような感じがしますが、bas64のエンコード、デコードの規格が同じであれば、問題なくできます。
実際やってみましたが、PHPでエンコードしたデータをMYSQLでデコードできました。
まずはデコードせずにソートしてみました
base64でエンコードしていても、そのままで正しくソートできているならそれに越したことはありません。まずは、base64エンコードで保存されている状態のままソートをしてみました。
結果は失敗でした。
サンプルだと、「あ行」の次が「た行」になってしまいました。
やはり、デコードしてソートしないと駄目でした。
MYSQLでbase64をデコードする方法
MYSQLでFROM_BASE64()関数を呼ぶだけです。
これだけで、base64デコードしてくれます。
ちなみに、MYSQLでbase64のエンコードもできます。
TO_BASE64()関数を呼べばエンコードになります。
SQL文
SELECT no, cast(FROM_BASE64(name) AS char) AS decode_name FROM base64
正しくでコードできました。
なんか感動です。
cast() でchar としているのは、このままだとblob 相当になり、phpmyadminの画面上に表示されないためでうす。実際に使用する際には、キャストしなくても文字が取り出せます。
さらに、MYSQLでデコードしてソートしてみました。
先ほどの記述に、order by（ソート）を指定しました。
すると、ちゃんと日本語でソートされました。
SQL文
SELECT no, cast(FROM_BASE64(name) AS char) AS decode_name FROM base64 ORDER BY decode_name ASC
ソート目的だけならば、ORDER BY 句のところで、FROM_BASE64()関数を呼べばOKです。
エンコードはPHP側でやるべきか、MYSQL側でやるべきか
今回のことで、base64でのエンコードは、PHPでやっても、MYSQLでやっても同じ結果になることがわかりました。ならば、どちらでやるのが正解なのか疑問に思いました。
速度を考えれば、MYSQL側でやった方が早いように思います。
ただ、汎用性を考えた場合は、PHP側でやったほうが良いようにも思います。
結局ケースバイケースで最適な方法を選択することになるのかなと考えます。
いままでbase64変換は、PHP側でしかできないと思っていましたが、MYSQL側でもできるとわかり、やり方の幅が広がりました。