Oraclenvarchar2存储特殊字符乱码问题
Oracle nvarchar2存储特殊字符乱码问题
这个问题研究了⼀天多，终于搞定了。
起因是业务需要存特殊字符'ø'到varchar2的字段中出现乱码，因为数据库字符集是ZHS16GBK。
简单测试了下，像'ø'之类的特殊。由于国家字符集是AL16UTF16，准备⽤nvarchar2（nvarchar2⽤的是国家字符集）存储特殊字符。但是测试环境测试结果是就算⽤nvarchar2存，还是有乱码的情况。
重现如下：
[oracle@zkm ~]$ locale
LANG=en_US.UTF-8长沙水上乐园
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
[oracle@zkm ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
11:22:28 SYS@zkm(451)>lect urenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
Elapd: 00:00:00.01
11:22:06 SYS@zkm(451)>create table zkm ( name1 varchar2(20),name2 nvarchar2(20));
Table created.
Elapd: 00:00:01.39
11:30:12 SYS@zkm(451)>lect*from NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
-
第二次战役------------------------------------------------- --------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.4.0
悠悠古韵20 rows lected.
Elapd: 00:00:00.00
11:31:18 SYS@zkm(451)>inrt into zkm values ('ø','ø');
1 row created.
Elapd: 00:00:00.00
11:31:21 SYS@zkm(451)>commit;
Commit complete.
Elapd: 00:00:00.00
11:31:26 SYS@zkm(451)>lect*from zkm;
NAME1 NAME2
---------- ----------
？（这⾥是中⽂问号）
Elapd: 00:00:00.00
⽐对中⽂问号和英⽂问号：
14:19:01 SYS@zkm(451)>lect dump('?',1016) from dual union all lect dump('？',1016) from dual union all lect dump('ø',1016) from dual;
DUMP('?',1016)
---------------------------------------------------------------------------------------------------------------------------
Typ=96Len=1 CharacterSet=ZHS16GBK: 3f
Typ=96Len=2 CharacterSet=ZHS16GBK: a3,bf
Typ=96Len=2 CharacterSet=ZHS16GBK: a3,bf
Elapd: 00:00:00.01
也就是说，在ZHS16GBK下，中⽂"？"和"ø"最后成了⼀样的效果，也就是"ø"乱码了。
这个很好理解，Linux OS将UTF8类型的"ø"通过sqlplus这个客户端送进去数据库中，然后数据库通过NLS_LANG环境变量了解到进来的"ø"是UTF8编码的，于是通过⽐对UTF8编码表和GBK编码表对应的"ø"，将UTF8编码的"ø"转换成GBK的"ø"。
由于GBK不⽀持特殊字符"ø"，在GBK编码表中不存在对应的编码，于是使⽤GBK编码表中的中⽂"？"编码替代，通过上边的dump可知为a3bf，这个就是乱码的原因。
我们可以dump表zkm的name1，由于name2字段为nvarchar类型，该类型不使⽤数据库字符集ZHS16GBK，⽽是使⽤国家字符集
AL16UTF16，因此有如下结果：
14:38:24 SYS@zkm(87)>lect dump(name1,1016) name1,dump(name2,1016) name2 from zkm;
NAME1 NAME2
-------------------------------------------------- --------------------------------------------------
Typ=1Len=2 CharacterSet=ZHS16GBK: a3,bf Typ=1Len=2 CharacterSet=AL16UTF16: ff,1f
Elapd: 00:00:00.00
可以知道，将"ø"inrt进去表的name1，name2字段，确实变成了中⽂的"？" ，都是'a3bf'。
不过问题是nvarchar类型⽤的是AL16UTF16，为啥存不了"ø"？？
⽹上找资料才发现，要通过加N告诉这个特殊字符是Unicode字符才⾏。
⽤法参考：
于是：
15:35:30 SYS@zkm(1398)>lect dump(N'?',1016) from dual union all lect dump(N'？',1016) from dual union all lect dump(N'ø',1016) from dual;
DUMP(N'?',1016)
--------------------------------------------------------------------------------
Typ=96Len=2 CharacterSet=AL16UTF16: 0,3f
Typ=96Len=2 CharacterSet=AL16UTF16: ff,1f
Typ=96Len=2 CharacterSet=AL16UTF16: ff,1f
Elapd: 00:00:00.00
15:35:39 SYS@zkm(1398)>delete zkm;
1 row deleted.
Elapd: 00:00:00.01
15:35:44 SYS@zkm(1398)>inrt into zkm values ('ø',N'ø');
1 row created.
Elapd: 00:00:00.00
15:36:05 SYS@zkm(1398)>commit;
Commit complete.
Elapd: 00:00:00.00
15:36:44 SYS@zkm(1398)>lect*from zkm;
NAME1 NAME2
---------- ----------
？
Elapd: 00:00:00.00
加了N后还是发现，AL16UTF16下，中⽂"？"和"ø"还是⼀样的。
难道AL16UTF16不⽀持字符"ø"？？不可能啊。
于是在新建了⼀个字符集为AL32UTF8的库，做上边同样建表的操作，然后插⼊数据，⽆论是varchar或者nvarchar都不会乱码啊，部分如下操作：
SQL>lect dump(N'?',1016) from dual union all lect dump(N'？',1016) from dual union all lect dump(N'ø',1016) from dual;
DUMP(N'?',1016)
------------------------------------------
Typ=96Len=2 CharacterSet=AL16UTF16: 0,3f
吃肉减肥法
Typ=96Len=2 CharacterSet=AL16UTF16: ff,1f
Typ=96Len=2 CharacterSet=AL16UTF16: 0,f8
SQL>lect urenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
俄国文学之父AMERICAN_AMERICA.AL32UTF8
SQL>lect dump(N'?',1016) from dual union all lect dump(N'？',1016) from dual union all lect dump(N'ø',1016) from dual;
DUMP(N'?',1016)
------------------------------------------
Typ=96Len=2 CharacterSet=AL16UTF16: 0,3f
Typ=96Len=2 CharacterSet=AL16UTF16: ff,1f
Typ=96Len=2 CharacterSet=AL16UTF16: 0,f8
SQL>lect unistr('\00F8') from dual;
UNIS
----
ø
从这个实验基本能够确定，Oracle将特殊字符"ø"存⼊数据库的时候，是先通过存为数据库字符集ZHS16GBK，然后再通过ZHS16GBK转为国家字符集AL16UTF16。
由于转ZHS16GBK的时候已经乱码，再次转AL16UTF16的时候就有问题了。
新库已经证明了国家字符集可以存该特殊字符了。
那么到底如何能够将特殊字符存进去nvarchar⽽不乱码，百度找不到于是只能MOS搜了，果然找到了..
实际上，环境变量ORA_NCHAR_LITERAL_REPLACE默认值为fal，该变量表⽰任何从客户端传过来的NCHAR类型的字符先转换为数据库字符集，再转换为国家数据库字符集，⽽把该参数设为true后，从客户端传过来的NCHAR类型的字符直接转换为国家字符集存储，因此要想正确存储NCHAR字符集，最好得把该参数设为TRUE。
[oracle@zkm ~]$ export ORA_NCHAR_LITERAL_REPLACE=true
15:37:07 SYS@zkm(99)>delete from zkm;
1 row deleted.
Elapd: 00:00:00.00
15:37:14 SYS@zkm(99)>inrt into zkm values ('ø',N'ø');
1 row created.
Elapd: 00:00:00.00
15:37:19 SYS@zkm(99)>commit;
Commit complete.
Elapd: 00:00:00.00
15:37:22 SYS@zkm(99)> col name1 for a10
15:37:27 SYS@zkm(99)> col name2 for a10
15:37:27 SYS@zkm(99)>lect*from zkm;
NAME1 NAME2
---------- ----------
ø
Elapd: 00:00:00.00
由于name1为varchar类型本⾝不⽀持特殊字符乱码是正常的，⽽name2为nvarchar现在已经正常存取了。
开启会话，做10046。
执⾏sql，对应trace信息：
inrt into zkm values ('ø',N'ø');
对应的trc信息：
***2021-08-1816:03:01.812
WAIT #140175898804800: nam='SQL*Net message from client' ela=24006178 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273781812848
CLOSE #140175898804800:c=9,e=9,dep=0,type=1,tim=1629273781812992浪淘沙的诗意
=====================
PARSING IN CURSOR #140175898795576len=38 dep=0 uid=0 oct=2 lid=0 tim=1629273781814001 hv=1357752502 ad='ce798418' sqlid='fj1m2tx8fva5q'
inrt into zkm values ('U'\00F8')
END OF STMT
PARSE #140175898795576:c=941,e=950,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1629273781813995
EXEC #140175898795576:c=256,e=256,p=0,cr=1,cu=2,mis=0,r=1,dep=0,og=1,plh=0,tim=1629273781814387
STAT #140175898795576 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=195 us)'
WAIT #140175898795576: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273781814475
*** 2021-08-18 16:04:06.396
WAIT #140175898795576: nam='SQL*Net message from client' ela= 64582323 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846396824
CLOSE #140175898795576:c=9,e=9,dep=0,type=0,tim=1629273846397035
lect N'ø'from dual;
对应的trc信息：
PARSING IN CURSOR #140175898795576len=25 dep=0 uid=0 oct=3 lid=0 tim=1629273846398341 hv=1602785773 ad='f1c07ee8' sqlid='9rgvrkjgsj4gd'
lect U'\00F8'from dual
END OF STMT
耐力素质PARSE #140175898795576:c=1197,e=1208,p=0,cr=0,cu=2,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1629273846398339
EXEC #140175898795576:c=28,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1629273846398525
WAIT #140175898795576: nam='SQL*Net message to client' ela=2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846398588
FETCH #140175898795576:c=13,e=13,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1629273846398649
STAT #140175898795576 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
WAIT #140175898795576: nam='SQL*Net message from client' ela=384 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846399149
FETCH #140175898795576:c=9,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=1629273846399258
WAIT #140175898795576: nam='SQL*Net message to client' ela=2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1629273846399369
注意，正常存取需要满⾜3个条件
1. 客户端的字符集需要⽀持⽬标字符
2. 正确设置环境变量NLS_LANG
3. 正确设置环境变量ORA_NCHAR_LITERAL_REPLACE
在这个案例中，sqlplus是在服务器Linux上的，因此sqlplus本⾝没有字符集所以依托Linux字符集为UTF8，⽽UTF8满⾜第⼀个条件。
另外，NLS_LANG=AMERICAN_AMERICA.AL32UTF8和Linux的UTF8⼀致是正确的。
最后ORA_NCHAR_LITERAL_REPLACE=TRUE控制了Oracle存Unicode⾏为。
需要注意的另外⼀点，由于我的Linux是远程通过SecureCRT⼯具连接的，所以对应CRT的会话的字符集要设置和Linux字符集⼀样：
也就是说如果使⽤windows上边的sqlplus客户端，该客户端⽤的是936也就是简体中⽂字符集。
那么即使NLS_LANG和ORA_NCHAR_LITERAL_REPLACE设置正确也会有问题，因为客户端使⽤936(简体中⽂)送进去特殊字符"ø"的时候就已经出问题了，936不⽀持"ø"。
每周的英语
C:\Urs\admin>chcp
活动代码页: 936
C:\Urs\admin>t nls_lang
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
C:\Urs\admin>t ORA_NCHAR_LITERAL_REPLACE
ORA_NCHAR_LITERAL_REPLACE=TRUE
C:\Urs\admin>sqlplus sys/zkm@192.168.1.152/zkm as sysdba
SQL*Plus: Relea 11.2.0.4.0 Production on Wed Aug 1817:07:282021
Copyright (c) 1982, 2013, Oracle. All rights rerved.
Connected to:
Oracle Databa 11g Enterpri Edition Relea 11.2.0.4.0- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>lect urenv('language') from dual;
USERENV('LANGUAGE')
-
---------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL>delete zkm;
2 rows deleted.
SQL>inrt into zkm values ('ø',N'ø');
1 row created.
SQL>commit;
Commit complete.
SQL> col name1 for a10
SQL> col name2 for a10
SQL>lect*from zkm;
NAME1 NAME2
---------- ----------
另外，plsqldev这个⼯具，对于N'ø'这类特殊字符存储进去nchar或者nvarchar2字段的时候，不需要设置
ORA_NCHAR_LITERAL_REPLACE也可以成功正常存取。
对plsqdev的会话开启10046，然后执⾏sql可以看出区别：
查会话sid：
然后oracle使⽤sys登录对该会话做10046：
17:26:10 SYS@zkm(1264)>lect a.sid,a.rial#,b.pid,b.spid from v$ssion a ,v$process b where a.sid=&sid and b.addr=a.paddr;
Enter value for sid: 904
old 1: lect a.sid,a.rial#,b.pid,b.spid from v$ssion a ,v$process b where a.sid=&sid and b.addr
=a.paddr
