人大金仓数据库兼容mysql中hex,unhex函数

关键字： KingbaseES、人大金仓、sql函数、hex、unhex

函数调研

mysql8 hex函数调研

语法：hex(arg);

功能：将一个字符串或数字转换为十六进制格式的字符串。

其中参数arg分为两种：字符串参数str，数字参数N。

• 对于字符串参数str，hex返回str的十六进制字符串，其中str中每个字符的每个字节的编码以两个十六进制数字表示。
• 对于数字参数N分为两种情况：N为整数时，若超出bigint最大范围，则输出7FFFFFFFFFFFFFFF，若未超出hex将N值的十六进制字符串表示将10进制数以16进制形式显示；N为小数时，会先将N四舍五入为整数再进行hex函数执行。
• 若参数是其他类型，会先转化为字符串然后按字符串进行处理。

mysql8 unhex函数调研

语法：unhex(arg);

功能：将参数种每对十六进制数字转换为该数字表示的字符。

其中参数arg分为两种：字符串参数str，数字参数N。

• 对于字符串参数str，将参数中的每对字符解释为十六进制数字，并将其为该数字所表示的字节。返回类型为binary。
• 参数字符串中的字符必须合法的十六进制数字：“0”…“9”，“A”…“F”，“a”…”f”,如果参数遇到任何非十六进制数字，则返回NULL；
• 对于数值参数N，不接受小数，负数形式参数，返回null，只允许参数为正整数，将N转为字符串处理；
• 对于其他类型参数，返回NULL

推导分析

mysql hex测试用例

函数	用例	结果
hex(str)	select hex('');	空
	select hex(' ');	20
	select hex(null);	null
	select hex('aaa');	616161
	select hex('111');	313131
	select hex('1a1');	316131
	select hex('*');	2A
	select hex('M');	4D
	select hex('M^1');	4D5E31
	select hex('给');	E7BB99
	select hex('2023-08-16 00:00:00');	323032332D30382D31362030303A30303A3030
	select hex(to_date('2023-08-16 00:00:00','%Y-%m-%d'));	323032332D30382D31362030303A30303A3030
	select hex('09:30:00');	30393A33303A3030
	select hex(time('09:30:00'));	30393A33303A3030
	select hex('hello');	68656C6C6F
	select hex(cast('hello' as binary));	68656C6C6F
	select hex(cast('10' as double));	A
	select hex(10);	A
hex(N)	select hex(12);	C
	select hex(12.5);	D
	select hex(12.3);	C
	select hex(12.8);	D
	select hex(-12);	FFFFFFFFFFFFFFF4
	select hex(-12.2);	FFFFFFFFFFFFFFF4
	select hex(-12.5);	FFFFFFFFFFFFFFF3
	select hex(123a);	报错
	select hex(100000000000000000000000000);	7FFFFFFFFFFFFFFF，warning

mysql unhex测试用例

函数	用例	mysql结果
unhex（）	--skip-binary-as-hex
	select unhex(-6161);	Null，warning
	select unhex(123.123);	Null，warning
	select unhex(-123.123);	Null，warning
	select unhex('6161');	aa
	select unhex(6161);	aa
	select unhex('6161.6262');	Null，warning
	select unhex('asd');	Null，warning
	select unhex(346);	F
	select unhex('346');	F
	select unhex('');	空
	select unhex(' ');	Null，warning
	select unhex(null);	Null
	不加—skip-binary-as-hex
	select unhex(-6161);	Null，warning
	select unhex(123.123);	Null，warning
	select unhex(-123.123);	Null，warning
	select unhex('6161');	0x6161
	select unhex(6161);	0x6161
	select unhex('6161.6262');	Null，warning
	select unhex('asd');	Null，warning
	select unhex(346);	0x0346
	select unhex('346');	0x0346
	select unhex('');	0x
	select unhex(' ');	Null，warning
	select unhex(null);	Null
	select unhex(now());	Null，warning
	select unhex(cast('hello' as binary));	NULL

hex功能

1、 函数定义：text hex(text str)

函数功能：将str中每个字符对应的字节以十六进制数字表示。

• 如果参数是null则返回null，若为’’则返回null；
• 支持中文传参；
• 如果参数为其他类型，数据库会先将其转换为字符串类型再进行函数转化功能；

函数主要属性：

• proname：hex
• pronamespace：8000（sys）
• prokind：f（普通函数）
• proisstrict：t
• provolatile：i
• proparallel：s

2、 函数定义：text hex(numeric val)

函数功能：将val十进制数计算机中的字节表示以十六进制的字符串显示。

• 如果参数是null则返回null；
• 接受参数为小数，会先将小数四舍五入成整数；
• 参数支持负数。

函数主要属性：

• proname：hex
• pronamespace：8000（sys）
• prokind：f（普通函数）
• proisstrict：t
• provolatile：i
• proparallel：s

unhex功能

1. 函数定义：binary unhex(text str)；

函数功能：把十六进制格式的字符串返回binary类型的值。

• 参数字符串中的字符必须合法的十六进制数字：“0”…“9”，“A”…“F”，“a”…”f”,如果参数遇到任何非十六进制数字，则返回NULL；
• 参数输入null传出null；
• 参数若为整数类型，将其转换字符串进行处理。
• 其他类型则返回NULL。

实现函数

3.1 unhex函数

Plsql: create function sys.unhex(text) returns binary as $$ declare a varchar; result varchar; begin if $1 is null then return null; else result := concat('0x',$1); return result::binary; end if; end; $$ language plpgsql; //c语言实现 Datum unhex(KDB_FUNCTION_ARGS) { text *val = KDB_GETARG_TEXT_P(0); Binary *res = NULL; text *temp = NULL; temp = (DatumGetTextP(DirectFunctionCall2(textcat,(Datum)cstring_to_text("0x"),PointerGetDatum(val)))); res = DatumGetVarBinaryP(DirectFunctionCall3(binaryin, PointerGetDatum(TextDatumGetCString(temp)), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1))); KDB_RETURN_BINARY_P(res); }

• 1. 3.2 hex函数

create or replace function sys.hex(numeric) returns text as $$ select upper(to_hex($1::bigint)); $$ language sql; CREATE OR REPLACE FUNCTION hex(int4) RETURNS text AS $$select hex($1::numeric)$$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION hex(double) RETURNS text AS $$select hex($1::numeric)$$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION hex(timestamp) RETURNS text AS $$select hex($1::text)$$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION hex(time) RETURNS text AS $$select hex($1::text)$$ LANGUAGE SQL; -- 参数为文本 create function sys.hex(text) returns text as $$ select upper(encode($1::bytea,'hex')); $$ language sql;

参考资料

《KingbaseES SQL 语言参考手册》