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.

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 plaintext string. If either function argument
is NULL, the function returns NULL.

The str and crypt_str arguments can be any length, and padding is
automatically added to str so it is a multiple of a block as required
by block-based algorithms such as AES. This padding is automatically
removed by the AES_DECRYPT() function. The length of crypt_str can be
calculated using this formula:

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

For a key length of 128 bits, the most secure way to pass a key to the
key_str argument is to create a truly random 128-bit value and pass it
as a binary value. For example:

VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));

A passphrase can be used to generate an AES key by hashing the
passphrase. For example:

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

Do not pass a password or passphrase directly to crypt_str, hash it
first. Previous versions of this documentation suggested the former
approach, but it is no longer recommended as the examples shown here
are more secure.

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.

AES_ENCRYPT() and AES_DECRYPT() permit control of the block encryption
mode and take an optional init_vector initialization vector argument:

o The block_encryption_mode system variable controls the mode for
block-based encryption algorithms. Its default value is aes-128-ecb,
which signifies encryption using a key length of 128 bits and ECB
mode. For a description of the permitted values of this variable, see

o The optional init_vector argument provides an initialization vector
for block encryption modes that require it.

For modes that require the optional init_vector argument, it must be 16
bytes or longer (bytes in excess of 16 are ignored). An error occurs if
init_vector is missing.

For modes that do not require init_vector, it is ignored and a warning
is generated if it is specified.

A random string of bytes to use for the initialization vector can be
produced by calling RANDOM_BYTES(16). For encryption modes that require
an initialization vector, the same vector must be used for encryption
and decryption.

URL: https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html


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