AnalyticDB for MySQL supports the following encryption and compression functions.

  • MD5: calculates the MD5 hash value of an argument.
  • SHA1: calculates the SHA-1 checksum of a string.
  • SHA2: calculates the SHA-2 checksum of a string.
  • COMPRESS: compresses a string and returns the result as a binary string.
  • UNCOMPRESS: decompresses a string compressed by the COMPRESS() function.
  • UNCOMPRESSED_LENGTH: returns the length of a string before compression.
  • AES_ENCRYPT: uses the AES algorithm to encrypt data.
  • AES_DECRYPT: uses the AES algorithm to decrypt data.

MD5

md5(varbinary x)            
  • Description: This function returns the MD5 hash value of the x argument.
  • Return value type: VARCHAR.
  • Example:
        select md5(CAST('China' AS VARBINARY));
        +----------------------------------+
        | md5(CAST('China' AS varbinary))   |
        +----------------------------------+
        | c13dceabcb143acd6c9298265d618a9f |                    

SHA1

sha1(varbinary x)            
  • Description: This function calculates the SHA-1 checksum of the x string.
  • Return value type: VARCHAR.
  • Example:
        select sha1(CAST('China' AS VARBINARY));
        +------------------------------------------+
        | sha1(CAST('China' AS varbinary))          |
        +------------------------------------------+
        | 101806f57c322fb403a9788c4c24b79650d02e77 |                    

SHA2

sha2(varbinary x, integer y)
sha2(varbinary x, varchar y)            
  • Description: This function returns the SHA-2 checksum of the x string. You can choose to use SHA-224, SHA-256, SHA-384, and SHA-512. x is the plaintext string to be hashed. y is the length of bits required to represent the result, which must be 224, 256, 384, 512, or 0.
  • Return value type: VARCHAR.
  • Example:
        select sha2(CAST('China' AS VARBINARY),256);
        ----------------+------------------------------------------------------------------
        | sha2(CAST('China' AS varbinary), 256)                             |
        ----------------+------------------------------------------------------------------
        | f0e9521611bb290d7b09b8cd14a63c3fe7cbf9a2f4e0090d8238d22403d35182 |                    

COMPRESS

compress(varbinary x)            
  • Description: This function compresses the x string and returns the result as a binary string.
  • Return value type: VARBINARY.
  • Example:
        select hex(compress(CAST('China' AS VARBINARY)));
        +--------------------------------------------+
        | hex(compress(CAST('China' AS varbinary)))   |
        +--------------------------------------------+
        | 06000000789C7BB263EDD3D97B01104C0487       |                    

UNCOMPRESS

uncompress(varbinary x)            
  • Description: This function decompresses the x string compressed by the COMPRESS() function.
  • Return value type: VARBINARY.
  • Example:
        select uncompress(compress(CAST('China' AS VARBINARY)));
        +---------------------------------------------------+
        | uncompress(compress(CAST('China' AS varbinary)))   |
        +---------------------------------------------------+
        | China                                              |                    

UNCOMPRESSED_LENGTH

uncompressed_length(varbinary x)            
  • Description: This function returns the length of the x string before compression.
  • Return value type: LONG.
  • Example:
        select uncompressed_length(compress(CAST('China' AS VARBINARY)));
        +------------------------------------------------------------+
        | uncompressed_length(compress(CAST('China' AS varbinary)))   |
        +------------------------------------------------------------+
        |                                                          6 |                    

AES_ENCRYPT

aes_encrypt(varbinary x, varchar y)           
  • Description: This function uses the AES algorithm to encrypt x with y as the key.
  • Return value type: VARBINARY.
  • Example:
        select HEX(aes_encrypt(CAST('China' AS VARBINARY), '0123'));
        +-------------------------------------------------------+
        | HEX(aes_encrypt(CAST('China' AS VARBINARY), '0123'))   |
        +-------------------------------------------------------+
        | DFB166F0A03113AA848C0CE545D58757                      |                    

AES_DECRYPT

aes_decrypt(varbinary x, varchar y)            
  • Description: This function uses the AES algorithm to decrypt x with y as the key.
  • Return value type: VARBINARY.
  • Example:
        select aes_decrypt(aes_encrypt(CAST('China' AS VARBINARY), '0123'),'0123');
        +-----------------------------------------------------------------------+
        | aes_decrypt(aes_encrypt(CAST('China' AS varbinary), '0123'), '0123')   |
        +-----------------------------------------------------------------------+
        | China                                                                  |