Oracle 数据库从 gbk 更换到 utf8 数据库兼容性问题解决

2019年04月10日 12:07 · 阅读(3209) ·

[目录]

参考资料

彻底弄懂 Unicode 编码

字符编码笔记：ASCII，Unicode 和 UTF-8

UTF-8

字符编码

Oracle 字符集的查看和修改

Oracle Database Migration Assistant for Unicode

业务背景

之前数据库的编码是 gbk，为了业务的国际化，需要把编码修改为 utf8
由于 gbk 和 utf8 对应汉字的存储字节不同，所以需要对之前的字段做兼容性处理

Oracal数据库当前字符编码

查询sql：select userenv('language') from dual;

查询结果：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

gbk 和 utf8 对应字节

编码类型 英文字节 汉字字节
gbk 1 2
utf-8 1 3 
  1. string strTmp = "h";
  2. int ascii = System.Text.Encoding.ASCII.GetBytes(strTmp).Length;
  3. int unicode = System.Text.Encoding.Unicode.GetBytes(strTmp).Length;
  4. int gbk = System.Text.Encoding.Default.GetBytes(strTmp).Length;
  5. int utf8 = System.Text.Encoding.UTF8.GetBytes(strTmp).Length;
  6. Console.WriteLine("ascii-----：" + ascii);
  7. Console.WriteLine("unicode---：" + unicode);
  8. Console.WriteLine("gbk-------："+ gbk);
  9. Console.WriteLine("utf8------：" + utf8);
  11. Console.ReadKey();

结果：

  1. ascii-----：1
  2. unicode---：2
  3. gbk-------：1
  4. utf8------：1
  1. string strTmp = "汉";
  2. int ascii = System.Text.Encoding.ASCII.GetBytes(strTmp).Length;
  3. int unicode = System.Text.Encoding.Unicode.GetBytes(strTmp).Length;
  4. int gbk = System.Text.Encoding.Default.GetBytes(strTmp).Length;
  5. int utf8 = System.Text.Encoding.UTF8.GetBytes(strTmp).Length;
  6. Console.WriteLine("ascii-----：" + ascii);
  7. Console.WriteLine("unicode---：" + unicode);
  8. Console.WriteLine("gbk-------："+ gbk);
  9. Console.WriteLine("utf8------：" + utf8);
  11. Console.ReadKey();

结果：

  1. ascii-----：1
  2. unicode---：2
  3. gbk-------：2
  4. utf8------：3

修改思路

从下表可知，gbk 和 utf8 英文都只占用1个字节
gbk 1个汉字占用2个字节，utf8 占用3个，3/2=1.5

编码类型 英文字节 汉字字节
gbk 1 2
utf-8 1 3

如果之前的 gbk 编码类型长度为 255，修改为 utf8 后，对应类型长度应该为

255*1.5 = 382.5

为了更好的兼容，我们修改为之前长度的两倍 255*2

使用 oracle dmu 得到需要扩容表和字段

什么是 Oracle Database Migration Assistant for Unicode？

Oracle Database Migration Assistant for Unicode (DMU) 是一款独特的下一代迁移工具，提供将数据库从传统编码迁移到 Unicode 的端到端解决方案。通过在整个迁移流程中为 DBA 提供指导并实现许多迁移任务的自动化，DMU 直观的用户界面极大简化了迁移流程并降低了对字符集迁移专业知识的要求。它采用可扩展的就地迁移架构，与传统的导出和导入迁移方法相比，可显著减少数据转换所需的工作和停机时间。对于迁移后的数据库和已经使用 Unicode 字符集的现有数据库，DMU 还提供了一种验证模式，可识别未正确用 Unicode 编码的数据，从而对数据库应用中的 Unicode 实现的潜在问题进行健康检查。

从 2.1 版开始，DMU 与 Oracle GoldenGate 复制技术结合使用，支持近乎零停机时间的迁移模型。结合使用 DMU 与 GoldenGate 12.1.2.1.0 或更高版本，您可以设置这样一个迁移过程，利用 DMU 的数据准备和就地转换功能，利用 GoldenGate 复制迁移过程中生产系统上发生的增量数据更改，从而有效地消除停机时间窗口需求。

思路

功能大致如下
1.全库扫描表字段，字段类型，字段值，字符编码
2.把对应表的字符编码从 gbk 转换为 utf8，如果之前字段的长度能够存储对应内容，就通过
3.如果之前字段的长度不能存储对应内容，比如存不下了，就会在 system.dum$exceptions 表里面添加对应表，行，列信息，这个字段就是需要扩展的字段

辅助查询

查询表信息

  1. --查询表信息
  2. select * from dba_objects
  3. where  upper(object_name) ='Student'

查询表对应的列信息

  1. --查询表对应的列信息
  2. select * from dba_tab_columns
  3. where TABLE_NAME='Student'

查询兼容的异常信息

  1. --查询兼容的异常信息
  2. select decode(sde.type,
  3.                       2,
  4.                       'exceed column',
  5.                       4,
  6.                       'exceed datatype',
  7.                       8,
  8.                       'invalid') as "DMU_TYPE",
  9.                sde.row_id, --表行Id
  10.                sde.obj#,    --表的Id
  11.                sde.intcol# --表列Id
  12. from system.dum$exceptions sde
  13. where  sde.obj# = 1301018 --表对象Id

获取表对应行，列对应的值方法

  1. create or replace function dmu_data_detail_f(p_object_id in number,
  2.                                              p_column_id in number,
  3.                                              p_rowid     in varchar2)
  4.   return varchar2 is
  5.   l_data_detail    varchar2(4000);
  6.   l_data_query_sql VARCHAR2(2000);
  7.   l_column_name    VARCHAR2(128);
  8.   l_table_name     VARCHAR2(128);
  9.   l_owner_name     VARCHAR2(128);
  10. begin
  11.   select dtc.column_name
  12.     into l_column_name
  13.     from dba_objects do, dba_tab_columns dtc
  14.    where do.object_name = dtc.TABLE_NAME
  15.      and do.OWNER = dtc.OWNER
  16.      and dtc.COLUMN_ID = p_column_id
  17.      and do.object_id = p_object_id;
  19.   select object_name, owner
  20.     into l_table_name, l_owner_name
  21.     from dba_objects
  22.    where object_id = p_object_id;
  24.   l_data_query_sql := 'select ' || l_column_name || ' from ' ||
  25.                       l_owner_name || '.' || l_table_name ||
  26.                       ' where rowid=''' || p_rowid || '''';
  28.   --dbms_output.put_line(l_data_query_sql);
  30.   execute immediate l_data_query_sql
  31.     into l_data_detail;
  33.   return(l_data_detail);
  34. end dmu_data_detail_f;

查询对应表不兼容的字段-记录数少时使用

  1. --当查询结果记录数少时使用
  2. select owner as "用户",
  3.        object_name as "对象名",
  4.        object_type as "对象类型",
  5.        COLUMN_NAME as "数据列",
  6.        DATA_TYPE as "数据类型",
  7.        DMU_TYPE as "扩展类型",
  8.        dmu_data_detail_f(obj#, intcol#, row_id) as "例外数据"
  9.   from (select do.owner,
  10.                do.object_name,
  11.                do.object_type,
  12.                dto.COLUMN_NAME,
  13.                dto.DATA_TYPE || '(' || dto.DATA_LENGTH || ')' as "DATA_TYPE",
  14.                decode(sde.type,
  15.                       2,
  16.                       'exceed column',
  17.                       4,
  18.                       'exceed datatype',
  19.                       8,
  20.                       'invalid') as "DMU_TYPE",
  21.                sde.row_id,
  22.                sde.obj#,
  23.                sde.intcol#
  24.           from system.dum$exceptions sde,
  25.                dba_objects           do,
  26.                dba_tab_columns       dto
  27.          where sde.obj# = do.object_id
  28.            and sde.intcol# = dto.COLUMN_ID
  29.            and do.object_name = dto.TABLE_NAME
  30.            and do.OWNER = dto.OWNER
  31.            and upper(do.object_name) = upper('&p_table_name')
  32.         --and sde.type = &p_dmu_type
  33.          order by OWNER, object_name, COLUMN_NAME, TYPE);

查询对应表不兼容的字段-数据量大时使用

  1. select '用户.表','字段名称',字段名称 ,255 data_length,cux_utf8_pub.count_byte_as_utf8(字段名称),rowid row_id 
  2. from 用户.表 t where exists (
  3.           select null
  4.           from system.dum$exceptions sde,
  5.                dba_objects           do,
  6.                dba_tab_cols       dto
  7.           where sde.obj# = do.object_id
  8.            and sde.intcol# = dto.internal_column_id
  9.            and do.object_name = dto.table_name
  10.            and do.owner = dto.owner
  11.            and do.owner = '用户'
  12.            and dto.column_name = '字段名称'
  13.            and upper(do.object_name) = '表名称'
  14.            and sde.row_id = t.rowid
  15. );

修改 sql

从上面得到对应的表和不兼容的字段，对应修改该字段的字段长度或类型即可，例如

  1. -- =============================================
  2. -- Author:      luoma
  3. -- Create date: 2019年4月9日15:48:25
  4. -- Description: 字符编码从 SIMPLIFIED CHINESE_CHINA.ZHS16GBK 修改为 utf-8,所以对应字段要进行扩容
  5. --              gbk和utf8 英文都占用1个字符，gbk中文占用2个字符，utf8占用3个，所以对应字段需要扩容为之前的1.5倍，这里我们修改为2倍
  6. -- =============================================
  7. alter table Student
  8. modify Name varchar2(383);

字符串截取

测试sql

  1. -- =============================================
  2. -- Author:       luoma
  3. -- Create date:  2019年4月11日10:50:24
  4. -- Description:  用utf8字符集截取对应表超过指定utf8编码长度的字符串
  5. -- =============================================
  6. declare
  7.     l_ret_var varchar2(32767);--存储需要截取的字符串
  8.     l_var     varchar2(3);    --存储截取的单个字符串
  9.     l_len     number := 0;    --用于已经截取的utf8字符总长度
  10.     l_dump    number := 0;    --存储单个utf8字符长度
  11.     p_lengthb  number := 25;   --需要截取的字符长度
  12. begin
  13.     --获取超过指定用UTF8字符集计算字符串字节数的数据
  14.     for item in (select no,name from test_v_hwhao1 where (nvl(lengthb(name), 0) -  nvl(length(name), 0)) + nvl(lengthb(name), 0) > p_lengthb )
  15.     loop
  16.       --初始化变量
  17.       l_len := 0;
  18.       l_ret_var := '';
  20.       --处理超过指定长度的字段
  21.       for i in 1 .. length(item.name)
  22.       loop
  23.         --截取单个字符串
  24.         l_var := substr(item.name,i,1);
  25.         --因为之前使用 gbk 编码，所以一个中文占用2个字符
  26.         if (lengthb(l_var) = 2) then 
  27.           --utf8 一个中文字符长度为3
  28.           l_dump := 3;
  29.         else
  30.           --utf8 其它字符长度为1
  31.           l_dump := 1;
  32.         end if;
  34.         --已经总共统计了多少个 utf8 字符长度
  35.         l_len := l_len + l_dump;
  37.         --如果截取的长度还未达到需要截取的长度
  38.         if(l_len <= p_lengthb) then 
  39.           --继续添加
  40.           l_ret_var := l_ret_var || l_var;
  41.         end if;
  43.         --如果截取的长度达到了需要截取的长度
  44.         if(l_len >= p_lengthb) then
  45.           --把截取后的长度修改到表字段
  46.           update test_v_hwhao1
  47.           set name = l_ret_var
  48.           where no = item.no;
  49.           --提交修改
  50.           commit;
  51.           --退出循环
  52.           exit;
  53.         end if;
  55.       end loop i;
  57.     end loop;
  58. end;

测试表

测试完成后，结果应该为下图中红圈部分

select * from test_v_hwhao1 order by no

测试前

测试后

cux_utf8_pub

  1. create or replace package body cux_utf8_pub is
  3.   --用UTF8字符集计算字符串字节
  4.   function count_byte_as_utf8(p_text in varchar2) return number is
  5.     l_char_lenth number := 0;
  6.     l_byte_lenth number := 0;
  7.   begin
  8.     l_char_lenth := nvl(length(p_text), 0);
  9.     l_byte_lenth := nvl(lengthb(p_text), 0);
  10.     return(l_byte_lenth - l_char_lenth) + l_byte_lenth;
  11.   end count_byte_as_utf8;
  13.   --用UTF8字符集截取字符串，确保最后一位不是乱码
  14.   function substrb_use_utf8(p_text in varchar2, p_lengthb in number)
  15.     return varchar2 is
  16.     l_ret_var varchar2(32767);
  17.     l_var     varchar2(3);
  18.     l_len     number := 0;
  19.     l_dump    number := 0;
  20.   begin
  22.     if (count_byte_as_utf8(p_text) <= p_lengthb) then
  23.       return p_text;
  24.     end if;
  26.     for i in 1 .. length(p_text) loop
  27.       l_var := substr(p_text, i, 1);
  29.       if (lengthb(l_var) = 2) then
  30.         l_dump := 3;
  31.       else
  32.         l_dump := 1;
  33.       end if;
  35.       l_len := l_len + l_dump;
  36.       if (l_len <= p_lengthb) then
  37.         l_ret_var := l_ret_var || l_var;
  38.       end if;
  40.       if (l_len >= p_lengthb) then
  41.         return l_ret_var;
  42.       end if;
  44.     end loop i;
  46.     return l_ret_var;
  47.   end substrb_use_utf8;
  49.   --用GBK字符集截取字符串，确保最后一位不是乱码
  50.   function substrb_use_gbk(p_text in varchar2, p_lengthb in number)
  51.     return varchar2 is
  52.     l_last_string1 varchar2(4000);
  53.     l_last_string2 varchar2(4000);
  54.   begin
  56.     if (lengthb(p_text) <= p_lengthb) then
  57.       return p_text;
  58.     end if;
  60.     --截取逻辑
  61.     if (length(substrb(p_text, p_lengthb, 2)) = 1) then
  62.       --截取少一位
  63.       return substrb(p_text, 1, p_lengthb - 1);
  64.     else
  65.       return substrb(p_text, 1, p_lengthb);
  66.     end if;
  68.   end substrb_use_gbk;
  70.   procedure execute_substrb(p_owner       in varchar2,
  71.                             p_table_name  in varchar2,
  72.                             p_column_name in varchar2,
  73.                             p_debug_flag  in varchar2 default 'Y') is
  74.     l_bakup_sql        varchar2(4000);
  75.     l_datafix_sql      varchar2(4000);
  76.     l_count_length_sql varchar2(4000);
  77.     l_extend_sql       varchar2(4000);
  78.     g_max_length       number := 4000;
  79.     l_length           number;
  80.   begin
  82.     --截断处理
  83.     for cur in (select t.rowid row_id, t.*, c.data_length
  84.                   from cux_utf8_substr_list t, dba_tab_cols c
  85.                  where t.table_name = c.table_name
  86.                    and t.owner = c.owner
  87.                    and t.column_name = c.column_name
  88.                    and c.data_type = 'VARCHAR2'
  89.                    and t.owner = nvl(p_owner, t.owner)
  90.                    and t.column_name = nvl(p_column_name, t.column_name)
  91.                    and t.table_name = nvl(p_table_name, t.table_name)
  92.                    and convert_type = 'CUT') loop
  94.       l_bakup_sql := 'insert into cux_utf8_substr_bak
  95.         (table_name, owner, column_name, bak_data, bak_date, data_row_id)
  96.         select ''' || cur.table_name || ''',''' ||
  97.                      cur.owner || ''',''' || cur.column_name || ''',' ||
  98.                      cur.column_name || ',sysdate, t.rowid row_id from ' ||
  99.                      cur.owner || '.' || cur.table_name ||
  100.                      ' t where exists (select null
  101.           from system.dum$exceptions sde,
  102.                dba_objects           do,
  103.                dba_tab_cols       dto
  104.          where sde.obj# = do.object_id
  105.            and sde.intcol# = dto.internal_column_id
  106.            and do.object_name = dto.table_name
  107.            and do.owner = dto.owner
  108.            and do.owner = upper(''' || cur.owner || ''')
  109.            and dto.column_name = upper(''' ||
  110.                      cur.column_name || ''')
  111.            and upper(do.object_name) = upper(''' ||
  112.                      cur.table_name || ''')
  113.            and sde.row_id = t.rowid)';
  114.       --dbms_output.put_line(l_sql);
  116.       /*insert into cux_utf8_substr_bak
  117.       (table_name, owner, column_name, bak_data, bak_date)
  118.       select cur.table_name,
  119.              cur.owner,
  120.              cur.column_name,
  121.              dmu_data_detail_f(obj#, intcol#, row_id),
  122.              sysdate
  123.         from system.dum$exceptions sde,
  124.              dba_objects           do,
  125.              dba_tab_cols       dto
  126.        where sde.obj# = do.object_id
  127.          and sde.intcol# = dto.internal_column_id
  128.          and do.object_name = dto.table_name
  129.          and do.owner = dto.owner
  130.          and do.owner = upper(cur.owner)
  131.          and dto.column_name = upper(cur.column_name)
  132.          and upper(do.object_name) = upper(cur.table_name);*/
  134.       l_datafix_sql := 'update ' || cur.owner || '.' || cur.table_name ||
  135.                        ' t set ' || cur.column_name ||
  136.                        '=cux_utf8_pub.substrb_use_utf8(' || cur.column_name || ',' ||
  137.                        cur.data_length ||
  138.                        ') where exists (select null
  139.           from system.dum$exceptions sde,
  140.                dba_objects           do,
  141.                dba_tab_cols       dto
  142.          where sde.obj# = do.object_id
  143.            and sde.intcol# = dto.internal_column_id
  144.            and do.object_name = dto.table_name
  145.            and do.owner = dto.owner
  146.            and do.owner = upper(''' || cur.owner || ''')
  147.            and dto.column_name = upper(''' ||
  148.                        cur.column_name || ''')
  149.            and upper(do.object_name) = upper(''' ||
  150.                        cur.table_name || ''')
  151.            and sde.row_id = t.rowid)';
  153.       if (p_debug_flag = 'N') THEN
  154.         update cux_utf8_substr_list t
  155.            set t.process_flag       = 'P',
  156.                t.process_date_start = sysdate,
  157.                t.datafix_sql        = l_datafix_sql,
  158.                t.bakup_sql          = l_bakup_sql
  159.          where rowid = cur.row_id;
  161.         execute immediate l_bakup_sql;
  163.         execute immediate l_datafix_sql;
  165.         update cux_utf8_substr_list t
  166.            set t.process_flag = 'S', t.process_date_end = sysdate
  167.          where rowid = cur.row_id;
  169.         commit;
  170.       else
  171.         dbms_output.put_line('bakup:');
  172.         dbms_output.put_line(l_bakup_sql);
  173.         dbms_output.put_line('');
  174.         dbms_output.put_line('datafix:');
  175.         dbms_output.put_line(l_datafix_sql);
  176.       end if;
  177.     end loop cur;
  179.     --扩展处理
  180.     for cur in (select t.rowid row_id, t.*, c.data_length
  181.                   from cux_utf8_substr_list t, dba_tab_cols c
  182.                  where t.table_name = c.table_name
  183.                    and t.owner = c.owner
  184.                    and t.column_name = c.column_name
  185.                    and c.data_type = 'VARCHAR2'
  186.                    and t.owner = nvl(p_owner, t.owner)
  187.                    and t.column_name = nvl(p_column_name, t.column_name)
  188.                    and t.table_name = nvl(p_table_name, t.table_name)
  189.                    and convert_type = 'EXT') loop
  190.       l_count_length_sql := 'SELECT max(cux_utf8_pub.count_byte_as_utf8(' ||
  191.                             cur.column_name || ')) from ' || cur.owner || '.' ||
  192.                             cur.table_name || ' t' ||
  193.                             ' where exists (select null
  194.           from system.dum$exceptions sde,
  195.                dba_objects           do,
  196.                dba_tab_cols       dto
  197.          where sde.obj# = do.object_id
  198.            and sde.intcol# = dto.internal_column_id
  199.            and do.object_name = dto.table_name
  200.            and do.owner = dto.owner
  201.            and do.owner = upper(''' || cur.owner || ''')
  202.            and dto.column_name = upper(''' ||
  203.                             cur.column_name || ''')
  204.            and upper(do.object_name) = upper(''' ||
  205.                             cur.table_name || ''')
  206.            and sde.row_id = t.rowid)';
  208.       execute immediate l_count_length_sql
  209.         into l_length;
  211.       if (l_length > g_max_length) then
  212.         dbms_output.put_line(cur.owner || '.' || cur.table_name || '.' ||
  213.                              cur.column_name || '扩展后的长度:' || l_length ||
  214.                              '超过4000');
  215.         update cux_utf8_substr_list t
  216.            set t.process_flag = 'E', t.process_date_start = sysdate
  217.          where rowid = cur.row_id;
  218.         commit;
  219.         continue;
  220.       end if;
  222.       l_extend_sql := 'alter table ' || cur.owner || '.' || cur.table_name ||
  223.                       ' modify ' || cur.column_name || ' VARCHAR2(' ||
  224.                       l_length || ')';
  226.       l_bakup_sql := 'alter table ' || cur.owner || '.' || cur.table_name ||
  227.                      ' modify ' || cur.column_name || ' VARCHAR2(' ||
  228.                      cur.data_length || ')';
  230.       if (p_debug_flag = 'N') THEN
  231.         update cux_utf8_substr_list t
  232.            set t.process_flag       = 'P',
  233.                t.process_date_start = sysdate,
  234.                t.extend_sql         = l_extend_sql,
  235.                t.bakup_sql          = l_bakup_sql
  236.          where rowid = cur.row_id;
  238.         execute immediate l_extend_sql;
  240.         update cux_utf8_substr_list t
  241.            set t.process_flag = 'S', t.process_date_end = sysdate
  242.          where rowid = cur.row_id;
  244.         commit;
  245.       else
  246.         dbms_output.put_line('extend sql:');
  247.         dbms_output.put_line(l_extend_sql);
  248.         dbms_output.put_line('bakup_sql:');
  249.         dbms_output.put_line(l_bakup_sql);
  250.       end if;
  252.     end loop cur;
  254.   end execute_substrb;
  256.   procedure get_quick_query_sql(p_owner       in varchar2 default null,
  257.                                 p_table_name  in varchar2,
  258.                                 p_column_name in varchar2 default null) is
  259.     l_sql varchar2(4000);
  260.   begin
  262.     for cur in (select dto.data_length,
  263.                        do.object_name,
  264.                        do.owner,
  265.                        dto.column_name,
  266.                        do.object_id,
  267.                        dto.internal_column_id
  268.                   from dba_objects         do,
  269.                        dba_tab_cols     dto,
  270.                        cux_utf8_error_info cu
  271.                  where do.object_name = dto.table_name
  272.                    and do.owner = dto.owner
  273.                    and do.owner = cu.owner
  274.                    and dto.column_name = cu.column_name
  275.                    and do.object_name = cu.table_name
  276.                    and cu.owner = nvl(upper(p_owner), cu.owner)
  277.                    and cu.table_name = upper(p_table_name)
  278.                    and cu.column_name =
  279.                        nvl(upper(p_column_name), cu.column_name)) loop
  280.       l_sql := 'select ''' || cur.owner || '.' || cur.object_name ||
  281.                ''',''' || cur.column_name || ''',' || cur.column_name || ' ,' ||
  282.                cur.data_length ||
  283.                ' data_length,cux_utf8_pub.count_byte_as_utf8(' ||
  284.                cur.column_name || '),rowid row_id from ' || cur.owner || '.' ||
  285.                cur.object_name || ' t where exists (select null
  286.           from system.dum$exceptions sde,
  287.                dba_objects           do,
  288.                dba_tab_cols       dto
  289.          where sde.obj# = do.object_id
  290.            and sde.intcol# = dto.internal_column_id
  291.            and do.object_name = dto.table_name
  292.            and do.owner = dto.owner
  293.            and do.owner = ''' || cur.owner || '''
  294.            and dto.column_name = ''' || cur.column_name || '''
  295.            and upper(do.object_name) = ''' || cur.object_name || '''
  296.            and sde.row_id = t.rowid);';
  297.       dbms_output.put_line(l_sql);
  298.       dbms_output.put_line('');
  299.     end loop cur;
  300.   end get_quick_query_sql;
  301. end cux_utf8_pub;