これはなに
Base64に変換したJSON文字列をMySQLに突っ込む際に、いい感じに取り扱ってほしい場合があります。
MySQL 5.7です。
データとクエリ
FROM_BASE64でただ変換するとbinaryな文字列として扱われ、解釈されません。
> select json_extract(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ=='), '$.name');
ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'.
JSONにCastするとよくわからないものが返ります。
> select cast(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==') as JSON);
+-----------------------------------------------------------------------+
| cast(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==') as JSON) |
+-----------------------------------------------------------------------+
| "base64:type253:eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==" |
+-----------------------------------------------------------------------+
1 row in set (0.03 sec)
> select json_extract(cast(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==') as JSON), '$.name');
+-----------------------------------------------------------------------------------------------+
| json_extract(cast(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==') as JSON), '$.name') |
+-----------------------------------------------------------------------------------------------+
| NULL |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON内で使っている文字セットでcharへ変換することで扱うことができます。
> show variables like 'character_set_connection';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| character_set_connection | utf8 |
+--------------------------+-------+
1 row in set (0.00 sec)
> select json_extract(cast(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==') as char), '$.name');
+-----------------------------------------------------------------------------------------------+
| json_extract(cast(FROM_BASE64('eyJuYW1lIjoicm1hbnpva3UiLCJhZ2UiOjMwfQ==') as char), '$.name') |
+-----------------------------------------------------------------------------------------------+
| "rmanzoku" |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)