目次
MariaDB/暗号化・復号化と文字コード・照合順序（collation）の関係を整理する
MariaDBで日本語を扱うポイントと暗号化・復号化について整理しておきます。
試した環境は、Windows11／WSL2のUbuntu上のMariaDB10.9です。
そこにWIndows11のHeidiSQLから接続してオペレーションしています。
文字コードと照合順序（collation）
MariaDBで日本語を扱うポイントは、やっぱり文字コードだと思います。
文字コードは「utf8mb4」一択
MariaDBで日本語を扱う場合、文字コードは「utf8mb4」一択です。
日本語を表現する文字には、基本的な文字・記号のほとんどを含む「BMP文字（ Basic Multilingual Plane）」と「補助文字（追加文字）」がありますが、MariaDBの「utf8」には「BMP文字」しか含まれていないからです。
通常使用では問題にならないのですが、「urf8」だと、人名などで、まれに文字化けをおこして問題になります。
なので「BMP文字と補助文字の両方」を含む「utf8mb4」にすべきなのです。
照合順序のデフォルト「utf8mb4_general_ci」
文字コードの切っても切れない関係にあるのが、Selectの検索結果に影響する照合順序（collation）です。
MariaDBがサポートする「utf8mb4」に関連するCollationはたくさんあります。
でも、その中で日本語を使う時の主なものは以下の３つです。
- utf8mb4_unicode_ci：大文字小文字／全角半角を区別しません。
- utf8mb4_general_ci：大文字小文字を区別しません。
- utf8mb4_bin：完全に文字の一致を照合します。
MariaDBのデフォルトは「utf8mb4_general_ci」です。
（MySQL8.0だと「utf8mb4_0900_as_ci」になったみたいですが・・）
よほどの理由がない限り、デフォルトのままにしておくべきだと、僕は思います。
どうしても、utf8mb4_unicode_ciが必要なケースがあるなら、カラム単位でAlter文で変更をかけて対処すべきです。
その理由はあとのほうで書きます。
コードとかも「VARCHAR」が良い
文字列を格納するカラムの型の話です。
ディスクの格納効率を気にしないでいい環境でないのなら、utf8でもutf8mb4でも文字列を格納する型には「 VARCHAR 」を使うべきと思います。
たまに、マニュアルとかにも、そう書いてあったりしますし。
理由は格納効率の問題です。
utf8やutf8mb4の場合、１カラムに対して３～４バイトとってしまいます。
なので、CHARだとスペースで無駄にディスクを消費してしまいます。
コードとかで何となくCHAR(20）とか定義してしまうと、それだと80バイトも固定でとられてしまいますし、CHARとVARCHARで目に付くようなパフォーマンスの違いもないとしたら、CHARにする理由はありません。
暗号化カラムの復号化と文字コード・照合順序
データを暗号化して格納する機会も増えてます。
個人情報保護に気をつけるなら重要なデータは、保守担当者がHeidiSQLなんかでデータをのぞいても内容を見れないようにしておくべきなので当然の流れと思います。
暗号化したデータを復号化するときにも、文字コードと照合順序（collation）は関係してきます。
暗号化の方法自体はいろいろありますけれど、
今回は説明の例として、よく使われている以下の２つを使います。
＜暗号化＞
AES_ENCRYPT(str,key_str[,init_vector])
＜復号化＞
AES_DECRYPT(crypt_str,key_str[,init_vector])
暗号化・復号化に使うキーフレーズも色々な方法がありますが、今回は「 SHA2」を以下のように使ってみました。
select SHA2('boku_cript_passp',256);
実際に生成される、パスフレーズは長いのですけど、記事の都合上、以後は「d249306b0」と短く書いています。
動作確認用のデータは、文字化けとかのテストもかねて以下の文字列にしました。
- 辻はにてんつじ。昔は一点だったのに今は変換すると二点がでる
- 俱𠀋㐂兕あたりも文字化けしそうだよね
- ABCDEFGHIJKLM（英語の大文字）
- abcdefghijklm（英語の小文字）
格納先として、strというVARCHAR(200)カラムと、bstrというblobカラムの２つを用意し、どちらにも同じ文字列を以下のようなinsert文で挿入します。
insert into cript_test values (0,'ABCDEFGHIJKLM（英語の大文字）',AES_ENCRYPT('ABCDEFGHIJKLM（英語の大文字）','d249306b0'));
AES_ENCRYPTで暗号化した結果はバイナリになるので、blob項目につっこんでます。
これを使って確認をしていきます。
普通に復号化しただけだと漢字は化け化け・・
まず、暗号化して格納した文字列データを複合化して取り出してみます。
今回の環境は、データベースレベルで文字コードが「utf8mb4」、照合順序が「utf8mb4_general_ci」です。
なので、複合したら自動的に「utf8mb4」と「utf8mb4_general_ci」が適用されてほしいのですが、実際のところ、そうなりません。
文字列は「str」、暗号化文字列は「bstr」のカラムだという想定で
select str,AES_DECRYPT(bstr,'d249306b0') from cript_test;;
などとやってみると。
わお！ 文字が化け化けです。
CONVERTで文字コード変換して文字化け解消
文字化けを回避するには、文字コードを変換してやらないとダメです。
文字コード変換は「CONVERT」関数を使い、復号化の部分を以下のようにします。
CONVERT(AES_DECRYPT(‘name’, ‘key_string’) USING utf8mb4)
前のselect文を以下のようにすると
select str,CONVERT(AES_DECRYPT(bstr,'d249306b0') USING utf8mb4) from cript_test;
バッチリです。
ちなみに、上記の「USING utf8mb4」を「USING utf8」にすると。
2行目の一部の漢字が「????」に化けました。
照合順序はデフォルトを引き継ぐ・・のかな
今度は照合順序の確認をします。
VARCHARのカラムと、復号化したカラムで同じ結果が返ってくるかどうか比較します。
まずは、VARCHARのカラム（str）を条件にします。
where str like 'abcd%';
このカラムの照合条件はデフォルトの「utf8mb4_general_ci」です。
なので、英字の大文字・小文字を区別しません。
結果は以下のように2行とれます。
今度は同じ条件を復号化したものに適用します。
where CONVERT(AES_DECRYPT(bstr,'d249306b0') USING utf8mb4) like 'abcd%';
結果は
同じでした・・英字の大文字・小文字を区別しない・・は維持されています。
復号して「utf8mb4」にCONVERTしたカラムの照合条件はMariaDBのデフォルトの「utf8mb4_general_ci」が適用されています。
データベースに設定されたデフォルトではない点が注意です。
文字コード変換は万能ではない
上記のCONVERT関数の仕様が全体の照合順序をデフォルトと違うものにしたときに致命的な問題になることがあります。
MariaDBは異なる照合順序（collation）で結合や比較を行った場合に
Illegal mix of collations
というエラーが出るときがあります。
代表的なエラーになるケースが「utf8mb4_general_ci と utf8mb4_unicode_ci」のカラムの組み合わせですが、これがかなり致命的で、仮にデータベースのデフォルト照合順序（collation）を「utf8mb4_unicode_ci」にした場合、CONVERTで「utf9mb4」の指定ができなくなったりします。
これが、CONVERTが「USINGでutf8mb4を指定するとMariaDBのデフォルトのutf8mb4_general_ci を返す」弊害です。
これを回避しようとするとCONVERTのUSINGに「utf8」を指定ことになるので、何のために全体の文字コードを「utf8mb4」にしているかわからなくなります（笑）。
もちろん、SQL文で「COLLATE」文を使って強制的に照合順序（collation）を変更することもできます。
例えば、「utf8mb4_bin（大文字小文字を区別する）」にするならこんな感じ。
where CONVERT(AES_DECRYPT(bstr,'d249306b0') USING utf8mb4) like 'abcd%' COLLATE utf8mb4_bin;
でも、Select時に照合順序を強制的に変更するやり方だとインデックスが使われず、重たいクエリとかでやるとパフォーマンスが落ちるリスクもあるので、まあ、ピンポイントで緊急避難的に使うことはできても、上記の問題の根本的な解決策にはなりえないと個人的には思っています。
まとめ
繰り返しになりますが。
MariaDBで日本語を扱うシステムで、かつ、CONVERT関数を使うことが絶対にないと言い切れないのであれば・・
- データベース全体で「utf8mb4」に統一する
- 照合順序はデフォルトの「utf8mb4_general_ci 」を変更しない
としておくのが安全・安心だということになります。
今回は、このくらいですかね。
ではでは。
