关键字： 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功能
- 函数定义：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); } |
- 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 语言参考手册》
