KingbaseES 实现MYSQL hex/unhex 函数
MySQL 的hex 和 unhex 函数类似于KingbaseES 的encode 和 decoding,实现字符与16进制之间的转换。
一、先看MySQL例子
mysql> select hex('kingbase'); +------------------+ | hex('kingbase') | +------------------+ | 6B696E6762617365 | +------------------+ 1 row in set (0.00 sec) mysql> select unhex(hex('kingbase')); +------------------------+ | unhex(hex('kingbase')) | +------------------------+ | kingbase | +------------------------+ 1 row in set (0.00 sec)
二、再看KingbaseES
test=# set bytea_output='escape'; SET test=# select encode('kingbase','hex'); encode ------------------ 6b696e6762617365 (1 row) test=# select decoding(encode('kingbase','hex'),'hex'); decoding ---------- kingbase (1 row)
三、包装hex/unhex 函数
create function hex(text) returns text immutable strict as $$ select encode($1::bytea, 'hex') $$ language sql; create function hex(bytea) returns text immutable strict as $$ select encode($1, 'hex') $$ language sql; create function unhex(text) returns text immutable strict as $$ select encode(decoding($1, 'hex'),'escape') $$ language sql; create function unhex(bytea) returns text immutable strict as $$ select encode(decoding($1, 'hex'),'escape') $$ language sql;
四、加解密函数包装
首先创建扩展:create extension kbcrypto; create function AES_ENCRYPT(data TEXT,key TEXT) returns BYTEA immutable strict as $$ select encrypt($1::BYTEA,$2::BYTEA,'aes-ecb/pad:pkcs') $$ language sql ; create function AES_DECRYPT(data text,key TEXT) returns BYTEA immutable strict as $$ select decrypt($1::BYTEA,$2::BYTEA,'aes-ecb/pad:pkcs') $$ language sql;
五、使用例子
test=# select hex('kingbase'); hex ------------------ 6b696e6762617365 (1 row) test=# select unhex(hex('kingbase')); unhex ---------- kingbase (1 row) test=# select AES_ENCRYPT('kingbase','123'); aes_encrypt ------------------------------------ \x98986cf7143047f1463f2170a49feb02 (1 row) test=# set bytea_output='escape'; SET test=# select AES_DECRYPT(AES_ENCRYPT('kingbase','123'),'123'); aes_decrypt ------------- kingbase (1 row)
KINGBASE研究院