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

此函数使用官方的 AES（高级加密标准）算法解密数据。有关更多信息，请参阅 AES_ENCRYPT() 的描述。This function decrypts data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of AES_ENCRYPT().

使用 AES_DECRYPT() 的语句对于基于语句的复制是不安全的。Statements that use AES_DECRYPT() are unsafe for statement-based replication.

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

AES_ENCRYPT() 和 AES_DECRYPT() 实现了使用官方 AES（高级加密标准）算法的数据加密和解密，该算法以前被称为 “Rijndael.” AES 标准允许使用不同的密钥长度。默认情况下，这些函数使用 128 位密钥长度实现 AES。之后将描述可以使用 196 或 256 位的密钥长度。密钥长度是在性能和安全性之间的权衡。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() 使用密钥字符串 key_str 对字符串 str 进行加密，并返回一个包含加密结果的二进制字符串。AES_DECRYPT() 使用密钥字符串 key_str 对加密字符串 crypt_str 进行解密，并以十六进制格式返回原始（二进制）字符串。（要将结果作为明文字符串获取，请将结果转换为 CHAR。或者，使用 mysql 客户端启动时加上 --skip-binary-as-hex，以便所有二进制值以文本形式显示。）如果任一函数参数为 NULL，函数返回 NULL。如果 AES_DECRYPT() 检测到数据无效或填充不正确，它会返回 NULL。然而，如果输入数据或密钥无效，AES_DECRYPT() 也可能返回非 NULL 的值（可能是垃圾数据）。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.

这些函数支持使用密钥派生函数（KDF）从传入的key_str信息中创建一个加密强度的秘密密钥。派生的密钥用于加密和解密数据，它会保留在 MySQL 服务器实例中，用户无法访问。强烈建议使用 KDF，因为相比在函数中使用自己预先生成的密钥或通过更简单的方法派生密钥，它提供了更好的安全性。该函数支持 HKDF（从 OpenSSL 1.1.0 起可用），您可以为其指定可选的盐值和上下文特定信息以包含在密钥材料中；也支持 PBKDF2（从 OpenSSL 1.0.2 起可用），您可以为其指定可选的盐值并设置生成密钥时使用的迭代次数。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.

如果不使用 KDF，密钥大小应小于 16 字节。AES_ENCRYPT() 在不使用 KDF 时默认执行以下操作：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:

AES_ENCRYPT() 和 AES_DECRYPT() 允许控制块加密模式。block_encryption_mode 系统变量控制基于块的加密算法的模式。其默认值为 aes-128-ecb，表示使用 128 位密钥和 ECB 模式进行加密。如需了解此变量允许值的说明，请参见 第 7.1.8 节，“服务器系统变量”。可选的 init_vector 参数用于为需要它的块加密模式提供初始化向量。AES_ENCRYPT() and AES_DECRYPT() permit control of the block encryption mode. 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 Section 7.1.8, “Server System Variables”. The optional init_vector argument is used to provide an initialization vector for block encryption modes that require it.

使用 AES_ENCRYPT() 或 AES_DECRYPT() 的语句对于基于语句的复制是不安全的。Statements that use AES_ENCRYPT() or AES_DECRYPT() are unsafe for statement-based replication.

如果从 mysql 客户端中调用 AES_ENCRYPT()，二进制字符串会根据 --binary-as-hex 的值以十六进制形式显示。如需了解该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If AES_ENCRYPT() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

以下是 AES_ENCRYPT() 和 AES_DECRYPT() 函数的参数：The arguments for the AES_ENCRYPT() and AES_DECRYPT() functions are as follows:

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 CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
+-------------------------------------------------------------+
| CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
+-------------------------------------------------------------+
| text                                                        |
+-------------------------------------------------------------+

COMPRESS(string_to_compress)

压缩一个字符串并返回作为二进制字符串的结果。此函数要求 MySQL 必须使用压缩库（如 zlib）编译。否则，返回值始终为 NULL。如果 string_to_compress 为 NULL，返回值也为 NULL。压缩后的字符串可以使用 UNCOMPRESS() 进行解压。Compresses a string and returns the result as a binary string. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The return value is also NULL if string_to_compress is NULL. The compressed string can be uncompressed with UNCOMPRESS().

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
        -> 21
mysql> SELECT LENGTH(COMPRESS(''));
        -> 0
mysql> SELECT LENGTH(COMPRESS('a'));
        -> 13
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
        -> 15

压缩字符串的内容存储方式如下：The compressed string contents are stored the following way:

如果在 mysql 客户端中调用 COMPRESS()，二进制字符串将根据 --binary-as-hex 的值以十六进制表示。关于该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If COMPRESS() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

MD5(str)

计算字符串的 MD5 128 位校验和。该值以 32 位十六进制数字的字符串形式返回，如果参数为 NULL，则返回 NULL。返回值例如可以用作哈希键。有关如何高效存储哈希值，请参阅本节开头的说明。Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hexadecimal digits, or NULL if the argument was NULL. The return value can, for example, be used as a hash key. See the notes at the beginning of this section about storing hash values efficiently.

返回值是连接字符集中的字符串。The return value is a string in the connection character set.

如果启用 FIPS 模式，MD5() 将返回 NULL。请参见 第 8.8 节，“FIPS 支持”。If FIPS mode is enabled, MD5() returns NULL. See Section 8.8, “FIPS Support”.

mysql> SELECT MD5('testing');
        -> 'ae2b1fca515949e5d54fb22b8ed95575'

这是 “RSA 数据安全公司 MD5 消息摘要算法。”This is the “RSA Data Security, Inc. MD5 Message-Digest Algorithm.”

请参阅本节开头关于 MD5 算法的说明。See the note regarding the MD5 algorithm at the beginning this section.

RANDOM_BYTES(len)

此函数返回一个包含 len 个随机字节的二进制字符串，这些字节是使用 SSL 库的随机数生成器生成的。len 的允许值范围是 1 到 1024。超出该范围的值将导致错误。如果 len 为 NULL，则返回 NULL。This function returns a binary string of len random bytes generated using the random number generator of the SSL library. Permitted values of len range from 1 to 1024. For values outside that range, an error occurs. Returns NULL if len is NULL.

RANDOM_BYTES() 可用于为 AES_DECRYPT() 和 AES_ENCRYPT() 函数提供初始化向量。在该上下文中使用时，len 必须至少为 16。允许使用更大的值，但超过 16 的字节将被忽略。RANDOM_BYTES() can be used to provide the initialization vector for the AES_DECRYPT() and AES_ENCRYPT() functions. For use in that context, len must be at least 16. Larger values are permitted, but bytes in excess of 16 are ignored.

RANDOM_BYTES() 生成随机值，这使得其结果具有不确定性。因此，使用此函数的语句在基于语句的复制中是不安全的。RANDOM_BYTES() generates a random value, which makes its result nondeterministic. Consequently, statements that use this function are unsafe for statement-based replication.

如果从 mysql 客户端内部调用 RANDOM_BYTES()，二进制字符串将根据 --binary-as-hex 的值以十六进制表示。有关该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If RANDOM_BYTES() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

SHA1(str), SHA(str)

计算字符串的 SHA-1 160 位校验和，如 RFC 3174 (安全哈希算法) 所述。该值以 40 个十六进制数字的字符串形式返回，如果参数为 NULL，则返回 NULL。此函数的一个可能用途是作为哈希键。有关高效存储哈希值的说明，请参见本节开头的备注。SHA() 与 SHA1() 同义。Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hexadecimal digits, or NULL if the argument is NULL. One of the possible uses for this function is as a hash key. See the notes at the beginning of this section about storing hash values efficiently. SHA() is synonymous with SHA1().

返回值是连接字符集中的字符串。The return value is a string in the connection character set.

mysql> SELECT SHA1('abc');
        -> 'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1() 可以被认为是 MD5() 的加密上更安全的等效函数。不过，请参阅本节开头关于 MD5 和 SHA-1 算法的备注。SHA1() can be considered a cryptographically more secure equivalent of MD5(). However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section.

SHA2(str, hash_length)

计算 SHA-2 系列哈希函数（SHA-224、SHA-256、SHA-384 和 SHA-512）。第一个参数是要进行哈希的明文字符串。第二个参数表示所需结果的比特长度，其值必须为 224、256、384、512 或 0（等同于 256）。如果任一参数为 NULL 或哈希长度不是允许值之一，则返回值为 NULL。否则，函数结果为包含所需比特数的哈希值。有关高效存储哈希值的说明，请参阅本节开头的说明。Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The first argument is the plaintext string to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Otherwise, the function result is a hash value containing the desired number of bits. See the notes at the beginning of this section about storing hash values efficiently.

返回值是连接字符集中的字符串。The return value is a string in the connection character set.

mysql> SELECT SHA2('abc', 224);
        -> '23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7'

仅当 MySQL 配置了 SSL 支持时，此函数才能使用。请参阅 第 8.3 节，“使用加密连接”。This function works only if MySQL has been configured with SSL support. See Section 8.3, “Using Encrypted Connections”.

SHA2() 可以被认为在密码学上比 MD5() 或 SHA1() 更安全。SHA2() can be considered cryptographically more secure than MD5() or SHA1().

STATEMENT_DIGEST(statement)

给定一个作为字符串的 SQL 语句，返回该语句的摘要哈希值，类型为连接字符集中的字符串；如果参数为 NULL，则返回 NULL。相关的 STATEMENT_DIGEST_TEXT() 函数返回规范化的语句摘要。有关语句摘要的信息，请参见 第 29.10 节，“性能模式语句摘要与采样”。Given an SQL statement as a string, returns the statement digest hash value as a string in the connection character set, or NULL if the argument is NULL. The related STATEMENT_DIGEST_TEXT() function returns the normalized statement digest. For information about statement digesting, see Section 29.10, “Performance Schema Statement Digests and Sampling”.

这两个函数都使用 MySQL 解析器来解析语句。如果解析失败，将会发生错误。仅当语句以字面字符串提供时，错误消息才会包含解析错误。Both functions use the MySQL parser to parse the statement. If parsing fails, an error occurs. The error message includes the parse error only if the statement is provided as a literal string.

max_digest_length 系统变量决定这些函数用于计算规范化语句摘要的最大字节数。The max_digest_length system variable determines the maximum number of bytes available to these functions for computing normalized statement digests.

mysql> SET @stmt = 'SELECT * FROM mytable WHERE cola = 10 AND colb = 20';
mysql> SELECT STATEMENT_DIGEST(@stmt);
+------------------------------------------------------------------+
| STATEMENT_DIGEST(@stmt)                                          |
+------------------------------------------------------------------+
| 3bb95eeade896657c4526e74ff2a2862039d0a0fe8a9e7155b5fe492cbd78387 |
+------------------------------------------------------------------+
mysql> SELECT STATEMENT_DIGEST_TEXT(@stmt);
+----------------------------------------------------------+
| STATEMENT_DIGEST_TEXT(@stmt)                             |
+----------------------------------------------------------+
| SELECT * FROM `mytable` WHERE `cola` = ? AND `colb` = ?  |
+----------------------------------------------------------+

STATEMENT_DIGEST_TEXT(statement)

给定一个作为字符串的 SQL 语句，返回以连接字符集表示的规范化语句摘要字符串，如果参数为 NULL，则返回 NULL。有关更多讨论和示例，请参阅相关 STATEMENT_DIGEST() 函数的描述。Given an SQL statement as a string, returns the normalized statement digest as a string in the connection character set, or NULL if the argument is NULL. For additional discussion and examples, see the description of the related STATEMENT_DIGEST() function.

UNCOMPRESS(string_to_uncompress)

解压由 COMPRESS() 函数压缩的字符串。如果参数不是压缩值，结果为 NULL；如果 string_to_uncompress 为 NULL，结果也为 NULL。此函数要求 MySQL 必须使用如 zlib 之类的压缩库进行编译。否则，返回值始终为 NULL。Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL; if string_to_uncompress is NULL, the result is also NULL. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL.

mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
        -> 'any string'
mysql> SELECT UNCOMPRESS('any string');
        -> NULL

UNCOMPRESSED_LENGTH(compressed_string)

返回压缩字符串在压缩前的长度。如果 compressed_string 为 NULL，则返回 NULL。Returns the length that the compressed string had before being compressed. Returns NULL if compressed_string is NULL.

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
        -> 30

VALIDATE_PASSWORD_STRENGTH(str)

给定表示明文密码的参数，此函数返回一个整数以表示密码的强度，如果参数为 NULL，则返回 NULL。返回值范围从 0（弱）到 100（强）。Given an argument representing a plaintext password, this function returns an integer to indicate how strong the password is, or NULL if the argument is NULL. The return value ranges from 0 (weak) to 100 (strong).

通过 VALIDATE_PASSWORD_STRENGTH() 进行的密码评估由 validate_password 组件完成。如果该组件未安装，该函数总是返回 0。有关安装 validate_password 的信息，请参阅 第 8.4.3 节，“密码验证组件”。要检查或配置影响密码测试的参数，请查看或设置由 validate_password 实现的系统变量。请参阅 第 8.4.3.2 节，“密码验证选项和变量”。Password assessment by VALIDATE_PASSWORD_STRENGTH() is done by the validate_password component. If that component is not installed, the function always returns 0. For information about installing validate_password, see Section 8.4.3, “The Password Validation Component”. To examine or configure the parameters that affect password testing, check or set the system variables implemented by validate_password. See Section 8.4.3.2, “Password Validation Options and Variables”.

密码会接受越来越严格的测试，返回值反映了满足哪些测试，如下表所示。此外，如果启用了 validate_password.check_user_name 系统变量，并且密码与用户名匹配，VALIDATE_PASSWORD_STRENGTH() 将返回 0，而不管其他 validate_password 系统变量如何设置。The password is subjected to increasingly strict tests and the return value reflects which tests were satisfied, as shown in the following table. In addition, if the validate_password.check_user_name system variable is enabled and the password matches the user name, VALIDATE_PASSWORD_STRENGTH() returns 0 regardless of how other validate_password system variables are set.