Oracle字符集的简单图解,中文乱码解决
来源:互联网 发布:php 给字符串加密解密 编辑:程序博客网 时间:2024/06/02 14:30
经常碰到SQLPLUS展现乱码的问题,字符集和相关的定义都有说明但是很少有能把这些关系说的很简单易懂的。
在此之前我们需要搞清楚三个概念,操作系统字符集,客户端字符集,Oracle字符集:
操作系统字符集:对应的参数是LANG,这个参数应该是Oracle数据库的超集,如果操作系统不支持,那么我们的数据就会乱码。这里的操作系统指的是客户端的操作系统。服务器端的操作系统不会影响数据的存取。
数据库字符集:NLS_CHARACTERSET,可以在nls_database_parameters中查看当前数据库的字符集,安装数据库的时候选择,一般不修改,不过在新的字符集是现有字符集的严格超集的情况下可以改,其他情况下修改可能导致数据库异常。例如将UTF8字符集修改为AL32UTF8
关于子集超集的映射关系,见如下Oracle官网的文档的Binary Subset-Superset Pairs。
http://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG591
客户端字符集:对应的参数是NLS_LANG,如果客户端未设置,此时则取的是安装时数据库的默认参数
为了帮助理解,我画了一张图如下,图中标红部分如果一致表示数据的存储方式一致,即如果LANG、NLS_LANG、NLS_CHARACTERSET的编码是一致的如UTF8,那么数据的传输过程中不会异常,字符乱码只是显示问题。
1、操作系统字符集
linux下首先locale 查看字符集
[oracle@oddpc ~]$ localeLANG=en_US.UTF-8LC_CTYPE="en_US.UTF-8"LC_NUMERIC="en_US.UTF-8"LC_TIME="en_US.UTF-8"LC_COLLATE="en_US.UTF-8"LC_MONETARY="en_US.UTF-8"LC_MESSAGES="en_US.UTF-8"LC_PAPER="en_US.UTF-8"LC_NAME="en_US.UTF-8"LC_ADDRESS="en_US.UTF-8"LC_TELEPHONE="en_US.UTF-8"LC_MEASUREMENT="en_US.UTF-8"LC_IDENTIFICATION="en_US.UTF-8"LC_ALL=[oracle@oddpc ~]$ echo $LANGen_US.UTF-8
2、该主机并未安装中文支持包,设置LANG后可以效果如下,显然无路如何调整NLS_LANG在这台机器上都无法展现中文
[oracle@evenpc ~]$ export LANG=zh_CN.utf8[oracle@evenpc ~]$ date2016? 10? 13? ??? 15:17:01 CST
3、安装中文支持包,使用yum -y groupinstall chinese-support 可以安装中文支持包,安装过程略过,安装完毕后可以正常显示中文
[oracle@oddpc ~]$ export LANG=zh_CN.utf8[oracle@oddpc ~]$ date2016年 10月 13日 星期四 15:14:19 CST
4、接下来就是展现测试,我安装了两个数据库实例PROD1和PROD5,PROD1 的字符集是WE8MSWIN1252,PROD5的字符集是AL32UTF8
默认情况下NLS_LANG是空的,此时NLS_LANG取默认安装时的值,PROD1是AMRICAN,PROD5是SIMPLIFIED CHINESE
[oracle@oddpc ~]$ echo $NLS_LANG[oracle@oddpc ~]$
SQL> show parameter lang NAME TYPE VALUE------------------------------------ ----------- ------------------------------nls_date_language stringnls_language string AMERICAN
SQL> select sysdate from dual;SYSDATE---------13-OCT-16PROD5
SQL> show parameter lang NAME TYPE VALUE------------------------------------ ----------- ------------------------------nls_date_language stringnls_language string SIMPLIFIED CHINESE
SQL> select sysdate from dual;SYSDATE----------13-10?-165、PROD5 发生乱码,PROD1英文正常,设置下NLS_LANG参数
PROD1 的结果如下,可以看到提示信息已经变成中文,但是由于字符集非UTF8中文字符存入后将乱码
[oracle@oddpc ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.UTF8"[oracle@oddpc ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on 星期四 10月 13 15:42:46 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter langNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------nls_date_language string SIMPLIFIED CHINESEnls_language string SIMPLIFIED CHINESESQL> show parameter db_nameNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------db_name string PROD1SQL> show parameter langNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------nls_date_language string SIMPLIFIED CHINESEnls_language string SIMPLIFIED CHINESESQL> select sysdate from dual;SYSDATE------------13-10? -16
SQL> select * from nls_database_parameters;PARAMETER VALUE---------------------------------------- ----------------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET WE8MSWIN1252NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMPARAMETER VALUE---------------------------------------- ----------------------------------------NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSENLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION 11.2.0.3.0已选择20行。
[oracle@oddpc ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.UTF8"[oracle@oddpc ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on 星期四 10月 13 15:46:36 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter db_nameNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------db_name string PROD5SQL> select sysdate from dual;SYSDATE------------13-10月-16SQL> show parameter langNAME TYPE VALUE------------------------------------ --------------------------------- ------------------------------nls_date_language string SIMPLIFIED CHINESEnls_language string SIMPLIFIED CHINESE<pre name="code" class="sql">SQL> select * from nls_database_parameters;PARAMETER VALUE---------------------------------------- ----------------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET AL32UTF8NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMPARAMETER VALUE---------------------------------------- ----------------------------------------NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSENLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION 11.2.0.3.0已选择20行。
总结:通过以上的实验可以看出,客户端展现是否乱码是由NLS_LANG决定,发生中文乱码的情况下,首先查看数据库的NLS_CHARACTERSET是否支持中文存储,如果不支持,无论如何设置均无法正常显示中文。Oracle官方文档上给出了各种语言的编码支持如下。
http://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG593
Table A-13 Languages and Character Sets Supported by LCSSCAN and GDK
Arabic
AL16UTF16, AL32UTF8, AR8ISO8859P6, AR8MSWIN1256, UTF8
Bulgarian
AL16UTF16, AL32UTF8, CL8ISO8859P5, CL8MSWIN1251, UTF8
Catalan
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Croatian
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Czech
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Danish
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Dutch
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
English
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Estonian
AL16UTF16, AL32UTF8, NEE8IOS8859P4, UTF8
Finnish
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
French
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
German
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Greek
AL16UTF16, AL32UTF8, EL8ISO8859P7, EL8MSWIN1253, UTF8
Hebrew
AL16UTF16, AL32UTF8, IW8ISO8859P8, IW8MSWIN1255, UTF8
Hindi
AL16UTF16, AL32UTF8, IN8ISCII, UTF8
Hungarian
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Indonesian
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Italian
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Japanese
AL16UTF16, AL32UTF8, ISO2022-JP, JA16EUC, JA16SJIS, UTF8
Korean
AL16UTF16, AL32UTF8, ISO2022-KR, KO16KSC5601, KO16MSWIN949, UTF8
Latvian
AL16UTF16, AL32UTF8, NEE8ISO8859P4, UTF8
Lithuanian
AL16UTF16, AL32UTF8, NEE8ISO8859P4, UTF8
Malay
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Norwegian
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Persian
AL16UTF16, AL32UTF8, AR8MSWIN1256, UTF8
Polish
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Portuguese
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Romanian
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Russian
AL16UTF16, AL32UTF8, CL8ISO8859P5, CL8KOI8R, CL8MSWIN1251, RU8PC866, UTF8
Serbian
AL16UTF16, AL32UTF8, CL8ISO8859P5, CL8MSWIN1251, UTF8
Simplified Chinese
AL16UTF16, AL32UTF8, HZ-GB-2312, UTF8, ZHS16GBK, ZHS16CGB231280
Slovak
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Slovenian
AL16UTF16, AL32UTF8, EE8ISO8859P2, EE8MSWIN1250, UTF8
Spanish
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Swedish
AL16UTF16, AL32UTF8, US7ASCII, UTF8, WE8ISO8859P1, WE8ISO8859P15, WE8MSWIN1252
Thai
AL16UTF16, AL32UTF8, TH8TISASCII, UTF8
Traditional Chinese
AL16UTF16, AL32UTF8, UTF8, ZHT16MSWIN950
Turkish
AL16UTF16, AL32UTF8, TR8MSWIN1254, UTF8, WE8ISO8859P9
Ukranian
AL16UTF16, AL32UTF8, CL8ISO8859P5, CL8MSWIN1251, UTF8
Vietnamese
AL16UTF16, AL32UTF8, VN8VN3, UTF8
- Oracle字符集的简单图解,中文乱码解决
- ORACLE 服务端与客户端字符集的匹配,解决中文乱码
- oracle中文字符集乱码
- tomcat字符集与中文乱码的解决
- tomcat字符集与中文乱码的解决
- tomcat字符集与中文乱码的解决
- oracle 字符集乱码解决
- 解决Oracle数据库里的中文数据显示乱码的问题,修改数据库的字符集。
- 解决ORACLE字符集为US7ASCII,插入数据中文乱码问题
- oracle小记之修改字符集解决中文乱码
- 修改ORACLE客户端字符集编码解决中文乱码问题
- mysql_query设置字符集为utf8,解决中文乱码的问题
- mysql修改字符集解决中文乱码的方法
- 完整的解决oracle乱码shell处理(当每日导入数据库是英文字符集但需要导入中文字符集并且显示中文)
- Oracle 12c中文乱码,修改字符集的方法
- 解决Oracle 中文乱码
- 解决Oracle 中文乱码
- 解决Oracle 中文乱码
- STM32 systick 定时 时间计算
- MPAndroidchart
- 【CodeForces 534A】Exam 【数学构造】
- SysTick_CLKSourceConfig 这个函数还有用吗?
- (七)Servlet就是这样-Servlet路径专题
- Oracle字符集的简单图解,中文乱码解决
- JDK Logger 简介
- Python __slots__ 作用
- myeclipse 怎么查看哪个类实现了已知接口
- 拉格朗日对偶性问题-《统计学习方法》学习笔记
- nginx location配置及解析过程
- 哑变量或虚拟变量介绍(dummyvar)
- Linux下查看文件和文件夹大小
- 通信建立的基础——耳机线上传输的信号