可变长二进制函数详解 - 云原生数据仓库AnalyticDB

可变长二进制函数用于处理二进制数据类型（如BINARY、VARBINARY、BLOB等），比如加解密、提取子串、计算长度等。本文介绍云原生数据仓库 AnalyticDB MySQL 版可变长二进制函数的用法与示例。

AES_DECRYPT：使用AES算法进行数据解密。
AES_DECRYPT_MY：使用AES算法进行数据解密。
AES_ENCRYPT：使用AES算法进行数据加密。
AES_ENCRYPT_MY：使用AES算法进行数据加密。
CHAR_LENGTH：以字符为单位返回字符串的长度。
COMPRESS：压缩字符串X并将结果作为二进制字符串返回。
CRC32：返回参数x的循环冗余码。
ENCRYPT：对字符串进行加密。
FROM_BASE64：解码BASE64编码的字符串并返回结果。
GUNZIP：基于GZIP格式解压二进制字符串X，返回解压后的二进制字符串。
GZIP：基于GZIP格式压缩二进制字符串X，并将结果作为二进制字符串返回。
HEX：将字符串或数字转换为十六进制形式，并返回结果。
LEFT：返回字符串最左边的`y`个字符。
LENGTH：以字节为单位返回参数的长度。
LOWER：返回参数的小写形式。
LPAD：左拼接字符串。
LTRIM：移除字符串左边的空白字符。
MD5：计算参数MD5的hash值。
ORD：如果字符串最左边的字符是多字节字符，则返回该字符的代码。
REPEAT：返回重复指定次数的字符串。
REVERSE：反转字符串中的字符。
RIGHT：返回字符串最右边的`y`个字符。
RPAD：右拼接字符串。
RTRIM：移除字符串右边的空白字符。
SHA1：计算字符串的SHA-1校验和。
SHA2：计算SHA-2校验和。
SUBSTR：返回指定子字符串。
TO_BASE64：返回字符串的BASE64编码形式。
TRIM：移除字符串左右两边的空白字符。
UNCOMPRESS：解压缩由COMPRESS()函数压缩的字符串。
UNCOMPRESSED_LENGTH：在压缩之前返回一个字符串的长度。
UNHEX：将参数中的每对字符转换为十六进制形式，再将其转换为数字表示的字节，最后以二进制字符串形式返回结果。
UNZIP：基于ZIP格式解压二进制字符串X，返回解压后的二进制字符串。
UPPER：返回参数的大写形式。
ZIP：基于ZIP格式压缩二进制字符串X，并将结果作为二进制字符串返回。

注意事项

部分函数因不同客户端解析的编码不同，可能会出现返回值不一样，会将“China”解析为"0x4368696E61"。本文中的所有示例的返回结果均以DMS中的结果为例。

AES_DECRYPT

AES_DECRYPT(VARBINARY X, VARCHAR Y)

命令说明：使用AES算法解密X，Y为密钥。
返回值类型：VARBINARY

示例：

SELECT HEX(AES_DECRYPT(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'),'0123'));

返回结果如下：

+-----------------------------------------------------------------------------+
| HEX(AES_DECRYPT(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'),'0123'))    |
+-----------------------------------------------------------------------------+
|   4368696E61                                                                |
+-----------------------------------------------------------------------------+

AES_DECRYPT_MY

重要

AES_DECRYPT_MY函数兼容MySQL，MySQL中加密的数据可以在AnalyticDB for MySQL中解密。
仅3.1.10.0及以上内核版本的集群支持使用AES_DECRYPT_MY函数解密数据。
查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
查看和升级数仓版集群的内核版本，请参见查看和升级版本。

AES_DECRYPT_MY(VARBINARY X, VARCHAR Y)

命令说明：使用AES算法解密X，Y为密钥。
返回值类型：VARBINARY

示例：

SELECT CAST(AES_DECRYPT_MY(UNHEX('CF3AB34E18CD73CE37D46AD2588BA258'),'key_string') AS CHAR);

返回结果如下：

+---------------------------------------------------------------------------------------+
| CAST(AES_DECRYPT_MY(UNHEX('CF3AB34E18CD73CE37D46AD2588BA258'),'key_string') AS CHAR)  |
+---------------------------------------------------------------------------------------+
| China                                                                                 |
+---------------------------------------------------------------------------------------+

AES_ENCRYPT

AES_ENCRYPT(VARBINARY X, VARCHAR Y)

命令说明：使用AES算法加密X，Y为密钥。
返回值类型：VARBINARY

示例：

SELECT HEX(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'));

返回结果如下：

+-------------------------------------------------------+
|HEX(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'))   |
+-------------------------------------------------------+
| 6C5CB5E59E3EA58E58CFBE3C76BFBECA                      |
+-------------------------------------------------------+

AES_ENCRYPT_MY

重要

AES_ENCRYPT_MY函数兼容MySQL，AnalyticDB for MySQL中加密的数据可以在MySQL中解密。
仅3.1.10.0及以上内核版本的集群支持使用AES_ENCRYPT_MY函数加密数据。
查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
查看和升级数仓版集群的内核版本，请参见查看和升级版本。

AES_ENCRYPT_MY(VARBINARY X, VARCHAR Y)

命令说明：使用AES算法加密X，Y为密钥。
返回值类型：VARBINARY

示例：

SELECT HEX(AES_ENCRYPT_MY('China', 'key_string'));

返回结果如下：

+-------------------------------------------------------+
|HEX(AES_ENCRYPT_MY('China', 'key_string'))             |
+-------------------------------------------------------+
|CF3AB34E18CD73CE37D46AD2588BA258                       | 
+-------------------------------------------------------+

CHAR_LENGTH

CHAR_LENGTH(VARBINARY X)

命令说明：以字符为单位返回字符串X的长度。
返回值类型：LONG

示例：

SELECT CHAR_LENGTH(CAST('ABC' AS VARBINARY));

返回结果如下：

+---------------------------------------+
| CHAR_LENGTH(CAST('ABC' AS VARBINARY)) |
+---------------------------------------+
|3                                      | 
+---------------------------------------+

COMPRESS

COMPRESS(VARBINARY X)

命令说明：压缩字符串X并将结果作为二进制字符串返回。
返回值类型：VARBINARY

示例：

SELECT HEX(COMPRESS(CAST('China' AS VARBINARY)));

返回结果如下：

+--------------------------------------------+
| HEX(COMPRESS(CAST('China' AS VARBINARY)))  |
+--------------------------------------------+
|  05000000789C73CEC8CC4B0400056C01E4        |
+--------------------------------------------+

CRC32

CRC32(VARBINARY X)

命令说明：返回参数X的循环冗余码。
返回值类型：LONG

示例：

SELECT CRC32(CAST('China' AS VARBINARY));

返回结果：

+------------------------------------+
| CRC32(CAST('China' AS VARBINARY))  |
+------------------------------------+
| 2704207136                         | 
+------------------------------------+

ENCRYPT

ENCRYPT(VARBINARY X, VARCHAR Y)

命令说明：对参数X进行加密，Y为Salt值。
返回值类型：BLOB

示例：

SELECT ENCRYPT('ABDABC123','KEY');

返回结果如下：

+-----------------------------+
| ENCRYPT('ABDABC123','KEY')  |
+-----------------------------+
| kezazmcIo.aCw               |
+-----------------------------+

FROM_BASE64

FROM_BASE64(VARBINARY X)

命令说明：解码BASE64编码的字符串X并返回结果。
返回值类型：BLOB

示例：

SELECT FROM_BASE64(TO_BASE64(CAST('abc' AS VARBINARY)));

返回结果如下：

+--------------------------------------------------+
| FROM_BASE64(TO_BASE64(CAST('abc' AS VARBINARY))) |
+--------------------------------------------------+
| abc                                              |
+--------------------------------------------------+

GUNZIP

重要

仅3.1.9.3及以上内核版本的集群支持使用GUNZIP函数。

查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

查看和升级数仓版集群的内核版本，请参见查看和升级版本。

GUNZIP(VARBINARY X)

命令说明：基于GZIP格式解压二进制字符串X，返回解压后的二进制字符串。
返回值类型：VARBINARY。

示例：

SELECT GUNZIP(GZIP(CAST('China' AS VARBINARY)));

返回结果如下：

+--------------------------------------------+
| GUNZIP(GZIP(CAST('China' AS VARBINARY)))   |
+--------------------------------------------+
| China                                      |  
+--------------------------------------------+

GZIP

重要

仅3.1.9.3及以上内核版本的集群支持使用GZIP函数。

查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

查看和升级数仓版集群的内核版本，请参见查看和升级版本。

GZIP(VARBINARY X)

命令说明：基于GZIP格式压缩二进制字符串X，并将结果作为二进制字符串返回。
返回值类型：VARBINARY。

示例：

SELECT HEX(GZIP(CAST('China' AS VARBINARY)));

返回结果如下：

+------------------------------------------------------+
| HEX(GZIP(CAST('China' AS VARBINARY)))                |
+------------------------------------------------------+
|1F8B080000000000000073CEC8CC4B040020ED2EA105000000    | 
+------------------------------------------------------+

HEX

HEX(VARBINARY X)

命令说明：将字符串X或数字转换为十六进制格式的字符串。
返回值类型：VARCHAR

示例：

SELECT HEX(CAST('China' AS VARBINARY));

返回结果如下：

+----------------------------------+
| HEX(CAST('China' AS VARBINARY))  |
+----------------------------------+
|  4368696E61                      | 
+----------------------------------+

LEFT

LEFT(VARBINARY X, BIGINT Y)
LEFT(VARBINARY X, DOUBLE Y)

命令说明：返回字符串X最左边的Y个字符。
返回值类型：BLOB

示例：

SELECT LEFT(CAST('China' AS VARBINARY),1000);

返回结果如下：

+-----------------------------------------+
| LEFT(CAST('China' AS VARBINARY), 1000)  |
+-----------------------------------------+
| China                                   | 
+-----------------------------------------+

LENGTH

LENGTH(VARBINARY X)

命令说明：以字节为单位返回参数X的长度。
返回值类型：LONG

示例：

SELECT LENGTH(CAST('ABC' AS VARBINARY));

返回结果如下

 +----------------------------------+
 | LENGTH(CAST('ABC' AS VARBINARY)) |
 +----------------------------------+
 | 3                                |   
 +----------------------------------+

LOWER

LOWER(VARBINARY X)

命令说明：返回参数X的小写形式。
返回值类型：VARBINARY

示例：

SELECT LOWER(CAST('ABC' AS VARBINARY));

返回结果如下：

+---------------------------------+
| LOWER(CAST('ABC' AS VARBINARY)) |
+---------------------------------+
| abc                             | 
+---------------------------------+

LPAD

LPAD(VARBINARY X, BIGINT Y, VARCHAR Z)
LPAD(VARBINARY X, DOUBLE Y, VARCHAR Z)

命令说明：将字符串X左边拼接字符串z直到长度达到Y，并返回拼接后的字符串。
如果X长于Y，则返回值将缩短为y个字符。
返回值类型：VARBINARY

示例：

SELECT HEX(LPAD(CAST('China' AS VARBINARY), 7,'-'));

返回结果如下：

+------------------------------------------------+
| HEX(LPAD(CAST('China' AS VARBINARY), 7, '-'))  |
+------------------------------------------------+
| 2D2D4368696E61                                 | 
+------------------------------------------------+

LTRIM

LTRIM(VARBINARY X)

命令说明：移除字符串X左边的空白字符。
返回值类型：BLOB

示例：

SELECT LTRIM(CAST('  China  ' AS VARBINARY));

返回结果如下：

+----------------------------------------+
| LTRIM(CAST('  China  ' AS VARBINARY))  |
+----------------------------------------+
| China                                  |
+----------------------------------------+

MD5

MD5(VARBINARY X)

命令说明：返回参数XMD5的hash值。
返回值类型：VARCHAR

示例：

SELECT MD5(CAST('China' AS VARBINARY));

返回结果如下：

+----------------------------------+
| MD5(CAST('China' AS VARBINARY))  |
+----------------------------------+
| ae54a5c026f31ada088992587d92cb3a | 
+----------------------------------+

ORD

ORD(VARBINARY X)

命令说明：如果字符串X最左边的字符是多字节字符，则返回该字符的代码。
返回值类型：LONG

示例：

SELECT ORD(CAST('China' AS VARBINARY));

返回结果如下：

+----------------------------------+
| ORD(CAST('China' AS VARBINARY))  |
+----------------------------------+
|67                                | 
+----------------------------------+

REPEAT

REPEAT(VARBINARY X, DOUBLE Y)
REPEAT(VARBINARY X, BIGINT Y)

命令说明：返回重复指定次数Y的字符串X。
返回值类型：VARBINARY

示例：

SELECT HEX(REPEAT(CAST('China' AS VARBINARY),1));

返回结果如下：

+---------------------------------------------+
| HEX(REPEAT(CAST('China' AS VARBINARY), 1))  |
+---------------------------------------------+
|  4368696E61                                 | 
+---------------------------------------------+

REVERSE

REVERSE(VARBINARY X)

命令说明：反转字符串X中的字符。
返回值类型：VARBINARY

示例：

SELECT HEX(REVERSE(CAST('China' AS VARBINARY)));

返回结果如下：

+-------------------------------------------+
| HEX(REVERSE(CAST('China' AS VARBINARY)))  |
+-------------------------------------------+
|  616E696843                               |
+-------------------------------------------+

RIGHT

RIGHT(VARBINARY X, BIGINT Y)
RIGHT(VARBINARY X, DOUBLE Y)

命令说明：返回字符串X最右边的Y个字符。
返回值类型：VARBINARY

示例：

SELECT HEX(RIGHT(CAST('China' AS VARBINARY),1));

返回结果如下：

+--------------------------------------------+
| HEX(RIGHT(CAST('China' AS VARBINARY), 1))  |
+--------------------------------------------+
| 61                                         |
+--------------------------------------------+

RPAD

RPAD(VARBINARY X, BIGINT Y, VARCHAR Z)
RPAD(VARBINARY X, DOUBLE Y, VARCHAR Z)

命令说明：将字符串X右边拼接字符串z直到长度达到Y，并返回拼接后的字符串。
如果X长于Y，则返回值将缩短为Y个字符。
返回值类型：VARBINARY

示例：

SELECT HEX(RPAD(CAST('China' AS VARBINARY), 4.7,'x'));

返回结果如下：

+--------------------------------------------------+
| HEX(RPAD(CAST('China' AS VARBINARY), 4.7, 'x'))  |
+--------------------------------------------------+
| 4368696E61                                       | 
+--------------------------------------------------+

RTRIM

RTRIM(VARBINARY X)

命令说明：移除字符串X右边的空白字符。
返回值类型：BLOB

示例：

SELECT RTRIM(CAST('  China  ' AS VARBINARY));

返回结果如下：

+----------------------------------------+
| RTRIM(CAST('  China  ' AS VARBINARY))  |
+----------------------------------------+
|   China                                | 
+----------------------------------------+

SHA1

SHA1(VARBINARY X)

命令说明：计算字符串X的SHA-1校验和。
返回值类型：VARCHAR

示例：

SELECT SHA1(CAST('China' AS VARBINARY));

返回结果如下：

+------------------------------------------+
| SHA1(CAST('China' AS VARBINARY))         |
+------------------------------------------+
| d2eaf2aa1512d6596e0a5bae633537c6b8e779a3 |      
+------------------------------------------+

SHA2

SHA2(VARBINARY X, INTEGER Y)
SHA2(VARBINARY X, VARCHAR Y)

命令说明：计算字符串XSHA-2系列散列函数（SHA-224、SHA-256、SHA-384和SHA-512）。X是要清理的明文字符串，Y表示结果所需的位数长度，其值必须为224、256、384、512或0。
返回值类型：VARCHAR

示例：

SELECT SHA2(CAST('China' AS VARBINARY),256);

返回结果如下：

+------------------------------------------------------------------+
| SHA2(CAST('China' AS VARBINARY),256)                            |
+------------------------------------------------------------------+
| 10436829032f361a3de50048de41755140e581467bc1895e6c1a17f423e42d10 |
+------------------------------------------------------------------+

SUBSTR

SUBSTR(VARBINARY X, BIGINT Y, DOUBLE Z)
SUBSTR(VARBINARY X, DOUBLE Y, DOUBLE Z)
SUBSTR(VARBINARY X, BIGINT Y, BIGINT Z)
SUBSTR(VARBINARY X, DOUBLE Y, BIGINT Z)
SUBSTR(VARBINARY X, DOUBLE Y)
SUBSTR(VARBINARY X, BIGINT Y)

命令说明：返回指定子字符串。
返回值类型：VARBINARY
示例：

TO_BASE64

TO_BASE64(VARBINARY X)

命令说明：返回字符串X的BASE64编码形式。
返回值类型：VARCHAR

示例：

SELECT TO_BASE64(CAST('China' AS VARBINARY));

返回结果如下：

+----------------------------------------+
| TO_BASE64(CAST('China' AS VARBINARY))  |
+----------------------------------------+
| Q2hpbmE=                               |
+----------------------------------------+

TRIM

TRIM(VARBINARY X)

命令说明：移除字符串X左右两边的空白字符。
返回值类型：BLOB

示例：

SELECT TRIM(CAST('  China  ' AS VARBINARY));

返回结果如下：

+---------------------------------------+
| TRIM(CAST('  China  ' AS VARBINARY))  |
+---------------------------------------+
| China                                 | 
+---------------------------------------+

UNCOMPRESS

UNCOMPRESS(VARBINARY X)

命令说明：解压缩X由COMPRESS()函数压缩的字符串。
返回值类型：BLOB

示例：

SELECT UNCOMPRESS(COMPRESS(CAST('China' AS VARBINARY)));

返回结果如下：

+---------------------------------------------------+
| UNCOMPRESS(COMPRESS(CAST('China' AS VARBINARY)))  |
+---------------------------------------------------+
| China                                             | 
+---------------------------------------------------+

UNCOMPRESSED_LENGTH

UNCOMPRESSED_LENGTH(VARBINARY X)

命令说明：在压缩之前返回字符串X的长度。
返回值类型：LONG

示例：

SELECT UNCOMPRESSED_LENGTH(COMPRESS(CAST('China' AS VARBINARY)));

返回结果如下：

+------------------------------------------------------------+
| UNCOMPRESSED_LENGTH(COMPRESS(CAST('China' AS VARBINARY)))  |
+------------------------------------------------------------+
| 5                                                          |
+------------------------------------------------------------+

UNHEX

UNHEX(VARBINARY X)

命令说明：将参数X中的每对字符解释为十六进制数，并将其转换为数字表示的字节，以二进制字符串将其返回。
返回值类型：VARBINARY

示例：

SELECT UNHEX(CAST('China' AS VARBINARY));

返回结果如下：

+------------------------------------+
| UNHEX(CAST('China' AS VARBINARY))  |
+------------------------------------+
| NULL                               |  
+------------------------------------+

UNZIP

重要

仅3.1.9.3及以上内核版本的集群支持使用UNZIP函数。

查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

查看和升级数仓版集群的内核版本，请参见查看和升级版本。

UNZIP(VARBINARY X)

命令说明：基于ZIP格式解压二进制字符串X，返回解压后的二进制字符串。
返回值类型：VARBINARY。

示例：

SELECT UNZIP(ZIP(CAST('China' AS VARBINARY)));

返回结果如下：

+---------------------------------------+
| UNZIP(ZIP(CAST('China' AS VARBINARY)))|
+---------------------------------------+
| China                                 | 
+---------------------------------------+

UPPER

UPPER(VARBINARY X)

命令说明：返回参数X的大写形式。
返回值类型：VARBINARY

示例：

SELECT UPPER(CAST('abc' AS VARBINARY));

返回结果如下：

+---------------------------------+
| UPPER(CAST('abc' AS VARBINARY)) |
+---------------------------------+
| ABC                             | 
+---------------------------------+

ZIP

重要

仅3.1.9.3及以上内核版本的集群支持使用ZIP函数。

查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

查看和升级数仓版集群的内核版本，请参见查看和升级版本。

ZIP(VARBINARY X)

命令说明：基于ZIP格式压缩二进制字符串X，并将结果作为二进制字符串返回。
返回值类型：VARBINARY。

示例：

SELECT UNZIP(ZIP(CAST('China' AS VARBINARY)));

返回结果如下：

+---------------------------------------+
| UNZIP(ZIP(CAST('China' AS VARBINARY)))|
+---------------------------------------+
| China                                 | 
+---------------------------------------+