ENHANCED_AES_ENCRYPT

Syntax

ENHANCED_AES_ENCRYPT(plaintext[, iv])

Purpose

• This function is mainly used for column encryption. To enable this feature, set the tde_method parameter. For more information, see tde_method.

• This function requires the ENCRYPT privilege.

• plaintext specifies the plaintext to be encrypted using the AES algorithm.

• [, iv] is an optional parameter that specifies the initialization vector.

• This function uses the current tenant-level primary key. The default encryption algorithm is AES-128-ECB. You can change the encryption algorithm by setting the block_encryption_mode system variable. Only AES encryption algorithms are supported.

• This function supports encryption algorithms that require an initialization vector (IV), such as AES-128-CFB1. When you use such an algorithm for encryption or decryption, you must explicitly specify the iv parameter as the second argument. The minimum length of iv is 16 bytes.

• Key rotation does not affect decryption operations.

Examples

Use the INSERT and UPDATE statements to explicitly encrypt data in the specified column.

-- Encrypt the plaintext and write the result to the cipher_col column of the tbl table.
-- If any value in the values list uses the encryption function, you must have the corresponding privilege.
INSERT INTO tbl(cipher_col, ...) VALUES(ENHANCED_AES_ENCRYPT(plaintext), ...);
-- Update the encrypted column.
UPDATE tbl SET cipher_col = ENHANCED_AES_ENCRYPT(plaintext) WHERE ...;

Use the SELECT statement to query the encrypted column and return the encrypted data.

-- Query the encrypted column.
SELECT cipher_col FROM tbl;

Directly compare the encrypted data with the corresponding plaintext data. The expected result is false. To compare the data, you must decrypt the encrypted data or encrypt the plaintext data.

-- cipher_col is the encrypted column, and 12345 is the plaintext data. The encrypted data cannot match the plaintext data.
SELECT ... FROM tbl WHERE cipher_col = '12345';
-- The LIKE statement behaves similarly and cannot match the encrypted data.
SELECT ... FROM tbl WHERE cipher_col LIKE '123%';
-- After encrypting the plaintext data '12345', the encrypted data can match the encrypted data under the current key. You can use the index.
SELECT ... FROM tbl WHERE cipher_col = ENHANCED_AES_ENCRYPT('12345');
-- You can use the encrypted column for various calculations, including comparisons.
SELECT ... FROM tbl WHERE tbl.cipher_col = another_tbl.another_cipher_col;
SELECT ... FROM tbl GROUP BY cipher_col ORDER BY cipher_col;

References

ENHANCED_AES_DECRYPT

MySQL Mode Privilege Types