[20240820]字符集与dml语句.txt

--//测试看看不同环境不同字符集插入数据到表中,会出现怎么情况.

1.环境:
SCOTT@book01p> @ pr
==============================
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，这种情况几乎是前几年大部分数据库的建立
--//模式,至少我们团队建立的数据库基本都是这样.

2.测试:
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.

--//在windows客户端插入:
SCOTT@book01p> insert into t1 values (2,'3测试4','c测试d');
1 row created.

SCOTT@book01p> commit ;
Commit complete.

--//显示看看:
--//在linux服务端执行:
SCOTT@book01p> select * from t1;
        ID VC                   NVC
---------- -------------------- -------------
         2 3?               c?
         1 1测试2             a测试b         --//linux下插入的正常显示

--//在windows客户端执行:
SCOTT@book01p> select * from t1;
        ID VC                   NVC
---------- -------------------- ------------
         2 3测试4               c测试d     --//windows下插入的正常显示
         1 1娴嬭瘯2             a娴嬭瘯b

--//各个环境仅仅显示DML的结果是正确的,而实际上对于当前的环境在服务端操作插入是错误的.特别注意!!

3.继续:
--//重新登录,选择LANG=en_US,kitty Remote character set 选择 Use font encoding.
--//安全期间我修改/etc/locale.conf文件:
# cat locale.conf
#LANG="en_US.UTF-8"
LANG="en_US"

--//再重新登录.

$ echo $LANG
en_US

$ echo 张|xxd -c 16
0000000: d5c5 0a                                  ...

--//在linux服务端插入:
SCOTT@book01p> insert into t1 values (3,'5测试6','e测试f');
1 row created.

SCOTT@book01p> commit;
Commit complete.
--//注:我的测试本来是在winodws下使用vim事先写好语句,但是无法实现copy and paste.只能手工在linux下输入.
--//也许我的kitty版本太新的缘故.

--//再次显示看看:
--//在linux服务端执行:
SCOTT@book01p> select * from t1 order by 1;
        ID VC                   NVC
---------- -------------------- ---------
         1 1娴嬭瘯2             a娴嬭瘯b
         2 3测试4               c测试d
         3 5测试6               e测试f    --//这次linux的插入正确.

SCOTT@book01p> select dump(vc,16) c40 ,dump(nvc,16) c50,t1.* from t1 order by id;
C40                                      C50                                                        ID VC                   NVC        
---------------------------------------- -------------------------------------------------- ---------- -------------------- -----------
Typ=1 Len=8: 31,e6,b5,8b,e8,af,95,32     Typ=1 Len=10: 0,61,5a,34,5b,2d,76,2f,0,62                   1 1娴嬭瘯2             a娴嬭瘯b   
Typ=1 Len=6: 33,b2,e2,ca,d4,34           Typ=1 Len=8: 0,63,6d,4b,8b,d5,0,64                          2 3测试4               c测试d     
Typ=1 Len=6: 35,b2,e2,ca,d4,36           Typ=1 Len=8: 0,65,6d,4b,8b,d5,0,66                          3 5测试6               e测试f    --//这次linux的插入正确.

--//在windows客户端执行:
SCOTT@book01p> select * from t1 order by 1;
        ID VC                   NVC
---------- -------------------- --------------------
         1 1娴嬭瘯2             a娴嬭瘯b
         2 3测试4               c测试d
         3 5测试6               e测试f

4.总结：
--//如果你安装linux使用LANG="en_US.UTF-8",putty或者kitty设置Remote character set 选择 utf-8时,注意尽量不要在服务端执行
--//涉及中文字符的dml操作,否则可能导致乱码.
--//而且以前建立的数据库选择基本都是characterSet ZHS16GBK -nationalCharacterSet AL16UTF16

--//而且我目前总有一种感觉,统一到utf-8也许是大势所趋,至少许多软件都有这个的趋势.我下载最新vim 9.0,kitty,安装最新的linux
--//发布都遇到类似的问题.

# Silent mode.
dbca -silent -createDatabase                                                   \
     -templateName General_Purpose.dbc                                         \
     -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} -responseFile NO_VALUE         \
     -characterSet AL32UTF8                                                    \
     -sysPassword SysPassword1                                                 \
     -systemPassword SysPassword1                                              \
     -createAsContainerDatabase true                                           \
     -numberOfPDBs 1                                                           \
     -pdbName ${PDB_NAME}                                                      \
     -pdbAdminPassword PdbPassword1                                            \
     -databaseType MULTIPURPOSE                                                \
     -memoryMgmtType auto_sga                                                  \
     -totalMemory 2000                                                         \
     -storageType FS                                                           \
     -datafileDestination "${DATA_DIR}"                                        \
     -redoLogFileSize 50                                                       \
     -emConfiguration NONE                                                     \
     -ignorePreReqs
--//不知道是否有人测试过-characterSet AL32UTF8的情况.

5.收尾:
--//修改/etc/locale.conf文件:
# cat locale.conf
LANG="en_US.UTF-8"