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                                                                  |