医疗行业里Oracle令人头疼的生僻字处理案例

原创徐sir 2024-05-08

1535

事情起因

有小伙伴找到我问一下生僻字的问题，数据库是oracle 11g，字符集是zhs16gbk

下图里显示的？？应该是“𧿹”这个字，算是个生僻字。

问应用厂家就说数据库字符集建错了，要改库的字符集。what???，这库都用了好几年了，现在改库字符集也不现实啊。
检查下字典点，看表里也是？？显示

模拟测试

为了测试，先创建个测试表，第1列是varchar2，第2列是nvarchar2
这里为了方便测试，使用scott用户

  CREATE TABLE "SCOTT"."TEST_NAME" 
   (	"BIANMA" NUMBER NOT NULL ENABLE, 
	"VARCHAR2_NAME" VARCHAR2(200 BYTE), 
	"NVARCHAR2_NAME" NVARCHAR2(200), 
	 CONSTRAINT "TEST_NAME_PK" PRIMARY KEY ("BIANMA")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

创建如下图

这里提供几个把生僻字转换为Unicode的网址

http://unicode.wiicha.com/
https://www.bejson.com/convert/unicode_chinese/

查询一下这个字

\u是转换字符，用的时候去掉
查询一下

select utl_raw.cast_to_nvarchar2('d85fdff9') from dual;

在库里组合查询一下，看出来也可以显示

select utl_raw.cast_to_nvarchar2('d85fdff96307') from dual;

更新一下两个列里的内容

insert into  test_name  values (1,(select utl_raw.cast_to_nvarchar2('d85fdff96307') from dual),(select utl_raw.cast_to_nvarchar2('d85fdff96307') from dual));
commit;
select * from test_name

可以看出来，使用这种unicode转换的方式，只对nvchar2生效，varchar2还是显示乱码

关于varchar2和nvchar2

引用ASKTOM里的解答
NVARCHAR Vs. VARCHAR - Ask TOM (oracle.com)

Thanks for the question, Andrew.
Asked: September 04, 2015 - 3:00 pm UTC
Last updated: February 25, 2020 - 8:20 am UTC
Version: 12.0.2
Viewed 50K+ times! This question is
You Asked
In a database with character sets defined as:
NLS_CHARACTERSET = AL32UTF8
NLS_NCHAR_CHARACTERSET = UTF8
Would there be any difference in the (language) character sets that could be stored by VARCHAR2 Vs. NVARCHAR2? As in
NAME VARCHAR2(60 CHAR)
Vs.
NAME NVARCHAR2(60 CHAR)
and Chris said…
NVARCHAR2 is a unicode-only data type. It’s useful if you want to have unicode for some columns (those that are NVARCHAR2) but have the rest of the database use a different characterset. In all other cases stick with using VARCHAR2.
In your case the only difference between UTF8 and AL32UTF8 is the AL version includes “supplementary characters”.
See also:
http://www.oracle.com/technetwork/database/database-technologies/globalization/twp-appdev-unicode-10gr2-129234.pdf
and
http://docs.oracle.com/database/121/NLSPG/ch6unicode.htm#NLSPG006

解决办法

根据以上测试的情况来看，有几个方案：
方案1、列由原来的varchar2改为nvarchar2，再插入unicode编码。
方案2、新建个utf8字符集的库，导出现有全库，再导入回去。
方案3、𧿹改写成别的可识别字符，如（足母）代替。
下面一个一个说一下几个方案：
方案1
需要停业务改表，结构，测试了一下，varchar2可以直接改成nvarchar2，但是nvarchar2改成varchar2要求必须没有数据。实际生产我也没测试过直接改列的字段类型，不知道会不会有问题。。
目前从网上收录2个操作方法
操作A
1、将表字段修改类型为：NVARCHAR2(),无论该字段是否存值都可以直接修改 alter table 表名 modify (字段名 nvarchar2(20));
注：如果想从NVARCHAR2（）改回VARCHAR2,会报错：“ORA-01439:要更改数据类型,则要修改的列必须为空”，
解决方案为：①、将该字段A改名B；②、新建表字段，命名为A，将B值更新到A；③、删除B字段
2、使用该sql将生僻字插入到表中： update 表名 set 字段名 = N’生僻字’ where …
注意set字段名字前一定要有N，且在’'外。
操作B
也有人提供一个中转的解决办法:
修改字段类型，需要先将该字段清空，如果需要保存数据，可以先建一个临时的字段存储这些数据，然后删除要修改的列，再重新创建

--临时字段
alter table test_name add TEMP nvarchar2(200);
--赋转换后的值
update test_name set TEMP=cast(VARCHAR2_NAME as nvarchar2(200));
commit;
--删除字段
alter table test_name drop column VARCHAR2_NAME;
--重建
ALTER TABLE TEST_NAME ADD (VARCHAR2_NAME NVARCHAR2(200) );
--赋值
update test_name set VARCHAR2_NAME=TEMP;
--临时字段的使命完成了
alter table test_name drop column TEMP;
select * from test_name
insert into  test_name  values (2,(select utl_raw.cast_to_nvarchar2('d85fdff96307') from dual),(select utl_raw.cast_to_nvarchar2('d85fdff96307') from dual));

方法2：
基本不可行，一是停机时间太长，再就是改了字符集再重新导入的时候也可能会遇到字段长度不够的问题。
方法3：
就是真实案例，问了一下别家医院（几个不同厂家的HIS系统）是怎么解决这个问题的，结果。。。。就是写成（足母），别笑，真的好多家都这么搞的！！

另1家医院也是这样