how to encrypt emails in mysql database but still be able to query them?

I want to store the email addresses of users in a MySQL database using encryption to ensure that they won't be made public if the database gets compromised. I believe if I encrypt them with mysql's AES_ENCRYPT() function I can not create an index in an INNODB table because I have to use a BLOB datatype. If the table gets very large selects it will take a long time.

What is the best solution for开发者_运维知识库 securing email address but still being able to query them fast and preserve them as unique values in the column?

When a user registers on your site, use AES_ENCRYPT() to encrypt the email.

INSERT into users (email) VALUES (AES_ENCRYPT('someemail@example.com', 'aeskey'));

When you query your database, you can call the AES_DECRYPT() function like this:

SELECT AES_DECRYPT(email, 'aeskey') from users;

If you hash the addresses with SHA-256 or something similar, you can still index your tables, you can still do fast address lookups (when a user searches for example@example.com, you'll just hash the input and select matching hashes in the tables).

ssh uses a very similar hashing trick. (Look for the -H option in that manpage for details.)

AES_DECRYPT(email, 'secretkey') and AES_ENCRYPT(email, 'secretkey') is optimal solution,

I am not 100% sure about beeing unique after encryption but theory said if email is unique encription should be unique