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

[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"