Table 12.18 Encryption Functions加密功能
AES_DECRYPT() | |
AES_ENCRYPT() | |
COMPRESS() | |
MD5() | |
RANDOM_BYTES() | |
SHA1(), SHA() | |
SHA2() | |
STATEMENT_DIGEST() | |
STATEMENT_DIGEST_TEXT() | |
UNCOMPRESS() | |
UNCOMPRESSED_LENGTH() | |
VALIDATE_PASSWORD_STRENGTH() |
Many encryption and compression functions return strings for which the result might contain arbitrary byte values. 许多加密和压缩函数返回字符串，其结果可能包含任意字节值。If you want to store these results, use a column with a 如果要存储这些结果，请使用VARBINARY or BLOB binary string data type. VARBINARY或BLOB二进制字符串数据类型的列。This avoids potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (这避免了尾部空格删除或字符集转换可能会更改数据值的潜在问题，例如使用非二进制字符串数据类型（CHAR, VARCHAR, TEXT).CHAR、VARCHAR、TEXT）时可能出现的问题。
Some encryption functions return strings of ASCII characters: 一些加密函数返回ASCII字符字符串：MD5(), SHA(), SHA1(), SHA2(), STATEMENT_DIGEST(), STATEMENT_DIGEST_TEXT(). MD5()、SHA()、SHA1()、SHA2()、STATEMENT_DIGEST()、STATEMENT_DIGEST_TEXT()。Their return value is a string that has a character set and collation determined by the 它们的返回值是一个字符串，该字符串具有由character_set_connection and collation_connection system variables. character_set_connection和collation_connection系统变量确定的字符集和排序规则。This is a nonbinary string unless the character set is 这是一个非二进制字符串，除非字符集是binary.binary的。
If an application stores values from a function such as 如果应用程序存储来自返回十六进制数字字符串的函数（如MD5() or SHA1() that returns a string of hex digits, more efficient storage and comparisons can be obtained by converting the hex representation to binary using UNHEX() and storing the result in a BINARY( column. N)MD5()或SHA1()）的值，则可以通过使用UNHEX()将十六进制表示转换为二进制并将结果存储在二进制（N)列中来获得更有效的存储和比较。Each pair of hexadecimal digits requires one byte in binary form, so the value of 每对十六进制数字需要一个二进制字节，因此N depends on the length of the hex string. N的值取决于十六进制字符串的长度。N is 16 for an MD5() value and 20 for a SHA1() value. N对于MD5()值是16，对于SHA1()值是20。For 对于SHA2(), N ranges from 28 to 32 depending on the argument specifying the desired bit length of the result.SHA2()，N的范围从28到32，具体取决于指定结果所需位长度的参数。
The size penalty for storing the hex string in a 在CHAR column is at least two times, up to eight times if the value is stored in a column that uses the utf8 character set (where each character uses 4 bytes). CHAR列中存储十六进制字符串的大小惩罚至少为2倍，如果值存储在使用utf8字符集的列中（其中每个字符使用4个字节），则最多为8倍。Storing the string also results in slower comparisons because of the larger values and the need to take character set collation rules into account.存储字符串还会导致比较较慢，因为值较大，并且需要考虑字符集排序规则。
Suppose that an application stores 假设应用程序在MD5() string values in a CHAR(32) column:CHAR(32)列中存储MD5()字符串值：
CREATE TABLE md5_tbl (md5_val CHAR(32), ...);
INSERT INTO md5_tbl (md5_val, ...) VALUES(MD5('abcdef'), ...);
To convert hex strings to more compact form, modify the application to use 要将十六进制字符串转换为更紧凑的形式，请修改应用程序以使用UNHEX() and BINARY(16) instead as follows:UNHEX()和BINARY(16)，如下所示：
CREATE TABLE md5_tbl (md5_val BINARY(16), ...);
INSERT INTO md5_tbl (md5_val, ...) VALUES(UNHEX(MD5('abcdef')), ...);
Applications should be prepared to handle the very rare case that a hashing function produces the same value for two different input values. 应用程序应该准备好处理非常罕见的情况，即哈希函数为两个不同的输入值生成相同的值。One way to make collisions detectable is to make the hash column a primary key.使冲突可检测的一种方法是使哈希列成为主键。
Exploits for the MD5 and SHA-1 algorithms have become known. MD5和SHA-1算法的漏洞已经为人所知。You may wish to consider using another one-way encryption function described in this section instead, such as 您可能希望考虑改用本节中描述的另一个单向加密函数，例如SHA2().SHA2()。
Passwords or other sensitive values supplied as arguments to encryption functions are sent as cleartext to the MySQL server unless an SSL connection is used. 除非使用SSL连接，否则作为加密函数参数提供的密码或其他敏感值将以明文形式发送到MySQL服务器。Also, such values appear in any MySQL logs to which they are written. 而且，这些值会出现在写入它们的任何MySQL日志中。To avoid these types of exposure, applications can encrypt sensitive values on the client side before sending them to the server. 为了避免这些类型的暴露，应用程序可以在将敏感值发送到服务器之前对客户端的敏感值进行加密。The same considerations apply to encryption keys. 同样的考虑也适用于加密密钥。To avoid exposing these, applications can use stored procedures to encrypt and decrypt values on the server side.为了避免暴露这些值，应用程序可以使用存储过程在服务器端对值进行加密和解密。
AES_DECRYPT(crypt_str,key_str[,init_vector])
This function decrypts data using the official AES (Advanced Encryption Standard) algorithm. 此函数使用官方AES（高级加密标准）算法解密数据。For more information, see the description of 有关详细信息，请参阅AES_ENCRYPT().AES_ENCRYPT()的说明。
Statements that use 使用AES_DECRYPT() are unsafe for statement-based replication.AES_DECRYPT()的语句对于基于语句的复制是不安全的。
AES_ENCRYPT(str,key_str[,init_vector])
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. AES_ENCRYPT()和AES_DECRYPT()使用官方的AES（高级加密标准）算法（以前称为“Rijndael”）实现数据的加密和解密。AES标准允许不同的密钥长度。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. 默认情况下，这些函数实现具有128位密钥长度的AES。如下文所述，可以使用196或256位的密钥长度。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_ENCRYPT()使用密钥字符串key_str加密字符串str，并返回包含加密输出的二进制字符串。AES_DECRYPT() decrypts the encrypted string crypt_str using the key string key_str and returns the original plaintext string. AES_DECRYPT()使用密钥字符串key_str对加密字符串crypt_str进行解密，并返回原始的纯文本字符串。If either function argument is 如果任一函数参数为NULL, the function returns NULL.NULL，则函数返回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. str和crypt_str参数可以是任意长度，填充会自动添加到str中，因此它是基于块的算法（如AES）所需的块的倍数。This padding is automatically removed by the 此填充由AES_DECRYPT() function. AES_DECRYPT()函数自动删除。The length of crypt_str can be calculated using this formula:crypt_str的长度可以使用以下公式计算：
16 * (trunc(string_length / 16) + 1)
For a key length of 128 bits, the most secure way to pass a key to the 对于128位的密钥长度，将密钥传递给key_str argument is to create a truly random 128-bit value and pass it as a binary value. key_str参数的最安全方法是创建一个真正随机的128位值，并将其作为二进制值传递。For example:例如：
INSERT INTO t
VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));
A passphrase can be used to generate an AES key by hashing the passphrase. 密码短语可用于通过对密码短语进行散列来生成AES密钥。For example:例如：
INSERT INTO t
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. crypt_str，先将其散列。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. AES_DECRYPT()检测到无效数据或不正确的填充，则返回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_DECRYPT()可能返回非空值（可能是垃圾值）。
AES_ENCRYPT() and AES_DECRYPT() permit control of the block encryption mode and take an optional init_vector initialization vector argument:AES_ENCRYPT()和AES_DECRYPT()允许控制块加密模式，并采用可选的init_vector初始化向量参数：
The block_encryption_mode system variable controls the mode for block-based encryption algorithms. block_encryption_mode系统变量控制基于块的加密算法的模式。Its default value is 它的默认值是aes-128-ecb, which signifies encryption using a key length of 128 bits and ECB mode. aes-128-ecb，表示使用128位密钥长度和ECB模式进行加密。For a description of the permitted values of this variable, see Section 5.1.8, “Server System Variables”.有关此变量允许值的说明，请参阅第5.1.8节“服务器系统变量”。
The optional 可选的init_vector argument provides an initialization vector for block encryption modes that require it.init_vector参数为需要它的块加密模式提供初始化向量。
For modes that require the optional 对于需要可选init_vector argument, it must be 16 bytes or longer (bytes in excess of 16 are ignored). init_vector参数的模式，该参数必须为16字节或更长（超过16的字节将被忽略）。An error occurs if 如果缺少init_vector is missing.init_vector，则会发生错误。
For modes that do not require 对于不需要init_vector, it is ignored and a warning is generated if it is specified.init_vector的模式，如果指定了该向量，则忽略该向量并生成警告。
A random string of bytes to use for the initialization vector can be produced by calling 可以通过调用RANDOM_BYTES(16). RANDOM_BYTES(16)来生成用于初始化向量的随机字节字符串。For encryption modes that require an initialization vector, the same vector must be used for encryption and decryption.对于需要初始化向量的加密模式，必须使用相同的向量进行加密和解密。
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 | +-----------------------------------------------+
The following table lists each permitted block encryption mode and whether the initialization vector argument is required.下表列出了每个允许的块加密模式以及是否需要初始化向量参数。
| Block Encryption Mode | Initialization Vector Required |
|---|---|
| ECB | No |
| CBC | Yes |
| CFB1 | Yes |
| CFB8 | Yes |
| CFB128 | Yes |
| OFB | Yes |
Statements that use 使用AES_ENCRYPT() or AES_DECRYPT() are unsafe for statement-based replication.AES_ENCRYPT()或AES_DECRYPT()的语句对于基于语句的复制是不安全的。
If 如果从mysql客户机中调用AES_ENCRYPT() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. AES_ENCRYPT()，则二进制字符串将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql命令行客户端”。
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 此函数要求MySQL使用压缩库（如zlib. zlib）编译。Otherwise, the return value is always 否则，返回值总是NULL. NULL。The compressed string can be uncompressed with 压缩的字符串可以用UNCOMPRESS().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:压缩字符串内容按以下方式存储：
Empty strings are stored as empty strings.
Nonempty strings are stored as a 4-byte length of the uncompressed string (low byte first), followed by the compressed string. 非空字符串存储为未压缩字符串的4字节长度（先是低字节），然后是压缩字符串。If the string ends with space, an extra 如果字符串以空格结尾，则会添加一个额外的. character is added to avoid problems with endspace trimming should the result be stored in a CHAR or VARCHAR column. .字符，以避免如果结果存储在CHAR或VARCHAR列中会带来的末尾空格修剪问题。(However, use of nonbinary string data types such as （但是，不建议使用非二进制字符串数据类型（如CHAR or VARCHAR to store compressed strings is not recommended anyway because character set conversion may occur. CHAR或VARCHAR）来存储压缩字符串，因为可能会发生字符集转换。Use a 请改用VARBINARY or BLOB binary string column instead.)VARBINARY或BLOB二进制字符串列。）
If 如果从mysql客户机中调用COMPRESS() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. COMPRESS()，则二进制字符串将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql命令行客户端”。
Calculates an MD5 128-bit checksum for the string. 计算字符串的MD5 128位校验和。The value is returned as a string of 32 hexadecimal digits, or 值以32位十六进制数字的字符串形式返回，如果参数为NULL if the argument was NULL. NULL，则返回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.返回值是连接字符集中的字符串。
If FIPS mode is enabled, 如果启用了FIPS模式，MD5() returns NULL. MD5()将返回NULL。See Section 6.8, “FIPS Support”.参见第6.8节，“FIPS支持”。
mysql> SELECT MD5('testing');
-> 'ae2b1fca515949e5d54fb22b8ed95575'
This is the “RSA Data Security, Inc. MD5 Message-Digest Algorithm.”这就是“RSA Data Security，Inc.MD5消息摘要算法”。
See the note regarding the MD5 algorithm at the beginning this section.请参阅本节开头有关MD5算法的注释。
This function returns a binary string of 此函数返回使用SSL库的随机数生成器生成的len random bytes generated using the random number generator of the SSL library. len随机字节的二进制字符串。Permitted values of len range from 1 to 1024. len的允许值范围从1到1024。For values outside that range, an error occurs.对于超出该范围的值，将发生错误。
RANDOM_BYTES() can be used to provide the initialization vector for the AES_DECRYPT() and AES_ENCRYPT() functions. RANDOM_BYTES()可用于为AES_DECRYPT()和AES_ENCRYPT()函数提供初始化向量。For use in that context, 要在该上下文中使用，len must be at least 16. len必须至少为16。Larger values are permitted, but bytes in excess of 16 are ignored.允许较大的值，但超过16的字节将被忽略。
RANDOM_BYTES() generates a random value, which makes its result nondeterministic. RANDOM_BYTES()生成一个随机值，使其结果不确定。Consequently, statements that use this function are unsafe for statement-based replication.因此，使用此函数的语句对于基于语句的复制是不安全的。
If 如果从mysql客户机中调用RANDOM_BYTES() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. RANDOM_BYTES()，则二进制字符串将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql命令行客户端”。
Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). 计算字符串的SHA-1 160位校验和，如RFC 3174（安全哈希算法）中所述。The value is returned as a string of 40 hexadecimal digits, or 值以40个十六进制数字的字符串形式返回，如果参数为NULL if the argument was NULL. NULL，则返回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().SHA()与SHA1()同义。
The return value is a string in the connection character set.返回值是连接字符集中的字符串。
mysql> SELECT SHA1('abc');
-> 'a9993e364706816aba3e25717850c26c9cd0d89d'
SHA1() can be considered a cryptographically more secure equivalent of MD5(). SHA1()可以被认为是MD5()在加密上更安全的等价物。However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section.但是，请参阅本节开头有关MD5和SHA-1算法的说明。
Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). 计算SHA-2哈希函数族（SHA-224、SHA-256、SHA-384和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). 第二个参数表示结果的所需位长度，其值必须为224、256、384、512或0（相当于256)。If either argument is 如果任一参数为NULL or the hash length is not one of the permitted values, the return value is NULL. NULL或哈希长度不是允许的值之一，则返回值为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'
This function works only if MySQL has been configured with SSL support. 仅当MySQL配置了SSL支持时，此函数才起作用。See Section 6.3, “Using Encrypted Connections”.请参阅第6.3节，“使用加密连接”。
SHA2() can be considered cryptographically more secure than MD5() or SHA1().SHA2()可以被认为比MD5()或SHA1()更安全。
Given an SQL statement as a string, returns the statement digest hash value as a string in the connection character set, or 给定一个作为字符串的SQL语句，将语句摘要哈希值作为连接字符集中的字符串返回，如果参数为NULL if the argument is NULL. NULL，则返回NULL。The related 相关的STATEMENT_DIGEST_TEXT() function returns the normalized statement digest. STATEMENT_DIGEST_TEXT()函数的作用是：返回规范化的语句摘要。For information about statement digesting, see Section 27.10, “Performance Schema Statement Digests and Sampling”.有关语句摘要的信息，请参阅第27.10节，“性能模式语句摘要和采样”。
Both functions use the MySQL parser to parse the statement. 这两个函数都使用MySQL解析器来解析语句。If parsing fails, an error occurs. 如果解析失败，则会发生错误。The error message includes the parse error only if the statement is provided as a literal string.只有当语句作为文本字符串提供时，错误消息才会包含解析错误。
The max_digest_length system variable determines the maximum number of bytes available to these functions for computing normalized statement digests.max_digest_length系统变量确定这些函数可用于计算规范化语句摘要的最大字节数。
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)
Given an SQL statement as a string, returns the normalized statement digest as a string in the connection character set, or 给定一个作为字符串的SQL语句，则在连接字符集中以字符串形式返回规范化的语句摘要，如果参数为NULL if the argument is NULL. NULL，则返回NULL。For additional discussion and examples, see the description of the related 有关其他讨论和示例，请参阅相关的STATEMENT_DIGEST() function.STATEMENT_DIGEST函数的说明。
UNCOMPRESS(string_to_uncompress)
Uncompresses a string compressed by the 解压缩由COMPRESS() function. COMPRESS()函数压缩的字符串。If the argument is not a compressed value, the result is 如果参数不是压缩值，则结果为NULL. NULL。This function requires MySQL to have been compiled with a compression library such as 此函数要求MySQL使用压缩库（如zlib. zlib)编译。Otherwise, the return value is always 否则，返回值始终是NULL.NULL。
mysql>SELECT UNCOMPRESS(COMPRESS('any string'));-> 'any string' mysql>SELECT UNCOMPRESS('any string');-> NULL
UNCOMPRESSED_LENGTH(compressed_string)
Returns the length that the compressed string had before being compressed.返回压缩字符串在被压缩之前的长度。
mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
-> 30VALIDATE_PASSWORD_STRENGTH(str)
Given an argument representing a plaintext password, this function returns an integer to indicate how strong the password is. 给定一个表示明文密码的参数，此函数返回一个整数，以指示密码的强度。The return value ranges from 0 (weak) to 100 (strong).返回值的范围从0（弱）到100（强）。
Password assessment by VALIDATE_PASSWORD_STRENGTH() is done by the validate_password component. VALIDATE_Password_STRENGTH()的密码评估由validate_password组件完成。If that component is not installed, the function always returns 0. 如果未安装该组件，则函数始终返回0。For information about installing 有关安装validate_password, see Section 6.4.3, “The Password Validation Component”. validate_password的信息，请参阅第6.4.3节，“密码验证组件”。To examine or configure the parameters that affect password testing, check or set the system variables implemented by 要检查或配置影响密码测试的参数，请检查或设置由validate_password. validate_password实现的系统变量。See Section 6.4.3.2, “Password Validation Options and Variables”.参见第6.4.3.2节，“密码验证选项和变量”。
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.validate_password.check_user_name系统变量，并且密码与用户名匹配，则无论其他validate_password系统变量如何设置，VALIDATE_PASSWORD_STRENGTH()都返回0。
| Length < 4 | 0 |
Length ≥ 4 and < validate_password.length | 25 |
Satisfies policy 1 (LOW) | 50 |
Satisfies policy 2 (MEDIUM) | 75 |
Satisfies policy 3 (STRONG) | 100 |