MySQLのみでAES_ENCRYPTによる暗号化と復号と部分一致検索

MySQLのみで、AES_ENCRYPT関数による暗号化と復号を行う方法をまとめます。
また、暗号化されたカラムへの部分一致検索も実施したいと思います。

環境

centos 6系
mysqld 5.6系

テーブル

以下のようなスキーマが前提とします。

CREATE TABLE IF NOT EXISTS `customer` (
  `cus_id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'キーID',
  `cus_name` varchar(300) NOT NULL,
  PRIMARY KEY (`cus_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

データの登録

データをパスワードでAES暗号化→保存できるようにHEXで変換します。

insert into customer
(cus_name)
values
(HEX(AES_ENCRYPT('山田 太郎', 'password***')));

>以下のようなデータが登録されます。
>cus_id, cus_name
>1, 57A7306667A3E36142D49930278BCF84

復号してアプリケーションで表示できるように

UNHEX→デコード→utf8に再変換します。

select
  convert(
    AES_DECRYPT(UNHEX(cus_name), 'password***')
    USING utf8
  )
from
  customer

>convert( AES_DECRYPT(UNHEX(cus_name), 'password***') USING utf8 )
>山田 太郎

復号に成功！

部分一致検索

「山田」の部分一致検索を行います。

select
  *
from
  customer
where
  convert(
    AES_DECRYPT(UNHEX(cus_name), 'password***')
    USING utf8
  )
  like '%山田%'

>cus_id, cus_name
>1, 57A7306667A3E36142D49930278BCF84

上記だと、検索はできたましたが暗号化文字列が出力されます。

検索＆復号

上記を組み合わせてみます。

select
  convert(
    AES_DECRYPT(UNHEX(cus_name), 'password***')
    USING utf8
  )
from
  customer
where
  convert(
    AES_DECRYPT(UNHEX(cus_name), 'password***')
    USING utf8
  )
  like '%山田%'

>convert( AES_DECRYPT(UNHEX(cus_name), 'password***') USING utf8 )
>山田 太郎

これで部分一致検索と複合ができました。
MySQL側でやるべきか＆性能面で問題ないかは検証が必要かと思います。

参考

https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html
https://dev.mysql.com/doc/refman/5.6/ja/encryption-functions.html