PHPのbase64_encode()でエンコードしたデータをMYSQLでデコードしてみた。

2024年6月18日

MYSQLに日本語のデータを保存する場合、大抵はそのまま保存します。
ですが、まれに文字化けなどを起こす場合、テクニックの一つとしてbase64でエンコード（変換）した状態で保存します。

base64で保存すれば、半角英数字化されるため、文字化けしなくなるためです。
戻すときはbase64にデコード（復号化）すれば元に戻ります。

ただ、base64形式での保存には欠点があります。
それは、「日本語で正しくソートされない」という点です。

半角英数字で保存された状態でソートするので、当然といえば当然です。

今回、base64形式で保存されたデータを日本語ベースでソートできないか調べて実践してみました。
忘備録として記録いたします。

今回の前提

今回の前提は以下の通りです。

• base64エンコードはPHPで行う。
• base64デコードははMYSQLで行う。

検証用MYSQLテーブル

検証用プログラム

日本語をbase64でエンコードして、MYSQLに追加します。
テーブル名がbase64です。ちょっとややこしいですが、関数名ではありません。

検証テーブルの中身

MYSQLの中身は、base64でエンコードされた状態で保存されています。

そもそもPHPでbase64エンコードしたデータをMYSQLでデコードできるのか？

そもそも、PHPでエンコードしたら、PHPでなければデコードできないような感じがしますが、bas64のエンコード、デコードの規格が同じであれば、問題なくできます。

実際やってみましたが、PHPでエンコードしたデータをMYSQLでデコードできました。

まずはデコードせずにソートしてみました

base64でエンコードしていても、そのままで正しくソートできているならそれに越したことはありません。まずは、base64エンコードで保存されている状態のままソートをしてみました。

結果は失敗でした。
サンプルだと、「あ行」の次が「た行」になってしまいました。
やはり、デコードしてソートしないと駄目でした。

MYSQLでbase64をデコードする方法

MYSQLでFROM_BASE64()関数を呼ぶだけです。
これだけで、base64デコードしてくれます。

ちなみに、MYSQLでbase64のエンコードもできます。
TO_BASE64()関数を呼べばエンコードになります。

SQL文

正しくでコードできました。
なんか感動です。
cast() でchar としているのは、このままだとblob 相当になり、phpmyadminの画面上に表示されないためでうす。実際に使用する際には、キャストしなくても文字が取り出せます。

さらに、MYSQLでデコードしてソートしてみました。

先ほどの記述に、order by（ソート）を指定しました。
すると、ちゃんと日本語でソートされました。

SQL文

ソート目的だけならば、ORDER BY 句のところで、FROM_BASE64()関数を呼べばOKです。

エンコードはPHP側でやるべきか、MYSQL側でやるべきか

今回のことで、base64でのエンコードは、PHPでやっても、MYSQLでやっても同じ結果になることがわかりました。ならば、どちらでやるのが正解なのか疑問に思いました。

速度を考えれば、MYSQL側でやった方が早いように思います。
ただ、汎用性を考えた場合は、PHP側でやったほうが良いようにも思います。

結局ケースバイケースで最適な方法を選択することになるのかなと考えます。

いままでbase64変換は、PHP側でしかできないと思っていましたが、MYSQL側でもできるとわかり、やり方の幅が広がりました。