【TD迁移】【函数迁移之一】因字符集变化引起的函数适配

【前言】

      在国内环境通常使用中文的情况下，一般我们建议GaussDB(DWS)使用UTF8作为字符集。而TD（TERADATA）一般默认是使用latin1的字符集，存放中文的也是GBK字符集，与GaussDB(DWS)推荐使用的UTF8不一致。这样的不一致会导致一些字符串相关的函数，会因为字符集不一致导致结果不一致。例如substr，length这样的常用函数。length('TD迁移')在TD的latin1数据库下和GaussDB(DWS)的UTF8数据库下的结果分别是6和4

【转换相关的考虑】

1. 考虑重点是脚本迁移，调研存量的td脚本，若SQL中的字符函数较少且使用对象从业务角度无中文，则可以不考虑本文描述的情况；

2.使用新建的自定义函数来替代脚本中的字符函数，自定义函数使用统一命名规则，以便在脚本中替换。例如substr，新的自定义函数可以命名为td_substr；

3.目标是减少迁移的工作量，使用自定义函数替换系统自带函数，有一定的性能损失；目前项目经验看，性能损失在接受范围内（无测试结果，在大面积替换函数后，GaussDB(DWS)的SQL执行时间仍然在接受范围内）

4.适配的自定义函数应统一建在public下，用户使用时不需要在前面带上schema名称；

5.考虑会存在使用UTF8字符集结果需求，且以后迁移完成后能方便切换为UTF8，在自定义函数中增加可以指定字符集的参数，例如 td_length(column,'UTF8')实现的逻辑与utf8字符集的GaussDB(DWS)的length函数一致，而td_length(column,'GBK')则实现TD在latin1字符集下的length函数。

【函数实现】

在考虑好以后，可以参考以下函数的实现样例。td的字符相关函数有很多，可以大概分为以下几类：

• 获取字符串长度，如length
• 截取字符串，如substr
• 获取字符位置，如instr
  

【函数命名基本原则】

• GaussDB(DWS)有的函数名称或者关键字，需增加类似td_的前缀来区分已有的函数或关键字，例如char 是GaussDB(DWS)的关键字，但也是td的函数名称，因此该适配UDF名称需要增加前缀，td_char
• GaussDB(DWS)没有的函数，可以沿用td的函数名称，例如chars

下面为具体函数的实现

【获取字符串长度】

获取字符串长度的函数在td中有很多同义函数，我们以char()这个函数作为原始函数，其他同义函数则通过引用该函数实现。获取字符串长度的函数会作为基础函数，被其他类型的函数使用。

CREATE OR REPLACE FUNCTION public.td_char(text)
RETURNS int
LANGUAGE plpgsql
IMMUTABLE NOT FENCED
AS $$
DECLARE CNT INTEGER;

BEGIN

    CNT = 0;
    FOR I IN 1..length($1) LOOP
        IF(ASCII(SUBSTR($1,I,1)) <= 128) THEN
             CNT = CNT + 1;
        ELSE
             CNT = CNT + 2;
        END IF;
    END LOOP;
RETURN CNT;
END;
$$
;

--还可以通过转换字符集的方式实现，该方式开始迁移时没想到，还没经过大量脚本验证，但初步验证能处理中文且结果准确，速度比上面LOOP快
CREATE OR REPLACE FUNCTION public.td_char(text)
RETURNS int
LANGUAGE sql
IMMUTABLE NOT FENCED
AS $$
SELECT octet_length(convert(replace($1,'\','\\')::bytea,'UTF8','GBK')); --'\'无法转换为bytea
$$
;

同义的获取字符串长度的函数，下面的函数有一些在原来GaussDB(DWS)就存在的，比如length，需要建一个与length不一样的名字，td_length。同时GaussDB(DWS)不存在的，需要建一个与td一样的名字，如char_length。

• character
• char_length
• character_length
• length
• chars

下面以char_length函数举例，其他函数与例子一致。

CREATE OR REPLACE FUNCTION public.td_char_length(text,text)
RETURNS int
LANGUAGE plpgsql
IMMUTABLE NOT FENCED
AS $$
begin
   IF $2 ~* '^utf-?8\s*$' THEN return length($1);
ELSIF $2 ~* '^gbk\s*$' THEN
return public.td_char($1);
ELSE
RAISE EXCEPTION '% invalid,input GBK or UTF8',$2;
END IF;
END;
$$
;

/*区分现有char_length函数，增加td_前缀*/
CREATE OR REPLACE FUNCTION public.td_char_length(text)
RETURNS int
LANGUAGE sql
IMMUTABLE NOT FENCED
AS $$
select public.td_char($1);
$$
;

【截取字符串】

 TD常用的截取字符串函数有

• substr
• left
• right
• substring

对于substring(sn from 3 for 3) 这样的语法，我们无法新增含有关键字的函数，建议对substring函数统一都修改为substr

下面具体展示substr的SQL代码，具体其他的代码见附件。

CREATE OR REPLACE FUNCTION public.td_substr(text,int)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED SHIPPABLE
AS $$
DECLARE
    CNT INTEGER;
    RST VARCHAR;
    TMP VARCHAR;
    
    BEGIN
        IF($2 <= -1 ) THEN
            RST = $1;
        ELSE
            CNT = 0;
            RST = '';
            IF (LENGTH($1) = 0 ) THEN
                RETURN $1;
            END IF;
            
            FOR I IN 1..LENGTH($1) LOOP
                TMP = SUBSTR($1, I, 1);
                IF CNT < $2 THEN
                    /*判断是否中文*/
                    IF ASCII(TMP) <= 128 THEN
                        CNT = CNT + 1;
                        IF CNT = $2 THEN
                            RST = TMP;
                        END IF;
                        
                    ELSE
                    /*中文跳一位字节*/
                        CNT = CNT + 2;
                        IF CNT > $2 THEN 
                             RST = TMP;
                             /*如果刚好截取是中文的中间，则返回固定0F字节*/
                        ELSIF CNT = $2 THEN
                            RST = e'\x0f';
                        END IF;
                    END IF;
                     
                ELSE
                    RST = RST || TMP;
                END IF;
            END LOOP;
         END IF;
         
       RETURN RST;
    END;
$$
;

【获取字符位置】

TD常用的获取字符位置的函数有

• instr
• index
• position

其中position类似substring，函数中有IN语法，导致无法创建UDF。建议对含有position的语法都修改为instr。

下面简单举td_instr的实现代码。

CREATE OR REPLACE FUNCTION public.td_instr(text,text,int)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED SHIPPABLE
AS $$
DECLARE
    POINT INTEGER;
    FLEN INTEGER;
    CLEN INTEGER;
    TMP VARCHAR;
    SCAN VARCHAR;
    ST VARCHAR;
    
    BEGIN
        POINT = 0;
        CLEN = LENGTH($1);
        FLEN = LENGTH($2);
        /*输入为整数为大部分场景*/
        IF $3 >= 0 THEN
            FOR I IN 1..CLEN LOOP
                /*TMP为比较字符*/
                TMP = SUBSTR($1,I,FLEN);
                /*SCAN用来判断是否中文*/
                SCAN = SUBSTR($1,I,1);
                
                POINT = POINT + 1;
                IF POINT >= $3 THEN
                    IF TMP = $2 THEN
                        RETURN POINT;
                    END IF;
                END IF;
                
                /*中文则+1*/
                IF ASCII(SCAN) > 128 THEN
                    POINT = POINT + 1;
                END IF;
                
            END LOOP;
         
         /*小于0的情况，反向搜索*/
         ELSE
             FOR I IN 1..CLEN LOOP
                 ST = CLEN - I + 1;
                /*TMP为比较字符*/
                TMP = SUBSTR($1,ST,FLEN);
                /*SCAN用来判断是否中文*/
                SCAN = SUBSTR($1,ST,1);
                
                POINT = POINT + 1;
                IF POINT >= $3 THEN
                    IF TMP = $2 THEN
                        RETURN TD_CHAR($1) - POINT + 1;
                    END IF;
                END IF;
                
                /*中文则+1*/
                IF ASCII(SCAN) > 128 THEN
                    POINT = POINT + 1;
                END IF;
                
            END LOOP;
         END IF;
         POINT = 0;
     RETURN POINT;
    END;
$$
;

【总结】

因篇幅有限，只能举一些常用例子，具体实现的SQL可以参考附件。在现网某局点中，这些自定义函数能大大减少对数的工作量，但会有一定的性能损耗，在充分测试后，读者可以看看这样的性能损耗是否能在接受范围内。