aes_decrypt

使用指定的高级加密标准（AES）算法和加密密钥对字符串进行解密。

语法

aes_decrypt(str, key_str[, init_vector][, encryption_mode][, aad_str]);

参数说明

• str（必须）：待解密的字符串，支持的数据类型为 VARCHAR。

• key_str（必须）：用于加密 str 的密钥字符串，支持的数据类型为 VARCHAR。

• init_vector（可选）：Initialization Vector(IV)，初始化向量是 AES 加密中至关重要的安全参数，用于确保相同明文加密后产生不同的密文。只在СВС/CFB/OFB/CTR/GCM模式下生效。支持的数据类型为 VARCHAR。

• encryption_mode（可选）：加密算法。支持的数据类型为 VARCHAR。默认值：AES_128_ECB。

  支持算法：

  ECB	CBC	CFB	CFB1	CFB8	CFB128	OFB	CTR	GCM
  AES_128_ECB	AES_128_CBC	AES_128_CFB	AES_128_CFB1	AES_128_CFB8	AES_128_CFB128	AES_128_OFB	AES_128_CTR	AES_128_GCM
  AES_192_ECB	AES_192_CBC	AES_192_CFB	AES_192_CFB1	AES_192_CFB8	AES_192_CFB128	AES_192_OFB	AES_192_CTR	AES_192_GCM
  AES_256_ECB	AES_256_CBC	AES_256_CFB	AES_256_CFB1	AES_256_CFB8	AES_256_CFB128	AES_256_OFB	AES_256_CTR	AES_256_GCM

• aad_str（可选）：附加认证数据（AAD）。这是认证加密模式（如GCM）特有的参数。它允许包含必须进行完整性认证（防止篡改）但无需保密性（保持未加密状态）的数据。支持的数据类型为 VARCHAR。

返回值说明

返回值的数据类型为 VARCHAR。如果输入值非法，则返回 NULL。

示例

将 Base64 编码过的字符串进行解码，然后进行 AES 解密。

mysql> select AES_DECRYPT(from_base64('uv/Lhzm74syo8JlfWarwKA==  '),'F3229A0B371ED2D9441B830D21A390C3');
+--------------------------------------------------------------------------------------------+
| aes_decrypt(from_base64('uv/Lhzm74syo8JlfWarwKA==  '), 'F3229A0B371ED2D9441B830D21A390C3') |
+--------------------------------------------------------------------------------------------+
| starrocks                                                                                  |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select AES_DECRYPT(from_base64('uv/Lhzm74syo8JlfWarwKA=='),'F3229A0B371ED2D9441B830D21A390C3', NULL, "AES_128_ECB");
+---------------------------------------------------------------------------------------------------------------+
| aes_decrypt(from_base64('uv/Lhzm74syo8JlfWarwKA=='), 'F3229A0B371ED2D9441B830D21A390C3', NULL, 'AES_128_ECB') |
+---------------------------------------------------------------------------------------------------------------+
| starrocks                                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select AES_DECRYPT(from_base64('taXlwIvir9yff94F5Uv/KA=='),'F3229A0B371ED2D9441B830D21A390C3', "abcdefg", "AES_128_CBC");
+--------------------------------------------------------------------------------------------------------------------+
| aes_decrypt(from_base64('taXlwIvir9yff94F5Uv/KA=='), 'F3229A0B371ED2D9441B830D21A390C3', 'abcdefg', 'AES_128_CBC') |
+--------------------------------------------------------------------------------------------------------------------+
| starrocks                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select AES_DECRYPT(from_base64('YWJjZGVmZ2hpamtsdpJC2rnrGmvqKQv/WcoO6NuOCXvUnC8pCw=='),'F3229A0B371ED2D9441B830D21A390C3', "abcdefghijklmnop", "AES_128_GCM", "abcdefg");
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aes_decrypt(from_base64('YWJjZGVmZ2hpamtsdpJC2rnrGmvqKQv/WcoO6NuOCXvUnC8pCw=='), 'F3229A0B371ED2D9441B830D21A390C3', 'abcdefghijklmnop', 'AES_128_GCM', 'abcdefg') |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| starrocks                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)