Mysql5.7でutf8mb4のレコードをAES_ENCRYPTを使用して登録したい

Question

前提・実現したいこと

AES_ENCRYPTを使用してレコードを挿入したい。

DATABASE:sandbox
TABLE:tests

mysql> show create database sandbox;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| sandbox  | CREATE DATABASE `sandbox` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+------------------------------------------------------------------------------------------------+

mysql> show create table tests;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| tests | CREATE TABLE `tests` (
  `name` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+

発生している問題・エラーメッセージ

ERROR 1366 (HY000): Incorrect string value: '\x8D\x91\x82-\xFFB...' for column 'name' at row 1

該当のソースコード

INSERT INTO tests ( `name` ) VALUES ( AES_ENCRYPT( 'hoge', '123456' ) );

試したこと

CentOS7.6のmysql5.7でcharacter setをutf8mb4でcollationはutf8mb4_unicode_ciに設定しました。
my.cnfの内容は

[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set = utf8mb4

となっています。

mysqlで

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

となっているので特に問題はないと思われます。

INSERT INTO tests (`name`) VALUES ( 'hoge' );

だとレコードは登録されます。

文字コードが不整合なエラーだと思いますが、何処を確認したら良いでしょうか。

補足情報（FW/ツールのバージョンなど）

CentOS Linux release 7.6.1810 (Core)
mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper

Answer 1

実際使用していない上、未検証のエア回答です。

AES_ENCRYPT(str,key_str[,init_vector])

AES_ENCRYPT() encrypts the string str using the key string key_str and returns a binary string containing the encrypted output.

上記の通り、binary string が返るとあり、「 VARBINARY or BLOB 」が推奨されています。

ただ、推奨ってだけでダメって書いてないんですよねぇ。。。外れな気がします＾＾；

気になる機能なんで回答付けました。
誰か良い回答してくれるとよいのですが＾＾；

Comments

[spell]

回答ありがとうございます。
結局HEXとUNHEXとCONVERTで対応する事にしました。

[退会済みユーザー]

HEX 使用するのってアンチパターンな気がするんですよねぇ。。。
未検証回答なんで、ただの勘ですが。

Answer 2

AES_ENCRYPTをする際にHEXをしAES_DECRYPTをする際にUNHEXしてレコードの挿入閲覧が出来るようになりました。

mysql> INSERT INTO tests ( `name` ) VALUES ( HEX( AES_ENCRYPT( 'hoge', '123456' ) ) );
Query OK, 1 row affected (0.01 sec)

SELECT CONVERT( AES_DECRYPT(UNHEX(name), '123456' ) USING utf8mb4 ) FROM tests;
+-------------------------------------------------------------+
| convert( AES_DECRYPT(UNHEX(name), '12345' ) USING utf8mb4 ) |
+-------------------------------------------------------------+
| hoge                                                        |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 3

blobとかにしておけばよいのでは？

CREATE TABLE `tests` (
  `name` blob DEFAULT NULL
);
INSERT INTO tests (name) VALUES (AES_ENCRYPT('hoge','123456'));
select AES_DECRYPT(name,'123456') from tests;

追記

検索性もないのに無駄にvarcharを使う意味がわかりませんが、仕様なら仕方ないですね
すでにご自身で書いてありますがhex-unhex,aes_encrypt-aes_decryptで処理します

CREATE TABLE tests (
  `name` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

INSERT INTO tests (name) VALUES (HEX(AES_ENCRYPT('hoge','123456')));
select AES_DECRYPT(UNHEX(name),'123456') from tests;

Comments

[spell]

回答ありがとうございます。
要件としてvarcharでとの事なので、blobを使用は出来ないです。

[spell]

検索対象との事なのでlike検索するためvarcharになっているようです。