KingbaseES 实现MYSQL hex/unhex 函数
阅读原文时间:2023年07月09日阅读:2

MySQL 的hex 和 unhex 函数类似于KingbaseES 的encode 和 decoding,实现字符与16进制之间的转换。

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)

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)

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)