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

AES_ENCRYPT() and AES_DECRYPT() implement encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as “Rijndael.” The AES standard permits various key lengths. By default these functions implement AES with a 128-bit key length. Key lengths of 196 or 256 bits can be used, as described later. The key length is a trade off between performance and security.

16 * (trunc(string_length / 16) + 1)

AES_ENCRYPT() encrypts the string str using the key string key_str, and returns a binary string containing the encrypted output. AES_DECRYPT() decrypts the encrypted string crypt_str using the key string key_str, and returns the original (binary) string in hexadecimal format. (To obtain the string as plaintext, cast the result to CHAR. Alternatively, start the mysql client with --skip-binary-as-hex to cause all binary values to be displayed as text.) If either function argument is NULL, the function returns NULL. If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

INSERT INTO t
VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));

These functions support the use of a key derivation function (KDF) to create a cryptographically strong secret key from the information passed in key_str. The derived key is used to encrypt and decrypt the data, and it remains in the MySQL Server instance and is not accessible to users. Using a KDF is highly recommended, as it provides better security than specifying your own premade key or deriving it by a simpler method as you use the function. The functions support HKDF (available from OpenSSL 1.1.0), for which you can specify an optional salt and context-specific information to include in the keying material, and PBKDF2 (available from OpenSSL 1.0.2), for which you can specify an optional salt and set the number of iterations used to produce the key.

INSERT INTO t
VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));

If you do not use a KDF, the key size should be less than 16 bytes. AES_ENCRYPT() does the following by default when a KDF is not used:

SELECT AES_ENCRYPT('mytext','mykeystring', '', 'hkdf', 'salt', 'info');

Sets the full 16-byte key to 0s.

SELECT AES_ENCRYPT('mytext','mykeystring', '', 'pbkdf2_hmac','salt', '2000');

XORs the supplied key into the internal key for as long as there is data.

SET @salt = RANDOM_BYTES(8);

If the supplied key is longer than the required key, it wraps around at the start of the result key and keeps XORing until there is data in the supplied key.

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('My secret passphrase',512);
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
+-------------------------------------------------------------+
| CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
+-------------------------------------------------------------+
| text                                                        |
+-------------------------------------------------------------+