前提
AES_ENCRYPTとAES_DECRYPTを使いvarbinary型へのデーター保存で暗号化
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mail` varbinary(768) NOT null,
PRIMARY KEY (`id`),
KEY `mail` (`mail`(112))
);
登録
INSERT INTO `user` SET
`mail` = AES_ENCRYPT('hoge@hoge.com','鍵文字列');
複合
SELECT
AES_DECRYPT(`mail`,'鍵文字列')
FROM `user`;
検索
INDEX効く
mysql> EXPLAIN SELECT * FROM `user` WHERE `mail` = AES_ENCRYPT('hoge@hoge.com','鍵文字列');
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | mail | mail | 114 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
INDEX効かない
mysql> EXPLAIN SELECT * FROM `user` WHERE AES_DECRYPT(`mail`,'鍵文字列') = 'hoge@hoge.com';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)