Using MySQL AES_ENCRYPT and AES_DECRYPT for Data Encryption

MySQL provides both static encryption of physical files and SQL‑level encryption of specific fields using the native functions AES_ENCRYPT() and AES_DECRYPT() . This article introduces these functions, their parameters, storage requirements, and practical examples.

Parameter Description

Decryption: AES_DECRYPT(crypt_str, key_str[, init_vector][, kdf_name][, salt][, info | iterations])

Encryption: AES_ENCRYPT(str, key_str[, init_vector][, kdf_name][, salt][, info | iterations])

crypt_str : the string to be decrypted; the length of the encrypted field can be calculated as 16 * (trunc(string_length / 16) + 1) , so a single character requires a minimum of 16 bytes.

key_str : the encryption key; it is recommended to hash the key rather than store it in plain text.

init_vector : initialization vector required for block encryption modes other than the default aes-128-ecb . Modes such as CBC, CFB, and OFB need an IV, and the same IV must be used for decryption. ECB is considered insecure.

kdf_name, salt, info, iterations : parameters for key‑derivation functions (KDF). They improve security but require MySQL 5.7.40 or 8.0.30+; thus they are omitted in the examples.

Usage Guidelines

MySQL’s AES implementation supports 128‑bit keys by default, with optional 196‑bit or 256‑bit keys for higher security. Using AES_ENCRYPT() with statement‑based binlog is insecure; a SSL connection is recommended to avoid transmitting keys in clear text.

Example of encrypting and decrypting data:

mysql [localhost:5734] {root} (test)> show create table test;
+-------+---------------------------------------------------------------+
| Table | Create Table                                                  |
+-------+---------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `n` char(200) DEFAULT NULL,
  `t` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------+

mysql [localhost:5734] {root} (test)> insert into test values(aes_encrypt('b','test'),1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5734] {root} (test)> select * from test;
+----------------------------+------+ 
| n                          | t    |
+----------------------------+------+ 
| x                          | 0    |
| y                          | 0    |
| ùpñU!㿧ҟWHƒôò            | 1    |
+----------------------------+------+ 

mysql [localhost:5734] {root} (test)> select aes_decrypt(n,'test') from test where t = 1;
+-----------------------+
| aes_decrypt(n,'test') |
+-----------------------+
| b                     |
+-----------------------+

Because the encrypted result is binary, the column should be defined as VARBINARY or BLOB to avoid character‑set conversion errors.

mysql [localhost:5729] {msandbox} (test)> create table test (a int, n varchar(60));
Query OK, 0 rows affected (0.06 sec)

mysql [localhost:5729] {msandbox} (test)> insert into test values(1,AES_ENCRYPT('test','test'));
ERROR 1366 (HY000): Incorrect string value: '\x87\xBD\x908\x85\x94...' for column 'name' at row 1

mysql [localhost:5729] {msandbox} (test)> alter table test MODIFY `n` VARBINARY(180);
Query OK, 0 rows affected (0.13 sec)

mysql [localhost:5729] {msandbox} (test)> insert into test values(1,AES_ENCRYPT('test','test'));
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5729] {msandbox} (test)> select a, AES_decrypt(n,'test') from test;
+------+--------------------------+
| a    | AES_decrypt(n,'test')    |
+------+--------------------------+
| 1    | test                     |
+------+--------------------------+

Alternatively, store the encrypted value as hexadecimal to avoid insertion errors, then use UNHEX() for decryption:

mysql [localhost:5729] {msandbox} (test)> insert into test1 values(1,hex(AES_ENCRYPT('test','test')));
Query OK, 1 row affected (0.02 sec)

mysql [localhost:5729] {msandbox} (test)> select AES_DECRYPT(unhex(n),'test') from test1;
+---------------------------------+
| AES_DECRYPT(unhex(n),'test')    |
+---------------------------------+
| test                            |
+---------------------------------+

Setting the block encryption mode and using a hashed key with an IV demonstrates a more secure configuration:

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('mysql passphrase',512);
SET @init_vector = 'It is very very safe';
SET @crypt_str = AES_ENCRYPT('test',@key_str,@init_vector);
SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
+-----------------------------------------------+
| AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
+-----------------------------------------------+
| test                                          |
+-----------------------------------------------+

Conclusion

The native MySQL encryption functions can protect sensitive data such as ID numbers or bank cards, but bulk usage may introduce performance overhead. For complex encryption needs, implementing encryption at the application layer is often preferable to reduce database load.