【GaussDB】在UTF8的库上按GBK的字节数截取字符串背景在国产数据库迁移过程中可能会有这样一个场景源库ORACLE用的是ZHS16GBK字符集目标库GaussDB用的是UTF8字符集如果源库是VARCHAR2(10),且字段内容里包含汉字那么目标库字段需要增长假设按照常规的一个汉字在GBK占2字节到UTF8占3个字节也就是按原长度的1.5倍即VARCHAR2(15)。然而如果迁移后国产库里产生的新数据需要回流到ORACLE可能会存在ORACLE中长度不足的问题比如新数据为 “咕咕嘎嘎123” 在UTF8是15个字节在ZHS16GBK是11个字节。所以为了确保数据能回去只能进行截断别说为什么前端不控制总会有各种各样的原因然而如果用在UTF8库上按字节数进行截断可能又会截多了比如源库VARCHAR2(10) “咕咕嘎嘎啊” 在UTF8上substrb截10个字节会变成咕咕嘎一个字节截断尾部乱码后就只剩三个汉字了少了两个字。由此产生了一个需求在GaussDB的UTF8库里按照GBK编码的字节数实现一个类似substrb的自定义函数输入字符串、开始位置、保留字符数返回截取后的字符串以确保尽可能保留ORACLE中的数据。我的方案createorreplacefunctionuf_gbk_substrb(input_strvarchar,pos number,len number)returnsvarcharRETURNSNULLONNULLinput IMMUTABLEas$$declarel_tmp bytea;beginiflength(input_str)0thenreturnnull;endif;l_tmp:convert_to(input_str::text,GBK);foriin1..3loopbeginreturnconvert_from(substr(l_tmp,greatest(pos-1,0),least(len-i1,length(l_tmp))),GBK);exceptionwhenothersthennull;end;endloop;RAISE EXCEPTIONinvalid byte sequence for encoding GBK: %,l_tmp::text;end;$$languageplpgsql;基本用法和普通的subtrb一样不过我没去写反向截取即负数入参的逻辑。下面就是按GBK10个字节去截的效果。gaussdbselectuf_gbk_substrb(咕咕嘎嘎123,1,10);uf_gbk_substrb----------------咕咕嘎嘎12(1row)gaussdbselectuf_gbk_substrb(123咕咕嘎嘎,1,10);uf_gbk_substrb----------------123咕咕嘎(1row)gaussdbselectuf_gbk_substrb(咕咕嘎嘎啊,1,10);uf_gbk_substrb----------------咕咕嘎嘎啊(1row)注意到第2个测试用例截完后GBK占9个字节并没有填满10个字节是因为PG系对不完整字符的支持都不好所以我只能尽可能保留UTF8能识别的字符尾部的半个汉字需要截掉。接下来逐行解释一下这个函数的设计思路NULLONNULLinput如果输入参数为空则直接返回空iflength(input_str)0thenreturnnull;endif;如果输入字符串长度为0则直接返回空有人可能认为这个已经有了null on null input就够了但其实null和0长度字符串是不一样的哪怕是在A兼容模式下l_tmp:convert_to(input_str::text,GBK);使用临时变量暂存输入字符串的GBK二进制数据foriin1..3loopbeginreturnconvert_from(substr(l_tmp,greatest(pos-1,0),least(len-i1,length(l_tmp))),GBK);exceptionwhenothersthennull;end;endloop;对这个临时二进制变量按字节截取注意当入参是bytea类型时substr是按字节截取且最小开始位置是0而不是1。substr截完后还是二进制再转回字符串。此时如果尾部存在不完整字符则可能报错因此尝试再少截一个字符进行重试最多3次。RAISE EXCEPTIONinvalid byte sequence for encoding GBK: %,l_tmp::text;三次都转换报错则函数报错无效的字节序并打印对应的GBK二进制数据。这函数我纯手搓的因为我经常与字符集打交道这个逻辑对我来说算是挺简单的了。AI会怎么做然后我突发奇想如果把这个需求丢给AI AI会怎么做于是我把本文开头的背景说明直接给到了mimo-v2.5-prota生成了一个逻辑比我的方案复杂得多的函数AI生成的代码里带了parallelGaussDB不支持我手动删掉了-- -- gbk_substrb: 在 UTF8 数据库中按 GBK 编码字节数实现 substrb-- 用途GaussDB(UTF8) → Oracle(ZHS16GBK) 数据回流时的字段截断---- 与 Oracle SUBSTRB 行为对齐-- start_pos0 → 等效于 1-- start_pos0 → 从字符串末尾倒数-- 截取跨越某字符的 GBK 字节边界时该字符被跳过不截断半个字符---- 参数-- src_str - 输入字符串UTF8-- start_pos - 起始位置GBK 字节数从 1 开始-- sub_length - 截取长度GBK 字节数可选默认取到末尾-- CREATEORREPLACEFUNCTIONgbk_substrb(src_strTEXT,start_posINTEGER,sub_lengthINTEGERDEFAULTNULL)RETURNSTEXTLANGUAGEplpgsql IMMUTABLEAS$$DECLAREv_lenINTEGER;v_charTEXT;v_cpINTEGER;v_blenINTEGER;v_eff_startINTEGER;v_real_posINTEGER:0;-- 当前 GBK 字节位置从 1 开始v_resultTEXT:;v_beginINTEGER:0;-- 截取开始的 GBK 字节位置v_endINTEGER;-- 截取结束的 GBK 字节位置v_startedBOOLEAN:FALSE;BEGINIFsrc_strISNULLORstart_posISNULLTHENRETURNNULL;ENDIF;v_len :char_length(src_str);IFv_len0THENRETURN;ENDIF;-- Oracle 行为start_pos0 等效于 1IFstart_pos0THENv_eff_start :1;ELSEv_eff_start :start_pos;ENDIF;-- 计算截取结束位置GBK 字节数IFsub_lengthISNULLTHENv_end :2147483647;-- 取到末尾ELSEv_end :v_eff_startsub_length-1;ENDIF;-- 负数 start_pos从末尾倒数IFv_eff_start0THEN-- 先算出整个字符串的 GBK 字节总长DECLAREv_totalINTEGER:0;v_chTEXT;v_cINTEGER;BEGINFORiIN1..v_lenLOOPv_ch :substr(src_str,i,1);v_c :ascii(v_ch);IFv_c127THENv_total :v_total1;ELSIF v_cBETWEEN192AND223THENv_total :v_total1;ELSEv_total :v_total2;ENDIF;ENDLOOP;v_eff_start :v_totalv_eff_start1;v_end :v_eff_startCOALESCE(sub_length,1)-1;END;ENDIF;IFv_eff_start0THENRETURNNULL;ENDIF;-- 遍历每个字符按 GBK 字节数做截取FORiIN1..v_lenLOOPv_char :substr(src_str,i,1);v_cp :ascii(v_char);-- 计算该字符在 GBK 编码中的字节数IFv_cp127THEN-- ASCII含英文、数字、符号v_blen :1;ELSIF v_cpBETWEEN192AND223THEN-- U0080 ~ U07FFLatin 扩展、希腊语、阿拉伯语等-- GBK 通常 1 字节直通映射或 ?v_blen :1;ELSE-- U0800CJK 等在 GBK 中占 2 字节v_blen :2;ENDIF;v_real_pos :v_real_posv_blen;-- 定位截取起始点IFNOTv_startedANDv_real_posv_eff_startTHENv_started :TRUE;v_begin :v_real_pos-v_blen1;ENDIF;-- 截取结束字符跨越 end 边界则跳过IFv_startedANDv_real_posv_endTHENEXIT;ENDIF;IFv_startedTHENv_result :v_result||v_char;ENDIF;ENDLOOP;RETURNv_result;END;$$;COMMENTONFUNCTIONgbk_substrb(TEXT,INTEGER,INTEGER)IS在 UTF8 库中按 GBK 字节数截取字符串用于 GaussDB→Oracle(ZHS16GBK) 数据回流截断;大概的逻辑就是从前到后逐字符遍历一个一个字符截取根据每一个字符的ascii码大小范围判断它是1个字节还是多个字节的多字节都按2字节算视为GBK字节长度然后把一个一个字符拼起来拼的同时GBK字节数也累加直到计数器达到目标长度就跳出循环返回拼接的字符串。方案对比AI写的更像一个真正的程序虽然代码长逻辑复杂但这个处理逻辑拿到任何开发语言里都是可以用的不像我写的那个依赖数据库自己的转码函数去判断能不能转码成功。先造一些用例对比下两个方案返回结果是否一致selectgbk_substrb(咕咕嘎嘎123,1,10)uf_gbk_substrb(咕咕嘎嘎123,1,10)b;selectgbk_substrb(123咕咕嘎嘎,1,10)uf_gbk_substrb(123咕咕嘎嘎,1,10)b;selectgbk_substrb(咕咕嘎嘎啊,1,10)uf_gbk_substrb(咕咕嘎嘎啊,1,10)b;这三个例子验证都为true然后对比一下性能createtablet_text(idint,atext);insertintot_textselectid,casemod(id,3)when0then咕咕嘎嘎123when1then123咕咕嘎嘎when2then咕咕嘎嘎啊endfrompg_catalog.generate_series(1,10000)id;insertintot_textselectid10000,casemod(id,2)when0thennullelseencode(\x,hex)endfrompg_catalog.generate_series(1,10000)id;explainanalyzeselectid,gbk_substrb(a,1,10)fromt_text;--Total runtime: 611.122 msexplainanalyzeselectid,uf_gbk_substrb(a,1,10)fromt_text;--Total runtime: 1918.647 ms我的方案耗时是AI的三倍还多AI的字符遍历可能对于数据越长性能就越差于是我又弄了个用例createtablet_text2(idint,atext);insertintot_text2selectid,casemod(id,3)when0thenrepeat(咕咕嘎嘎123,200)when1thenrepeat(123咕咕嘎嘎,200)when2thenrepeat(咕咕嘎嘎啊,200)endfrompg_catalog.generate_series(1,10000)id;explainanalyzeselectid,gbk_substrb(a,1,10)fromt_text2;--Total runtime: 1014.254 msexplainanalyzeselectid,uf_gbk_substrb(a,1,10)fromt_text2;--Total runtime: 1849.356 ms果然输入字符串的长度对性能有影响但是尽管加长到3K字节AI的方案性能依旧更好。现在也就一万行如果真实数据上千万行甚至有多个字段都需要截取中间的差异时间就会更大了。AI的额外启发AI其实还同时生成了另一个计算gbk字节数的函数-- -- gbk_bytelen: 计算字符串在 GBK 编码下的字节长度-- 用途截断前预判是否需要截断-- CREATEORREPLACEFUNCTIONgbk_bytelen(src_strTEXT)RETURNSINTEGERLANGUAGEplpgsql IMMUTABLE PARALLEL SAFEAS$$DECLAREv_lenINTEGER;v_totalINTEGER:0;v_charTEXT;v_cpINTEGER;BEGINIFsrc_strISNULLTHENRETURNNULL;ENDIF;v_len :char_length(src_str);FORiIN1..v_lenLOOPv_char :substr(src_str,i,1);v_cp :ascii(v_char);IFv_cp127THENv_total :v_total1;ELSIF v_cpBETWEEN192AND223THENv_total :v_total1;ELSEv_total :v_total2;ENDIF;ENDLOOP;RETURNv_total;END;$$;COMMENTONFUNCTIONgbk_bytelen(TEXT)IS计算字符串在 GBK 编码下的字节数ASCII1, CJK2;这个可以放到数据入库的前置校验逻辑中去这样库里的数据就不需要再截断了。另外我也尝试了清空所有上下文只给AI一句最简单的指令看看ta能生成什么“写一个GaussDB的自定义函数在UTF8的库上按GBK的字节数截取字符串”然后AI给了我3个方案-- 方法1PL/pgSQL 函数推荐兼容性好CREATEORREPLACEFUNCTIONgbk_substr(p_strTEXT,p_max_bytesINTEGER)RETURNSTEXTLANGUAGEplpgsql IMMUTABLEAS$$DECLAREv_resultTEXT:;v_current_bytesINTEGER:0;v_charTEXT;v_char_bytesINTEGER;v_lenINTEGER;v_iINTEGER;BEGIN-- 参数校验IFp_strISNULLORp_max_bytes0THENRETURN;ENDIF;v_len :length(p_str);FORv_iIN1..v_lenLOOPv_char :substr(p_str,v_i,1);-- 计算该字符在 GBK 编码下的字节数-- ASCII 字符 (0-127) 在 GBK 中占 1 字节-- 中文字符在 GBK 中占 2 字节IFascii(v_char)127THENv_char_bytes :1;ELSE-- 非 ASCII 字符中文等在 GBK 中占 2 字节v_char_bytes :2;ENDIF;-- 检查是否超出限制IFv_current_bytesv_char_bytesp_max_bytesTHENEXIT;ENDIF;v_current_bytes :v_current_bytesv_char_bytes;v_result :v_result||v_char;ENDLOOP;RETURNv_result;END;$$;COMMENTONFUNCTIONgbk_substr(TEXT,INTEGER)IS在 UTF8 数据库上按 GBK 字节数截取字符串。 参数 p_str - 输入字符串UTF8 编码 p_max_bytes - 最大 GBK 字节数 返回截取后的字符串确保转为 GBK 后字节数不超过 p_max_bytes 示例SELECT gbk_substr(你好世界Hello, 8); -- 返回 你好 (2*24字节) 或 你好H (415字节);-- 测试用例-- SELECT gbk_substr(你好世界Hello, 8); -- 预期你好He (222118字节)-- SELECT gbk_substr(你好世界, 5); -- 预期你好 (4字节再加世会超)-- SELECT gbk_substr(Hello, 3); -- 预期Hel-- SELECT gbk_substr(NULL, 10); -- 预期空字符串-- SELECT gbk_substr(abc, 0); -- 预期空字符串-- 方法2SQL 函数更简洁但依赖 ascii() 函数可用性-- 如果 GaussDB 支持 ascii() 函数可以用这个更简洁的版本CREATEORREPLACEFUNCTIONgbk_substr_sql(p_strTEXT,p_max_bytesINTEGER)RETURNSTEXTLANGUAGEsqlIMMUTABLEAS$$WITHcharsAS(SELECTc,CASEWHENascii(c)127THEN1ELSE2ENDASbyte_len,ROW_NUMBER()OVER()ASrnFROMunnest(string_to_array(p_str,NULL))ASc),cumulativeAS(SELECTc,byte_len,SUM(byte_len)OVER(ORDERBYrn)AScumulative_bytesFROMchars)SELECTCOALESCE(string_agg(c,ORDERBYcumulative_bytes),)FROMcumulativeWHEREcumulative_bytesp_max_bytes;$$;COMMENTONFUNCTIONgbk_substr_sql(TEXT,INTEGER)ISSQL 版本的 GBK 字节截取函数更简洁;-- 方法3如果需要处理 GBK 编码中的特殊字符如一些罕用字可能是 4 字节-- 使用 convert_to 函数进行精确计算需要 PostgreSQL 9.x 或 GaussDB 支持CREATEORREPLACEFUNCTIONgbk_substr_exact(p_strTEXT,p_max_bytesINTEGER)RETURNSTEXTLANGUAGEplpgsql IMMUTABLEAS$$DECLAREv_resultTEXT:;v_current_bytesINTEGER:0;v_charTEXT;v_char_bytesINTEGER;v_lenINTEGER;v_iINTEGER;v_gbk_bytes BYTEA;BEGINIFp_strISNULLORp_max_bytes0THENRETURN;ENDIF;v_len :length(p_str);FORv_iIN1..v_lenLOOPv_char :substr(p_str,v_i,1);-- 使用 convert_to 精确计算 GBK 字节数BEGINv_gbk_bytes :convert_to(v_char,GBK);v_char_bytes :length(v_gbk_bytes);EXCEPTIONWHENOTHERSTHEN-- 转换失败时按 2 字节估算v_char_bytes :2;END;IFv_current_bytesv_char_bytesp_max_bytesTHENEXIT;ENDIF;v_current_bytes :v_current_bytesv_char_bytes;v_result :v_result||v_char;ENDLOOP;RETURNv_result;END;$$;COMMENTONFUNCTIONgbk_substr_exact(TEXT,INTEGER)IS精确版本使用 convert_to 计算 GBK 字节数性能较低但最准确;三个方案都是遍历字符然后拼接但区别是第一个方案和AI前面的方案原理一样循环截取然后按ascii大小判断GBK长度第二个方案是SQL语言函数将字符串按每个字符切分转成一个数组然后再按ascii大小计算字节长度第三个方案考虑了生僻字使用了内置函数convert_to来精确识别字节数总结不是为自己找补我的方案和AI第一版的方案相比虽然AI的性能更好但是如果需要把方案给到一个对字符集不是很理解的开发人员考虑到函数可能还存在一些边界场景的BUG比如生僻字我可能仍然还会提供我自己的方案。因为AI的方案代码量稍大有些特殊场景也没有考虑我的方案里是有数据库自己的内置函数来进行未知情况的兜底。不过如果能有真实大量的数据测试一下AI这个函数识别到可能的边界场景让AI来完善这个函数的处理并且自动化进行性能测试分析和调整这可能是更符合AI-AGENT时代的做法了。另一方面不能忽视的是虽然AI这次写得挺好但前提是我能准确地描述这个问题即本文中的背景说明这一段并非我收到的原始描述而是我根据开发人员的无法准确描述的未知担忧逐步拆解分析出来的。AI的使用者依旧需要具有清晰的逻辑思考能力以及语言表达能力才能让AI完成想让ta完成的事。但是描述得越详细有时候也可能会扼杀AI的创造力比如我第二次给AI提的一句话需求的回答中其实AI反而考虑到了前面长提示词的回答没有考虑到的生僻字场景。最后我问了AI一句我你能搜一下网上有没有类似的文章或者案例么AI:搜了一圈没有找到直接对标的案例。搜索结果主要分两类同编码截取Oracle/GaussDB 的 SUBSTRB 按当前库编码的字节数截取比如 UTF8 库按 UTF8 字节数、GBK 库按 GBK 字节数乱码处理SUBSTRING 截取中文时因编码不一致导致的乱码问题你这个场景比较特殊UTF8 库存储但要按 GBK 字节数限制输出。典型场景是对接老系统接口字段长度按 GBK 算或者写文件时控制 GBK 编码的行宽。所以前面写的函数确实是个空白地带市面上没有现成方案。你这个需求挺实用的要是写成博客应该有人看 本文作者DarkAthena本文链接https://www.darkathena.top/archives/gaussdb-utf8-gbk-substrb版权声明本博客所有文章除特别声明外均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处