问题是这样的
server端字符集设置如下
SQL> select * from v$nls_parameters where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL>
实验如下:
1、nls_lang设置为"SIMPLIFIED CHINESE_CHINA".ZHS16GBK
[oracle@oradb1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
[oracle@oradb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 25 15:25:05 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into magy.t2 values(1,'CHINESE_CHINA','ZHS16GBK','连接到',SYSDATE);
已创建 1 行。
SQL> commit;
提交完成。
SQL> col b for a20
SQL> col c for a20
SQL> col d for a20
SQL> col e for a20
SQL> set linesize 100
SQL> select * from magy.t2;
A B C D E
---------- -------------------- -------------------- -------------------- --------------------
1 CHINESE_CHINA ZHS16GBK 连接到 25-FEB-14
SQL>
**********************************************************************
2、nls_lang设置为american_america.al32utf8
[oracle@oradb1 ~]$ export NLS_LANG=american_america.al32utf8
[oracle@oradb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 25 15:28:45 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into magy.t2 values(1,'american_america','al32utf8','连接到',SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> col b for a20
SQL> col c for a20
SQL> col d for a20
SQL> col e for a20
SQL> set linesize 100
SQL> select * from magy.t2;
A B C D E
---------- -------------------- -------------------- -------------------- --------------------
1 CHINESE_CHINA ZHS16GBK 杩炴帴鍒 25-FEB-14
1 american_america al32utf8 连接到 25-FEB-14
SQL>
可见,客户端字符集设置为american_america.al32utf8时,之前zhs16gbk的汉字显示为乱码
***********************************************************************
3、nls_lang再改为"SIMPLIFIED CHINESE_CHINA".ZHS16GBK
[oracle@oradb1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
[oracle@oradb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 25 16:55:29 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col b for a20
SQL> col c for a20
SQL> col d for a20
SQL> col e for a20
SQL> set linesize 100
SQL> select * from magy.t2;
A B C D E
---------- -------------------- -------------------- -------------------- --------------------
1 CHINESE_CHINA ZHS16GBK 连接到 25-2月 -14
1 american_america al32utf8 ????? 25-2月 -14
SQL>
可见,客户端字符集设置为ZHS16GBK时,al32utf8插入的汉字为乱码,
那么,目前,由此得到结论:
1、设定一个客户端字符集,无论插入和查询,都可以正确进行,虽然server端为另外一个字符集,可是不影响客户端的显示
2、在插入数据和查询数据的间隔期间,客户端字符集发生变化时,查询出来的汉字结果会显示乱码
得出的疑问如下
server端字符集的设置到底有什么用?我只要在客户端设置了正确的字符集,server也只是起到了一个保存二进制代码的作用?
但是,另外一个实验,将客户端字符集设置为utf8,又颠覆了我的结论,如下:
[oracle@oradb1 ~]$ export NLS_LANG=american_america.utf8
[oracle@oradb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 25 15:34:32 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into magy.t2 values(1,'american_america','utf8','连接到',SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> col b for a20
SQL> col c for a20
SQL> col d for a20
SQL> col e for a20
SQL> set linesize 100
SQL> select * from magy.t2;
A B C D E
---------- -------------------- -------------------- -------------------- --------------------
1 CHINESE_CHINA ZHS16GBK 杩炴帴鍒 25-FEB-14
1 american_america al32utf8 锟斤拷锟接碉拷 25-FEB-14
1 american_america utf8 锟斤拷锟接碉拷 25-FEB-14
SQL>
此处可见查询出来的结果都为乱码,请教哪位大侠帮忙给看看,这是怎么回事呢?多谢了