oracle 数据库字符集研究 下篇

简介: 整理自:http://blog.itpub.net/519536/viewspace-615379/自从选用了AL32UTF8字符集做为生产数据库字符集之后,就一直奔走于“乱码”与“转码”之间。

整理自:http://blog.itpub.net/519536/viewspace-615379/

自从选用了AL32UTF8字符集做为生产数据库字符集之后,就一直奔走于“乱码”与“转码”之间。

如果想要搞清楚Oracle的字符系统,需要紧紧地抓住三个因素:
一.“客户终端字符集”
二.“NLS_LANG”环境变量
三.“数据库字符集”

如果“NLS_LANG”等于“数据库字符集”时,不需要进行任何转换,直接把字符插入数据库
如果“NLS_LANG”不等于“数据库字符集”,则需要进行转换,乱码的根源就在这里


1.“数据库字符集”是AL32UTF8,具体信息如下:
SQL> col VALUE for a30
SQL> select * from nls_database_parameters;

PARAMETER       VALUE
------------------------------ ------------------------------
NLS_LANGUAGE       AMERICAN
NLS_TERRITORY       AMERICA
NLS_CURRENCY       $
NLS_ISO_CURRENCY       AMERICA
NLS_NUMERIC_CHARACTERS       .,
NLS_CHARACTERSET       AL32UTF8
NLS_CALENDAR       GREGORIAN
NLS_DATE_FORMAT       DD-MON-RR
NLS_DATE_LANGUAGE       AMERICAN
NLS_SORT       BINARY
NLS_TIME_FORMAT       HH.MI.SSXFF AM


PARAMETER       VALUE
------------------------------ ------------------------------
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.3.0

20 rows selected.

2.“客户终端字符集”信息如下:
以下实验使用了两种主要客户端(不包括后面提到的PL/SQL Developer和Toad):一个是XP的cmd命令行工具,另一个是PuTTY工具。
1)XP字符集是
C:\>chcp
Active code page: 936
代码页936就是中文字符集GBK,可以参考msdn的资料《Windows Codepage 936》
http://www.microsoft.com/globaldev/reference/dbcs/936.htm

2)PuTTY字符集我的设置:utf8

3.客户端使用AL32UTF8字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8  Linux环境(export NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
[oracle@lyg ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@lyg ~]$ echo $LANG
en_US.UTF-8
[oracle@lyg ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:11:16 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>  create table t (x varchar2(20), y varchar2(20));
Table created.
SQL> insert into t values ('圣','AL32UTF8');
1 row created.
SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;

X   Y DUMP(X)
---------- -------------------- ------------------------------
圣         AL32UTF8             Typ=1 Len=3: 229,156,163


4.客户端使用WE8ISO8859P1字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1    Linux环境(export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@lyg ~]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@lyg ~]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1
[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:15:48 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into t values ('圣','WE8ISO8859P1');
1 row created.
SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;
X   Y DUMP(X)
---------- -------------------- ------------------------------
¿   AL32UTF8 Typ=1 Len=3: 229,156,163
  WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163

5.客户端使用ZHS16GBK字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK     Linux环境(export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@lyg ~]$ echo $LANG
en_US.UTF-8
[oracle@lyg ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:21:02 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> insert into t values ('圣','ZHS16GBK');
ERROR:
ORA-01756: quoted string not properly terminated       ——11.2.0.3的环境,插不进去,网上查了下,应该是字符集设置的问题。

SQL> insert into t values ('A','ZHS16GBK');        ——尝试插了个A,是可以的。
1 row created.

又尝试插入了一个汉字“一”,倒是可以,但是插入“二”就又不行了。

SQL> select x, y, dump(x) from t;

X   Y DUMP(X)
---------- -------------------- ------------------------------
ʥ   AL32UTF8 Typ=1 Len=3: 229,156,163
£¿£¿¡   WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK Typ=1 Len=1: 65
  ZHS16GBK Typ=1 Len=6: 230,182,147,226,130,172

下面这是原作者10g中的测试:
sec@ora10g> insert into t values ('圣','ZHS16GBK');
1 row created.
sec@ora10g> commit;
Commit complete.

sec@ora10g> col x for a10
sec@ora10g> col dump(x) for a30
sec@ora10g> select x, y, dump(x) from t;
X          Y                    DUMP(X)
---------- -------------------- ------------------------------
?         AL32UTF8             Typ=1 Len=2: 202,165
ꥠ       WE8ISO8859P1         Typ=1 Len=4: 195,138,194,165
圣         ZHS16GBK             Typ=1 Len=3: 229,156,163


6.我把xshell设置长GB2312/16GB分别以ssh方式连接数据库服务器进行测试

SQL> insert into t values ('圣','xshgb2312 AL32UTF8');
1 row created.

SQL> commit;
Commit complete.

SQL> select x, y, dump(x) from t;
X   Y DUMP(X)
---------- -------------------- ------------------------------
?   AL32UTF8 Typ=1 Len=3: 229,156,163
??#   WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK Typ=1 Len=1: 65
??   ZHS16GBK Typ=1 Len=6: 230,182,147,226,130,172
??   xshgb2312 AL32UTF8 Typ=1 Len=6: 239,191,189,239,191,189

接下来,我再换成16GBK试试:


SQL> insert into t values ('圣','xsh GBK');
1 row created.


SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;

X   Y                                 DUMP(X)
---------- --------------------                ------------------------------
?   AL32UTF8                  Typ=1 Len=3: 229,156,163
??#   WE8ISO8859P1          Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK           Typ=1 Len=1: 65
??   ZHS16GBK           Typ=1 Len=6: 230,182,147,226,130,172
??   xshgb2312 AL32UTF8  Typ=1 Len=6: 239,191,189,239,191,189
??   xsh GBK Typ=1      Len=6: 239,191,189,239,191,189

6 rows selected.



7.最后我们将NLS_LANG置空进行一下最后的尝试
C:\>set NLS_LANG=        Linux环境(export NLS_LANG= 
 [oracle@lyg ~]$ export NLS_LANG= 
[oracle@lyg ~]$ echo $NLS_LANG
[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:52:00 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> insert into t values ('圣','unset NLS_LANG');
1 row created.

SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;
X   Y DUMP(X)
---------- -------------------- ------------------------------
?   AL32UTF8 Typ=1 Len=3: 229,156,163
??#   WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK Typ=1 Len=1: 65
??   ZHS16GBK Typ=1 Len=6: 230,182,147,226,130,172
??   xshgb2312 AL32UTF8 Typ=1 Len=6: 239,191,189,239,191,189
??   xsh GBK Typ=1 Len=6: 239,191,189,239,191,189
???   unset NLS_LANG Typ=1 Len=9: 239,191,189,239,191,189,239,191,189

7 rows selected.



9.Toad中“F9”的执行效果
X          Y                    DUMP(X)
---------- -------------------- ------------------------------
圣         AL32UTF8             Typ=1 Len=2: 202,165
脢楼         WE8ISO8859P1         Typ=1 Len=4: 195,138,194,165
鍦         ZHS16GBK             Typ=1 Len=3: 229,156,163
鍦         PuTTY AL32UTF8       Typ=1 Len=3: 229,156,163
圣         unset NLS_LANG       Typ=1 Len=2: 202,165

10.PL/SQL Developer中“Command Window”执行效果(我plsql dev设置的字符集是16GBK的(因为我的windows系统环境变量的设置))


11.PL/SQL Developer中“F8”执行效果(我plsql dev设置的字符集是16GBK的(因为我的windows系统环境变量的设置))



12.实验结论
1)如果有可能,尽量保证客户端编码(Windows XP的cmd工具可以使用chcp命令来确认)、NLS_LANG参数和数据库字符集这三个内容一致,这样设置,无论是从性能上,还是从防止编码转换上都是最佳的;
2)如果目的是支持中文,数据库Server端的字符集应该尽量选择ZHS16GBK或AL32UTF8字符集,这样可以减少因不当的“转码”导致的字符乱码故障;
3)(推荐)可已将NLS_LANG参数与操作终端字符编码一致,这样可以保证数据库能正确获得应用终端使用的编码,这时会发生“编码转换”,但是,这样就可以保证正确转码,可以防止错误的编码存入数据库;
4)(不推荐)也可以将NLS_LANG参数与数据库服务器端的编码一致,这样,客户端无论是发送到服务器端还是从服务器接收数据都不会“转码”,这样能保证客户端对字符的显示效果,但是,一定要小心,这时数据库服务器上存放的字符编码很可能是错误的。
5)PL/SQL Developer工具在AL32UTF8字符集下貌似可以保证数据效果,但是“Toad同学”貌似不太“稳定”。



相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
4天前
|
存储 自然语言处理 关系型数据库
|
3天前
|
Oracle Java 关系型数据库
[学习笔记] 在Eclipse中使用Hibernate,并创建第一个Demo工程,数据库为Oracle XE
[学习笔记] 在Eclipse中使用Hibernate,并创建第一个Demo工程,数据库为Oracle XE
15 0
|
4天前
|
Oracle 关系型数据库 数据库
Oracle 11G数据库安装步骤及截图操作2
Oracle 11G数据库安装步骤及截图操作
8 0
|
4天前
|
Oracle 安全 关系型数据库
Oracle 11G数据库安装步骤及截图操作1
Oracle 11G数据库安装步骤及截图操作
15 0
|
8天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之在进行Oracle数据库的全量同步时,只同步了一条数据而源表实际上包含多条数据,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
存储 关系型数据库 MySQL
|
2天前
|
SQL 关系型数据库 MySQL
|
2天前
|
存储 关系型数据库 MySQL
|
3天前
|
存储 关系型数据库 MySQL
关系型数据库mysql日志和临时文件
【6月更文挑战第15天】
22 4
|
3天前
|
存储 关系型数据库 MySQL
关系型数据库mysql数据文件存储
【6月更文挑战第15天】
10 4

热门文章

最新文章

推荐镜像

更多