金仓数据库KingbaseES RAW数据类型介绍
关键字:
KingbaseES、RAW、数据类型
1. 什么是RAW类型?
RAW是一种可变长度的数据类型,可以在具有不同字符集的数据库之间,或者具有不同字符集的服务器和客户端之间传输二进制数据流,而不需要字符集转换。可以理解为“不需要解释的数据”。
RAW数据类型可以用于存储二进制数据或者字节串。例如,存储图形,声音,文档或二进制数据数组。解释取决于用途。
RAW数据类型的语法为:RAW(n)。其中参数n表示长度,以字节为单位,长度取值范围为1-32767。如果长度超出此范围,则报错。如果不指定长度,则无长度限制,KingbaseES中最大存储 1G-4 字节数据。RAW类型输出显示为十六进制的字符串。
LONG RAW类型为早期类型,ORACLE已不推荐使用,当前保留是为了向前兼容。所以KingbaseES中没有定义专属类型,只做语法兼容,映射到RAW类型。
2. RAW数据类型好处
在网络中的计算机之间传输RAW数据时,或者使用数据库程序将RAW数据从一个数据库移到另一个数据库,数据库不执行字符集转换,存储实际列值所需要的字节数大小随每行大小而异。这样的数据类型在数据库效率上会提高,而且对数据由于字符集的不同而导致的不一致的可能性也进行了排除。
3. KingbaseES中RAW数据类型使用
使用RAW数据类型,需提前加载扩展kdb_raw插件,插件加载方式如下:
create extension kdb_raw;
RAW数据类型语法为: RAW[(N)],其中参数n表示长度,以字节为单位,长度取值范围为1-32767。RAW类型支持不指定长度,即RAW直接使用,不限长度,但有存储 1G-4 字节数据的限制。LONG RAW类型只能整体“LONG RAW”使用,不能设置长度。
在KES中RAW类型支持方式如下:
- 带\x前缀的十六进制字符串的常量值输入,等价oracle的HEXTORAW;
- 十六进制字符串的常理值;
- 通过HEXTORAW、UTL_RAW.CAST_TO_RAW把字符串转换成RAW类型值;
- 通过::和cast()类型转换的raw类型
示例:
create table raw_table(id int,c1 raw,c2 raw(2),c3 long raw);
insert into raw_table values(1,'\x4B4B','\x4B4B','\x4B4B');
insert into raw_table values(2,'abc','abc','abc');
insert into raw_table values(3,HEXTORAW('12FF'),HEXTORAW('12FF'),HEXTORAW('12FF'));
insert into raw_table values(4,UTL_RAW.CAST_TO_RAW('abc'),UTL_RAW.CAST_TO_RAW('ab'),UTL_RAW.CAST_TO_RAW('abc'));
insert into raw_table values(5,'123'::raw,'123'::raw,'123'::raw);
insert into raw_table values(6,cast('def' as raw),cast('def' as raw),cast('def' as raw));
select * from raw_table;
kdb_raw插件除了提供RAW和LONG RAW数据类型的支持,还提供了SQL函数 RAWTOHEX和HEXTORAW等价的转换功能,同时支持 utl_raw、utl_enode 和 utl_i18n 内置包,并提供内置包中的函数支持。具体支持的函数如下:
函数 | 说明 |
HEXTORAW | 把十六进制字符串转换为相应的字节串。 |
RAWTOHEX | 参数支持varchar和raw,参数为varchar是把参数字符串转换成16进制表示输出;参数为raw则返回参数本身的16进制输出。 如果输入其他类型参数,则隐式转换为varchar类型处理。和Oracle处理不同,不论输入何种类型,Oracle返回的都是该类型内部的二进制表示。 |
RAWTONHEX | 参数支持varchar和raw,同RAWTOHEX |
TO_BLOB | 把 raw 类型值转换为 blob 类型值。 |
UTL_RAW包中函数 | |
BIT_AND(r1 in raw, r2 in raw) return raw | 按位做与运算。 1)输入参数有一个为NULL则返回NULL; 2)长度较长的参数,和长度较短参数等长部分按位做与运算,超出部分直接附加前述计算结果后。 |
BIT_COMPLEMENT(r in raw) return raw | 按位做补运算。 1)输入参数有为NULL则返回NULL。 |
BIT_OR(r1 in raw, r2 in raw) return raw | 按位做或运算。 1)输入参数有一个为NULL则返回NULL; 2)长度较长的参数,和长度较短参数等长部分按位做或运算,超出部分直接附加前述计算结果后。 |
BIT_XOR(r1 in raw, r2 in raw) return raw | 按位做异或运算。 1)输入参数有一个为NULL则返回NULL; 2)长度较长的参数,和长度较短参数等长部分按位做异或运算,超出部分直接附加前述计算结果后。 |
CAST_FROM_BINARY_DOUBLE(n in double, endianess in integer default 1) return raw | Double类型数字转换成raw类型。 输入参数有NULL返回NULL; 2)endianess取值:1代表big_endian,2代表little_endian,3代表当前机器的endian。非此3种值报错。 |
CAST_FROM_BINARY_FLOAT(n in float, endianess in integer default 1) return raw | float类型数字转换成raw类型。raw表示 IEEE 754 single-precision编码格式。 1)输入参数有NULL返回NULL; 2)endianess取值:1代表big_endian,2代表little_endian,3代表当前机器的endian。非此3种值报错。 |
CAST_FROM_BINARY_INTEGER(n in integer, endianess in integer default 1) return raw | integer类型数字转换成raw类型。 1)输入是NULL返回NULL; 2)最小值-2147483648,小于最小值报错; 3)最大值2147483647,大于最大值报错; 4)endianess取值:1代表big_endian,2代表little_endian,3代表当前机器的endian。非此3种值报错。 |
CAST_FROM_NUMBER | number类型数字转换成raw类型。 1)输入是NULL返回NULL; 2)正数范围1e-130到1e126,不包括1e126,大于等于1e126报错ORA-01426: numeric overflow,小于1e-130都返回80; 3)负数范围-1e126到-1e-130,不包括-1e126,小于等于-1e126报错ORA-01426: numeric overflow,大于-1e-130都返回80; 4)其他情况按表后编码规则转换。 |
CAST_TO_BINARY_DOUBLE(r in raw, endianess in integer default 1) return double | 转换raw字节串为对应表示的double值。r表示IEEE 754 double-precision编码格式。 如果raw超过8字节,只取前8个字节; 2)如果raw不足8个字节,报错; 3)endianess取值:1代表big_endian,2代表little_endian,3代表当前机器的endian。非此3种值报错。 4)如果结果是-0,则返回+0;如果结果是NaN,返回NAN。 |
CAST_TO_BINARY_FLOAT(r in raw, endianess in integer default 1) return float | 转换raw字节串为对应表示的float值。r表示IEEE 754 single-precision编码格式。 如果raw超过4字节,只取前4个字节; 如果raw不足4个字节,报错; 3)endianess取值:1代表big_endian,2代表little_endian,3代表当前机器的endian;非此3种值报错。 4)如果结果是-0,则返回+0;如果结果是NaN,返回NAN。 |
CAST_TO_BINARY_INTEGER(r in raw, endianess in integer default 1) return integer | 转换raw值对应的整数。 输入raw超过8位16进制字符,endian=3时返回0,endian=1,2,如果raw输出字符串长度是奇数,保留最高1位字符计算值,如果raw输出字符串长度是偶数,保留最高2位字符计算值. 2)endianess取值:1代表big_endian,2代表little_endian,3代表当前机器的endian;非此3种值报错。 |
CAST_TO_NUMBER | raw类型数字转换成number类型。 1)输入是NULL返回NULL; 2)按表后转换规则的逆向操作进行转换; 3)如果输入字节串不符合编码规则或不是数字,则报错 |
CAST_TO_RAW(c in varchar2) return raw | 返回c字符串内部的字节表示。实际值不变,用来解释值的类型变为raw。 1)c为NULL在返回NULL。 |
CAST_TO_VARCHAR2(r in raw) return varchar2 | 以当前服务器的字符集解释r代表的字节串对应的字符串。 1)r为NULL在返回NULL。 |
COMPARE(r1 in raw, r2 in raw, pad in raw default NULL) return integer | 比较两个raw值,返回第一个不相等的字节位置。 1)两个输入raw可以为NULL或长度0; 2)长度较短的输入raw值拼接pad指定的第一个字节,默认为\x00; 3)如果两个输入raw都为NULL或完全一样返回0;否则返回第一个不相等字节的位置,从1开始计数。 |
CONCAT(r1 in raw, r2 in raw, r3 in raw, r4 in raw, r5 in raw, r6 in raw, r7 in raw, r8 in raw, r9 in raw, r10 in raw, r11 in raw, r12 in raw,) return raw | 连接最多12个输入raw,返回拼接后的结果。 1)结果长度超过raw允许最大长度报错。 |
CONVERT(r in raw, to_charset in varchar2, from_charset in varchar2) return raw | 把from_set字符集下r代表的字符串转换成to_set字符集下的字节串。如果转换后的字节串超出raw允许的最大长度,则自动截取到最大长度。 1)字符集名称以kes支持的为主,可使用oracle和kes支持的字符集名称,不区分大小写,不支持的字符集报错 2)结果超过raw允许的最大长度自动截断到最大长度 |
COPIES(r in raw, n in number) return raw | 返回r拼接n次形成的raw值。 1)n取四舍五入后的整数值; 2)输入r为NULL或长度0报错; 3)次数n小于1报错; 4)结果长度超出raw允许的最大长度报错。 |
LENGTH(r in raw) return number | 返回输入r值的字节长度。 |
OVERLAY(overlay_str in raw, target in raw, pos in number default 1, len in number default NULL, pad in raw default NULL) return raw | 把输入target值从pos位置起始长度为 len的部分替换为overlay_str指定的raw。 1)如果overlay_str长度小于len则增补pad指定的第一个字节; 2)如果overlay_str长度大于len超出部分忽略; 3)如果起始位置超出指定target长度,则先用pad第一个字节补充,再拼接overlay_str 4)overlay_str为NULL或长度为0报错; 5)target未指定报错; 6)结果超出raw允许的最大长度报错; 7)起始位置小于1报错;为null则等价于1 8)指定长度小于0报错;长度为null则为overlay_str长度; 9)pos和len截取整数计算。 |
REVERSE(r in raw) return raw | 把r的字节颠倒顺序后返回。 1)输入r为NULL或长度为0报错。 |
SUBSTR(r in raw, pos in number, len in number default NULL)return raw | 截取输入参数r中从pos开始长度为len的子串。 1)起始位置pos = 0 或NULL等价于1,大于raw长度报错; 2)指定长度len小于1或大于起始位置后剩余长度报错; 3)起始位置pos大于0,从r开头计数;小于0,从r结尾计数; 4)不指定长度len,则截取剩余所有长度; 5)输入r为NULL则返回NULL; 6)pos和len截取整数计算。 |
TRANSLATE(r in raw, from_set in raw, to_set in raw)return raw | 把输入参数r中字节按from_set中出现的位置替换为to_set对应位置的字节。 1)如果字节在from_set出现,并且在to_set中有对应位置的字节,则替换为to_set中该对应字节;如果同一字节在from_set中出现多次,以第一次出现位置的对应关系进行替换; 2)如果字节在from_set出现,并且在to_set中没有对应位置的字节,则从结果中删除; 3)如果字节没有在from_set出现,则保留在结果中; 4)如果to_set比from_set长,则多出部分忽略 5)任何输入参数为NULL或长度为0报错。 |
TRANSLITERATE(r in raw, to_set in raw default NULL, from_set in raw default NULL, pad in raw default NULL)return raw | 把输入参数r中字节按from_set中出现的位置替换为to_set对应位置的字节。 1)如果字节在from_set出现,并且在to_set中有对应位置的字节,则替换为to_set中该对应字节;如果同一字节在from_set中出现多次,以第一次出现位置的对应关系进行替换; 2)如果字节在from_set出现,并且在to_set中没有对应位置的字节,则替换为pad参数的第一个字节; 3)如果字节没有在from_set出现,则保留在结果中; 4)如果from_set为NULL,默认为00…ff; 5)输入参数r为NULL或长度为0报错。 |
XRANGE(start_byte in raw default NULL, end_byte in raw default NULL) return raw | 返回起始字节为strat_byte,结束字节为end_byte,中间包含连续递增字节组成的raw。 1)start_byte默认00,end_byte默认ff,只取第一个字节。 |
UTL_I18N包函数 | |
RAW_TO_CHAR(data in raw, src_charset in varchar2 default NULL) return varchar2 | 把data按src_charset指定的字符集转换成当前数据库编码的对应字符串。 1)如果src_dataset为NULL则按当前数据库编码处理。 |
STRING_TO_RAW(data in varchar2, dst_charset in varchar2 default NULL) return raw | 把data转换成dst_charset指定的字符集编码。 1)如果dst_dataset为NULL则按当前数据库编码处理。 |
UTL_ENCODE包函数 | |
BASE64_ENCODE(r in raw) return raw | 使用base64算法对r字节串进行编码。 1)输入为空或NULL报错 |
BASE64_DECODE(r in raw) return raw | 使用base64算法对r字节串进行解码。 1)输入为空或NULL报错 2)输入无法解码的值返回NULL |
QUOTED_PRINTABLE_ENCODE | 把输入的 RAW 字节串编码成 QUOTED PRINTABLE 格式字节串。 |
QUOTED_PRINTABLE_DECODE | 把输入的 QUOTED PRINTABLE 格式字节串解码为原始的 RAW 字节串。 |
4. KingbaseES中RAW类型相关函数示例
函数 | 示例 |
HEXTORAW | select hextoraw('abc') from dual; --0ABC select hextoraw('1234') from dual; --1234 |
RAWTOHEX | select RAWTOHEX('abc'::raw) from dual; --0ABC select RAWTOHEX('1'::raw) from dual; --01 |
RAWTONHEX | select rawtonhex('abc'::raw) from dual; --0ABC select rawtonhex('1'::raw) from dual; --01 |
TO_BLOB | select to_blob('123'::raw) from dual; --\001# select to_blob('abc'::raw) from dual; --\012\274 |
UTL_RAW包中函数 | |
BIT_AND(r1 in raw, r2 in raw) return raw | select UTL_RAW.BIT_AND(hextoraw('01'),hextoraw('111')) from dual; --0111 select UTL_RAW.BIT_AND(hextoraw('abc'),hextoraw('def')) from dual; --08AC |
BIT_COMPLEMENT(r in raw) return raw | select UTL_RAW.bit_complement(hextoraw('01')) from dual; --FE select UTL_RAW.bit_complement('01'::raw) from dual; --FE |
BIT_OR(r1 in raw, r2 in raw) return raw | select UTL_RAW.BIT_OR(hextoraw('01'),hextoraw('111')) from dual; --0111 select UTL_RAW.BIT_OR(hextoraw('abc'),hextoraw('def')) from dual; --0FFF |
BIT_XOR(r1 in raw, r2 in raw) return raw | select UTL_RAW.BIT_XOR(hextoraw('abc'),hextoraw('def')) from dual; --0753 select UTL_RAW.BIT_XOR('111'::raw,'101'::raw) from dual; --0010 |
CAST_FROM_BINARY_DOUBLE(n in double, endianess in integer default 1) return raw | select UTL_RAW.CAST_FROM_BINARY_DOUBLE(2.33) from dual; --4002A3D70A3D70A4 select UTL_RAW.CAST_FROM_BINARY_DOUBLE(2.33,1) from dual; --4002A3D70A3D70A4 select UTL_RAW.CAST_FROM_BINARY_DOUBLE(2.33,2) from dual; --A4703D0AD7A30240 select UTL_RAW.CAST_FROM_BINARY_DOUBLE(2.33,3) from dual; --A4703D0AD7A30240 |
CAST_FROM_BINARY_FLOAT(n in float, endianess in integer default 1) return raw | select UTL_RAW.CAST_FROM_BINARY_FLOAT(2.33) from dual; --40151EB8 select UTL_RAW.CAST_FROM_BINARY_FLOAT(2.33,1) from dual; --40151EB8 select UTL_RAW.CAST_FROM_BINARY_FLOAT(2.33,2) from dual; --B81E1540 select UTL_RAW.CAST_FROM_BINARY_FLOAT(2.33,3) from dual; --B81E1540 |
CAST_FROM_BINARY_INTEGER(n in integer, endianess in integer default 1) return raw | select UTL_RAW.CAST_FROM_BINARY_INTEGER(2.33) from dual; --00000002 select UTL_RAW.CAST_FROM_BINARY_INTEGER(2.33,1) from dual; --00000002 select UTL_RAW.CAST_FROM_BINARY_INTEGER(2.33,2) from dual; --02000000 select UTL_RAW.CAST_FROM_BINARY_INTEGER(2.33,3) from dual; --02000000 |
CAST_FROM_NUMBER | select UTL_RAW.CAST_FROM_NUMBER(2.33) from dual; --C10322 select UTL_RAW.CAST_FROM_NUMBER(1000) from dual; --C20B |
CAST_TO_BINARY_DOUBLE(r in raw, endianess in integer default 1) return double | select UTL_RAW.CAST_TO_BINARY_DOUBLE(hextoraw('4002A3D70A3D70A4')) from dual; --2.33 select UTL_RAW.CAST_TO_BINARY_DOUBLE(hextoraw('4002A3D70A3D70A4'),2) from dual; -- -3.57E-133 |
CAST_TO_BINARY_FLOAT(r in raw, endianess in integer default 1) return float | select UTL_RAW.CAST_TO_BINARY_FLOAT(hextoraw('40151EB8')) from dual; --2.33 select UTL_RAW.CAST_TO_BINARY_FLOAT(hextoraw('40151EB8'),2) from dual; -- -3.77E-005 |
CAST_TO_BINARY_INTEGER(r in raw, endianess in integer default 1) return integer | select UTL_RAW.CAST_TO_BINARY_INTEGER(hextoraw('30000')) from dual; --196608 select UTL_RAW.CAST_TO_BINARY_INTEGER(hextoraw('00000003'),1) from dual; --3 |
CAST_TO_NUMBER | select UTL_RAW.CAST_TO_NUMBER(hextoraw('C10322')) from dual; --2.33 |
CAST_TO_RAW(c in varchar2) return raw | select UTL_RAW.CAST_TO_RAW('abc') from dual; -- 616263 select UTL_RAW.CAST_TO_RAW('123') from dual; -- 313233 select UTL_RAW.CAST_TO_RAW('中文') from dual; --E4B8ADE69687 |
CAST_TO_VARCHAR2(r in raw) return varchar2 | select UTL_RAW.CAST_TO_VARCHAR2('616263') from dual; --abc |
COMPARE(r1 in raw, r2 in raw, pad in raw default NULL) return integer | select UTL_RAW.COMPARE(hextoraw('01'),hextoraw('02')) from dual; --1 select UTL_RAW.COMPARE(hextoraw('01'),hextoraw('01')) from dual; --0 |
CONCAT(r1 in raw, r2 in raw, r3 in raw, r4 in raw, r5 in raw, r6 in raw, r7 in raw, r8 in raw, r9 in raw, r10 in raw, r11 in raw, r12 in raw,) return raw | select UTL_RAW.CONCAT(hextoraw('01'),hextoraw('02')) from dual; --0102 |
CONVERT(r in raw, to_charset in varchar2, from_charset in varchar2) return raw | SELECT UTL_RAW.CONVERT(hextoraw('02'),'UTF8','sqlascii') from dual; --02 |
COPIES(r in raw, n in number) return raw | select UTL_RAW.COPIES(HEXTORAW('01'),2) from dual; --0101 |
LENGTH(r in raw) return number | select UTL_RAW.LENGTH(UTL_RAW.cast_to_raw('abc')) from dual; --3 select UTL_RAW.LENGTH(UTL_RAW.cast_to_raw('01')) from dual; --2 |
OVERLAY(overlay_str in raw, target in raw, pos in number default 1, len in number default NULL, pad in raw default NULL) return raw | select UTL_RAW.OVERLAY(hextoraw('aaaaaaaa'),hextoraw('bbbbbbbb'),2,2) from dual; --BBAAAABB select UTL_RAW.OVERLAY(hextoraw('aaaaaaaa'),hextoraw('bbbbbbbb'),2) from dual; --BBAAAAAAAA select UTL_RAW.OVERLAY(hextoraw('aaaaaaaa'),hextoraw('bbbbbbbb')) from dual; --AAAAAAAA |
REVERSE(r in raw) return raw | select UTL_RAW.REVERSE(hextoraw('F30201')) from dual; --0102F3 select UTL_RAW.REVERSE(hextoraw('010203')) from dual; --030201 |
SUBSTR(r in raw, pos in number, len in number default NULL)return raw | select UTL_RAW.SUBSTR(hextoraw('01020304'),2,2) from dual; --0203 select UTL_RAW.SUBSTR(hextoraw('01020304'),2) from dual; --020304 |
TRANSLATE(r in raw, from_set in raw, to_set in raw)return raw | select UTL_RAW.TRANSLATE(hextoraw('01020304'),hextoraw('01020304'),hextoraw('0a0b0c0d')) from dual; --0A0B0C0D select UTL_RAW.TRANSLATE(hextoraw('01020304'),hextoraw('01020303'),hextoraw('0a0b0c0d')) from dual; --0A0B0C04 |
TRANSLITERATE(r in raw, to_set in raw default NULL, from_set in raw default NULL, pad in raw default NULL)return raw | select UTL_RAW.TRANSLITERATE(hextoraw('01020304'),hextoraw('0a0b0c0d'),hextoraw('FA01020304EF'),hextoraw('5555')) from dual; --0B0C0D55 select UTL_RAW.TRANSLITERATE(hextoraw('01020304'),hextoraw('0a0b0c0d'),hextoraw('01020304EF'),hextoraw('5555')) from dual; --0A0B0C0D |
XRANGE(start_byte in raw default NULL, end_byte in raw default NULL) return raw | select UTL_RAW.XRANGE(hextoraw('01'),hextoraw('04')) from dual; --01020304 select UTL_RAW.XRANGE(hextoraw('05'),hextoraw('0f')) from dual; --05060708090A0B0C0D0E0F |
UTL_I18N包函数 | |
RAW_TO_CHAR(data in raw, src_charset in varchar2 default NULL) return varchar2 | select UTL_I18N.RAW_TO_CHAR(hextoraw('616263')) from dual; --abc select UTL_I18N.RAW_TO_CHAR(hextoraw('616263'),'utf8') from dual; --abc |
STRING_TO_RAW(data in varchar2, dst_charset in varchar2 default NULL) return raw | select UTL_I18N.STRING_TO_RAW('abc','utf8') from dual; --616263 select UTL_I18N.STRING_TO_RAW('123','utf8') from dual; --313233 |
UTL_ENCODE包函数 | |
BASE64_ENCODE(r in raw) return raw | select UTL_ENCODE.BASE64_ENCODE(utl_raw.cast_to_raw('gh')) from dual; --5A32673D select UTL_ENCODE.BASE64_ENCODE(hextoraw('0102')) from dual; --4151493D |
BASE64_DECODE(r in raw) return raw | select utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_DECODE ('5A32673D')) from dual; --gh select utl_raw.cast_to_varchar2(UTL_ENCODE.BASE64_DECODE('6447567A64413D3D')) from dual; --test |
QUOTED_PRINTABLE_ENCODE | select UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(utl_raw.cast_to_raw('gh')) from dual; --6768 select UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(hextoraw('0102')) from dual; --3D30313D3032 |
QUOTED_PRINTABLE_DECODE | select utl_raw.cast_to_varchar2(UTL_ENCODE.QUOTED_PRINTABLE_DECODE ('6768')) from dual; --gh select rawtohex(UTL_ENCODE.QUOTED_PRINTABLE_DECODE ('3D30313D3032')) from dual; --0102 |
更多信息,参见 https://help.kingbase.com.cn/v8/index.html