Oracle-OCP学习笔记:字符集

linux 环境下字符集：

[root@single ~]# locale

LANG=en_US.UTF-8

LC_CTYPE="en_US.UTF-8"

#export NLS_LANG=american_america.zhs16gbk

windows 环境下字符集：

C:\Users\Martin>chcp

活动代码页: 936

C:>set NLS_LANG=american_america.zhs16gbk

字符集分类：

操作系统字符集

Oracle 软件字符集

Sql developer 软件带字符集，如果软件没有字符集，则使用操作系统字符集

SYS AS SYSDBA@ORCL>desc t2;

Name                                       Null?   Type

ID                                           NUMBER(38)

NAME                                           VARCHAR2(20)

ID 例是数字列，不需要使用字符集

NAME 列是VARCHAR2表，需要使用字符集来转换。

字符集的作用和含义：

字符编码

使用场合

数据库字符集, 以下类型要使用字符集。

1.  用来存储CHAR,VARCHAR2,CLOB,LONG等类型数据

2.  用来标示诸如表名，列名以及PL/SQL变量等

3.  用来存储 SQL 和PL/SQL程序单元等

国家字符集：

用来存储NCHAR,NVARCHAR2,NCLOB等类型数据,如果没有这些类型的数据，则不需要使用国家字符集。

SYS ASSYSDBA@ORCL>desc t3;

Name                                       Null?   Type

-----------------------------------------------------------------------------------

ID                                          NUMBER

NAME                                       NVARCHAR2(20)

客户端OS字符集，NLS_LANG设置，服务端OS字符集，ORACLE数据库字符集

字符集之间的关系

正确设置字符集

字符集出现问题以后的判断流程

存储的是错误的字符集

存储是的正确的字符集

Locale  locale –a,chcp

字符集其实就是“字符”和“编码”的一张对照表

字符集原理：

字符集就是一个表，有两列，

左边的列是要存储的字符，比如中文字符”阮胜昌”

右边的列是一个编码，也就是计算机里面存储的数字。因为计算机里面只能存储数字，不能存储字符。

所以字符集是我们所能看到的字符与字符对应的编码的对应表。

字符集命名：

ORACLE 的字符集命令遵循以下命名规则：

<Language><bit size><encoding>

即：<语言><比特位数><编码>

比如：ZHS16GBK表示采用GBK编码格式，16位（两个字节），简体中文字符集

US7ASCII  # 只能存储美国人使用的字符，26个字母,数字，运算符号(+-*/),总量不超过128个字符

ZHS16CGB231280  # 中文字符集，是国标，比较老了

ZHS16GBK   # 最新的中文字符集,是字符集Zhs16CGB231280的超集,不是严格超集

AF16UTF16# 国家字符集

unicode 字符集：

AL32UTF8   # 最新的UTF8字符集(国际型企业，即有中文字符，也有其它国家的字符)

UTF8       # 比较老，字符集不全面

注：实际生产环境，如果确定数据库只是中国人用（只有中英文相关字符）的话，就采用ZHS16GBK..

数据库安装过程中，会选择确认下面两个字符集：

1. 数据库字符集：数据库需要存储多国语言选择AL32UTF8，只有中英文选择ZHS16GBK

2. 国家字符集 一般都选用AL16UTF16

NLS_LANG=<language>_<territory>.<clientcharacter set>

Language: 显示oracle消息，校验，日期命名,是用中文显示还是英文显示

Territory: 指定默认日期，数字，货币等格式

Clientcharacter set: 指定客户端将使用的字符集

例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII

AMERICAN ：是语言，AMERICA是地区，US7ASCII是客户端字符集

Oracle 提供若干NLS参数定制数据库和用户以适应本地格式，例如有NLS_LANGUAGE,NLS_DATE_FORMAT,NLS_CALENDER等，可以通过查询以下数字字典或v$视图查看。

NLS_DATABASE_PARAMETERS – 显示数据库当前NLS参数取值，包括数据库字符集取值

NLS_SESSION_PARAMETERS   - 显示由NLS_LANG设置的参数，或经过alter session改变后的参数值(不包括由NLS_LANG设置的客户端字符集)

NLS_INSTANCE_PARAMETE    - 显示由参数文件init<SID>.ora定义的参数

V$NLS_PARAMETERS         - 显示数据库当前NLS参数取值。

查看数据库字符集：用得多

SYS AS SYSDBA@ORCL>select * fromnls_database_parameters where PARAMETER='NLS_CHARACTERSET';

PARAMETER           VALUE

NLS_CHARACTERSET   AL32UTF8

查看国家字符集：用得少，做为数据库字符集的补充

SYS AS SYSDBA@ORCL>select * fromnls_database_parameters where PARAMETER='NLS_NCHAR_CHARACTERSET';

PARAMETER                   VALUE

NLS_NCHAR_CHARACTERSET      UTF8

查看当前session使用的字符集：

SYS AS SYSDBA@ORCL>select * from nls_session_parameters;

SYS AS SYSDBA@ORCL>select userenv('language') fromdual;

USERENV('LANGUAGE')

-------------------------------------------------

SIMPLIFIED CHINESE_CHINA.AL32UTF8

SYS AS SYSDBA@ORCL>select nls_charset_name(to_number('0354','xxxx'))from dual;

NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX'))

----------------------------------------------

ZHS16GBK

SYS AS SYSDBA@ORCL>! echo $NLS_LANG

SIMPLIFIED CHINESE_CHINA.UTF8

SYS AS SYSDBA@ORCL>select to_char(nls_charset_id('ZHS16GBK'),'xxxx')from dual;

TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'),'XXXX')

------------------------------------------------------------------------------------------------------------------------------------------------------

354

查看系统支持的字符集：

SYS AS SYSDBA@ORCL>select * from V$NLS_VALID_VALUES;

sql*plus 客户端 （ 设置原则：与所在操作系统字符集一致 ）:

sqlplus 没有字符集，他引用操作系统字符集

sqldeveloper 这个软件有字符集，所有不用操作系统字符集

oracle 有字符集，所有oracle不用操作系统字符集

总结 ：如果oracle client如果有字符集，以软件字符集为主，不会用操作系统的字符集,所有字符集的转换都在oracle内部完成的。

客户端字符集配置：(通过配置客户端字符集，oracle就知道客户端用什么字符集编码。)

windows ：一般都是gbk，即chcp结果为936

c:>set NLS_LANG=american_america.zhs16gbk

语言和地区为中文中国的话，set NLS_LANG=simplifiedchinese_china.zhs16gbk

linux ：一般为utf-8，即echo $LANG 结果为en_US.UTF-8 或zh_CN.UTF-8

#export NLS_LANG=american_america.utf8

以下是错误的字符集配置：

原则：客户端的NLS_LANG配置的字符集要与客户端软件或客户端操作系统的字符集要一样

ORACLE 不管是存数据还是取数据，都要向客户端询问，对方传过来的编码是通过什么字符集转换的。

如果数据库字符集与客户端的字符集一样，ORACLE不会发生字符集转换，

如果数据库字符集与客户端的字符集不一样，ORACLE会发生这符集转换。

以下图的客户端字符集与NLS_LANG字符集不一样，这是错误的，正常的配置是NLS_LANG字符集要配置成与客户端服务器或客户端软件的字符集一样。

上图错误的原因：

客户端在 varchar2 等类型的列上存一个字符，这时在客户端会将字符转换成编码 A4 。这时将这个 A4 发送到 ORACLE 服务端，这时 ORACLE 就会问，这个 A4 编码中用什么字符集编码的，因为这时客户端的 NLS_LANG 字符集为 WE8MSWIN1252 ，所以 oracle 就认为客户端的字符集为 WE8MSWIN1252, 而不是实际的 WE8ISO8859P15 。这时 oracle 看了一下数据库的字符集是 AL32UTF8, 与客户端的字符集 WE8MSWIN1252, 所有 ORACLE 会先将 A4 这个编码用 WE8MSWIN1252 字符集转换成 A4 对应的字符。再将字符通过 AL32UTF8 转换成编码 C2A4 ， ORACLE 就直接存这个转换好的编码 C2A4 。

当客户端读取数据库的字符时， ORACLE 返回以 AL32UTF8 字符集对应的编码 C2A4,ORACLE 查看到 NLS_LANG 的字符集为 WE8MSWIN1252, 与数据库的字符集不一样。所以 ORACLE 会将 C2A4 先通过 AL32UTF8 转换为字符，再用这个字符通过 WE8MSWIN1252 转换成编码。这时客户端看到的就是这个编码，但这时通过 WE8ISO8859P15 字符集解析这个编码时得不到原来的字符，这时的字符就产生的乱码。

下图的 NLS_LANG 字符集与数据库的字符集一样，与客户端的字符集不一样。

上图错误的原因：

客户端将字符编码后得到 A4 编码，这时客户端 NLS_LANG 配置为 UTF8, 而数据库的字符集也是 UTF8, 所以当将这个编码传到数据库时， ORACLE 看到客户端的 NLS_LANG 的字符集与数据库的字符集一样，所有 oracle 不会转换，而是直接将编码 A4 存入数据库。

测试：

客户端Linux，系统字符集为zh_CN.UTF-8

正确设置：

#export LANG=zh_CN.UTF-8

#export NLS_LANG=american_america.utf8

[oracle@oracle ~]$ echo $NLS_LANG

american_america.utf8

[oracle@oracle ~]$ echo $LANG

zh_CN.UTF-8

SYS AS SYSDBA@ORCL>select * from t3;

ID NAME

---------- --------------------

1 阮胜昌

commit;

查看字符对应的编码：这个编码就是oracle存储下来的。

SYS AS SYSDBA@ORCL>select id,name,dump(name,1016) fromt3;

ID NAME

---------- --------------------

DUMP(NAME,1016)

------------------------------------------------------------------------------------------------------------------------------------------------------

1 阮胜昌

Typ=1 Len=9 CharacterSet=UTF8: e9,98,ae,e8,83,9c,e6,98,8c  # 以16进制显示

select id,name,dump(name,1010) from t3;  # 以10进制显示

Typ=1 Len=9 CharacterSet=UTF8:233,152,174,232,131,156,230,152,140

select id,name,dump(name,1008) from t3; # 以8进制显示

Typ=1 Len=9 CharacterSet=UTF8:351,230,256,350,203,234,346,230,214

错误配置1：

[oracle@oracle ~]$ echo $LANG

en_US.UTF-8

[oracle@oracle ~]$ echo $NLS_LANG

SIMPLIFIED CHINESE_CHINA.UTF8

SYS AS SYSDBA@ORCL>select id,name from t3;

ID NAME

---------- --------------------

1  껨 莜

错误配置2：

#export NLS_LANG=american_america.zhs16gbk

select dump(' 靖宇',1016) from dual;

insert into t2 values (2,' 靖宇');

commit;

注意：字符集设置错误导致库中存储的就是错误的编码，这个几乎是不可逆的`一定要慎重仔细设置好字符集。

查看字符：阮胜昌字符正确的编码:

SYS AS SYSDBA@ORCL>select dump(' 阮胜昌',1016) from dual;

DUMP(' 阮胜昌',1016)

Typ=96 Len=9 CharacterSet=AL32UTF8:e9,98,ae,e8,83,9c,e6,98,8c

insert into t2 values (2,' 阮胜昌');

commit;

服务器上oracle字符集

如何确定当前字符集设定，何时可以更改字符集。

SYS AS SYSDBA@ORCL>select userenv('language') fromdual;

USERENV('LANGUAGE')

AMERICAN_AMERICA.AL32UTF8

查看系统的字符集：

select * from v$nls_parameters;

了解超集，严格超集的概念，确定是否可以更改字符集。Oracle不建议后期更改数据库字符集！

服务器操作系统字符集（暂时忽略）

ps:

1. 因为sql*plus 本身没有字符集，依赖于所在操作系统的字符集，而在远端 linux 上的oracle也是无法直接访问到windows上的字符集，所以依靠sql*plus的NLS_LANG设置来辨别。

2. 因为oracle软件本身有字符集，当软件有自己的字符集时，就不用操作系统的字符集，所以服务器操作系统字符集的因素可以暂时忽略。