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)