[20241118]NLS_LANG設定問題2.txt

lfree發表於2024-11-18
[20241118]NLS_LANG設定問題2.txt

--//連結 https://www.itpub.net/thread-2155589-1-1.html上的討論。

--//PiscesCanon指出：

--//NLS_LANG設定錯了，如果你的客戶端是sqlplus，那麼應該是NLS_LANG=.AL32UTF8或者NLS_LANG=AMERICAN_AMERICA.AL32UTF8，跟著
--//OS的字符集來。另外，SecureCRT或者xshell這類工具本身有自己的字符集，那麼也要設定為UTF8。

--//NLS_LANG這個環境變數的作用就是告訴資料庫，你客戶端(sqlplus、plsql dev之類的)所使用的字符集是什麼。這樣我資料庫才能根
--//據客戶端過來的對應字符集編碼根據編碼表轉化為資料庫本身的編碼來儲存。

--//跟我以前的理解或者一般人的理解不一樣，測試看看。
--//我以前的測試連結：[20240820]字符集與dml語句.txt

1.環境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

--//linux:
# cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)

# echo $LANG
en_US.UTF-8

SCOTT@book01p> select * from v$nls_parameters ;
PARAMETER VALUE CON_ID
------------------------------ ------------------------------------ ----------
NLS_LANGUAGE AMERICAN 3
NLS_TERRITORY AMERICA 3
NLS_CURRENCY $ 3
NLS_ISO_CURRENCY AMERICA 3
NLS_NUMERIC_CHARACTERS ., 3
NLS_CALENDAR GREGORIAN 3
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS 3
NLS_DATE_LANGUAGE AMERICAN 3
NLS_CHARACTERSET ZHS16GBK 3
NLS_SORT BINARY 3
NLS_TIME_FORMAT HH.MI.SSXFF AM 3
NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF 3
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZH:TZM 3
NLS_TIMESTAMP_TZ_FORMAT YYYY-MM-DD HH24:MI:SS.FF TZH:TZM 3
NLS_DUAL_CURRENCY $ 3
NLS_NCHAR_CHARACTERSET AL16UTF16 3
NLS_COMP BINARY 3
NLS_LENGTH_SEMANTICS BYTE 3
NLS_NCHAR_CONV_EXCP FALSE 3
19 rows selected.
--//建立資料庫字符集NLS_CHARACTERSET=ZHS16GBK,而NLS_NCHAR_CHARACTERSET=AL16UTF16，這種情況幾乎是前幾年大部分資料庫的建立
--//模式,至少我們團隊建立的資料庫基本都是這樣.

$ env | grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT=HH24.MI.SSXFF TZH:TZM
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
--//以前的測試定義NLS_LANG=AMERICAN_AMERICA.ZHS16GBK，這次測試設定NLS_LANG=AMERICAN_AMERICA.AL32UTF8看看。

2.測試：
$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

SCOTT@book01p> create table t1 (id number ,vc varchar2(20),nvc nvarchar2(40));
Table created.

--//在linux服務端插入:
SCOTT@book01p> insert into t1 values (1,'1測試2','a測試b');
1 row created.

SCOTT@book01p> commit;
Commit complete.

--//在linux服務端查詢:
SCOTT@book01p> column vc format a20
SCOTT@book01p> column nvc format a20
SCOTT@book01p> select * from t1;

ID VC NVC
---------- -------------------- --------------------
1 1測試2 a測試b

--//在windows客戶端查詢:
D:\tmp\study> set | grep -i nls_l
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

SCOTT@book01p> insert into t1 values (2,'3測試4','c測試d');
1 row created.

SCOTT@book01p> commit;
Commit complete.

SCOTT@book01p> select * from t1;
ID VC NVC
---------- -------------------- -----------------------------------
1 1測試2 a測試b
2 3測試4 c測試d

--//確實如此,顛覆我以前的認知。

--//在linux服務端查詢:
SCOTT@book01p> select dump(vc,16) c40 ,dump(nvc,16) c50 from t1 ;
C40 C50
---------------------------------------- --------------------------------------------------
Typ=1 Len=6: 31,b2,e2,ca,d4,32 Typ=1 Len=8: 0,61,6d,4b,8b,d5,0,62
Typ=1 Len=6: 33,b2,e2,ca,d4,34 Typ=1 Len=8: 0,63,6d,4b,8b,d5,0,64

--//b2,e2,ca,d4 = 測試
--//b2,e2,ca,d4 的GBK編碼確實是 "測試"。

3.總結：

--//看來我們以前許多人的理解嚴重錯誤，重複前面的提示：NLS_LANG這個環境變數的作用就是告訴資料庫，你客戶端(sqlplus、plsql
--//dev之類的)所使用的字符集是什麼。這樣我資料庫才能根據客戶端過來的對應字符集編碼根據編碼表轉化為資料庫本身的編碼來儲存。

--//NLS_LANG的設定要與OS字符集保持一致，而不是跟著資料庫裡面的設定!!

--//不過第一次設定看上去感覺非常彆扭，違法以前的認知。我個人建議如果有中文還是儘量減少在服務端操作，在windows客戶端操作
--//完成。我也已經發現生產系統建立包過程一些註解是亂碼，我不知道同事是在windows下還是liunx下完成操作完成，總之同事建立與
--//維護有點太不上心，建立完成就ok了，事後沒有使用工具仔細檢查，註解全是亂碼，如果語句涉及中文就很麻煩了。

--//我總有一種感覺,統一到utf-8也許是大勢所趨,至少許多軟體都有這個的趨勢.我下載最新vim 9.0,kitty,安裝最新的linux釋出都遇
--//到類似的問題.

4.補充我還發現一個細節問題，顯示寬度問題：
--//在windows下執行，NLS_LANG=AMERICAN_AMERICA.ZHS16GBK：
SCOTT@book01p> select dump('彩',16),dump('測試',16) from dual ;
DUMP('彩',16) DUMP('測試',16)
------------------- -------------------------
Typ=96 Len=2: b2,ca Typ=96 Len=4: b2,e2,ca,d4

--//在linux服務端下執行，NLS_LANG=AMERICAN_AMERICA.AL32UTF8：
SCOTT@book01p> select dump('彩',16),dump('測試',16) from dual ;
DUMP('彩',16) DUMP('測試',16)
--------------------------------------------------------- ---------------------------------------------------------------------------
Typ=96 Len=2: b2,ca Typ=96 Len=4: b2,e2,ca,d4
--//linux下寬度增加3倍數，佔用19，顯示寬度57.

SCOTT@book01p> select dump('1',16) from dual ;
DUMP('1',16)
------------------------------------------------
Typ=96 Len=1: 31
--//沒有漢字也是一樣。

--//在linux服務端下執行，NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SCOTT@book01p> select dump('彩',16),dump('測試',16) from dual ;
ERROR:
ORA-01756: quoted string not properly terminated
--//出現問題，如果檢查單引號沒有問題的情況下特別注意字符集相關問題，為什麼出現這樣的問題呢？

SYS@book> select dump('1彩2',16),dump('測試',16) from dual ;
DUMP('1彩2',16) DUMP('測試',16)
---------------------------- -------------------------------
Typ=96 Len=5: 31,e5,bd,a9,32 Typ=96 Len=6: e6,b5,8b,e8,af,95

--//實際上輸入的是UTF8的編碼，不是ZHS16GBK編碼，彩在轉換是e5bda9，這樣在ZHS16GBK出現半個漢字，導致錯誤。
--//在結尾加入一個空格正常。
SCOTT@book01p> select dump('彩 ',16) ,dump('測試',16) from dual ;

DUMP('彩 ',16) DUMP('測試',16)
------------------------- -------------------------------
Typ=96 Len=4: e5,bd,a9,20 Typ=96 Len=6: e6,b5,8b,e8,af,95

--//如果在這樣的情況下linux下插入在windows下的顯示就是出現亂碼。

SCOTT@book01p> insert into t1 values (3,'彩 ','e測試f');
1 row created.

SCOTT@book01p> commit ;
Commit complete.

SCOTT@book01p> select * from t1;
ID VC NVC
---------- -------------------- ---------
1 1? a?
2 3? c?
3 彩 e測試f
--//你以為顯示正常，實際上顯示的是UTF8的編碼。

--//在windows下執行顯示如下：
SCOTT@book01p> select * from t1;
ID VC NVC
---------- -------------------- ----------------------
1 1測試2 a測試b
2 3測試4 c測試d
3 褰? e嫻嬭瘯f

SCOTT@book01p> select id,dump(vc,16) c50,dump(nvc,16) c50 from t1;
ID C50 C50
---------- -------------------------------------------------- --------------------------------------------------
1 Typ=1 Len=6: 31,b2,e2,ca,d4,32 Typ=1 Len=8: 0,61,6d,4b,8b,d5,0,62
2 Typ=1 Len=6: 33,b2,e2,ca,d4,34 Typ=1 Len=8: 0,63,6d,4b,8b,d5,0,64
3 Typ=1 Len=4: e5,bd,a9,20 Typ=1 Len=10: 0,65,5a,34,5b,2d,76,2f,0,66

相關文章