Name Description AES_DECRYPT() Decrypt using AES AES_ENCRYPT() Encrypt using AES COMPRESS() Return result as a binary string MD5() Calculate MD5 checksum RANDOM_BYTES() Return a random byte vector SHA1() ,  SHA() Calculate an SHA-1 160-bit checksum SHA2() Calculate an SHA-2 checksum STATEMENT_DIGEST() Compute statement digest hash value STATEMENT_DIGEST_TEXT() Compute normalized statement digest UNCOMPRESS() Uncompress a string compressed UNCOMPRESSED_LENGTH() Return the length of a string before compression VALIDATE_PASSWORD_STRENGTH() Determine strength of password mysql>  SELECT BIT_COUNT(’64’), BIT_COUNT(_binary ’64’); -> 1, 7 mysql>  SELECT BIT_COUNT(X’40’), BIT_COUNT(_binary X’40’); -> 1, 1 Encryption and Compression Functions 12.14 Encryption and Compression Functions Table 12.18 Encryption Functions 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. 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 ). Some encryption functions return strings of ASCII characters:  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. This is a nonbinary string unless the character set is  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( N )  column. 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  is 16 for an MD5()  value and 20 for a  SHA1()  value. For  SHA2() ,  N  ranges from 28 to 32 depending on the argument specifying the desired bit length of the result. 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). 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: To convert hex strings to more compact form, modify the application to use  UNHEX()  and  BINARY(16) instead as follows: CREATE TABLE md5_tbl (md5_val CHAR(32), ...); INSERT INTO md5_tbl (md5_val, ...) VALUES(MD5(’abcdef’), ...); CREATE TABLE md5_tbl (md5_val BINARY(16), ...); 2271