Oracle字符集研究

Dextrys Co.,Ltd.Kelson Cong DBA Team Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.2飞刀，又见飞刀飞刀，又见飞刀 什么是字符集 字符集就是按照一定的字符编码方案，对一组特定的符号，分别赋予不同数值编码的集合。Oracle数据库最早支持的编码方案是US7ASCII。Oracle 的字符集命名遵循以下命名规则:即:比如:ZHS16GBK表示采用GBK编码格式、16位（两个字节）简体中文字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.3飞刀，又见飞刀飞刀，又见飞刀 字符编码方案 单字节编码 单字节7位字符集，可以定义128个字符，最常用的字符集为 US7ASCII 单字节8位字符集，可以定义256个字符，适合于欧洲大部分国家 例如：WE8ISO8859P1(西欧、8位、ISO标准8859P1编码)Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.4飞刀，又见飞刀飞刀，又见飞刀 多字节编码 变长多字节编码 某些字符用一个字节表示，其它字符用两个或多个字符表示，变长多字节编码常用于对亚洲语言的支持，例如日语、汉语、印地语等 例如：AL32UTF8（其中AL代表ALL,指适用于所有语言）、zhs16cgb231280 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.5飞刀，又见飞刀飞刀，又见飞刀 定长多字节编码 每一个字符都使用固定长度字节的编码方案，目前oracle唯一支持的定长多字节编码是AF16UTF16，也是仅用于国家字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.6飞刀，又见飞刀飞刀，又见飞刀 unicode 编码 Unicode 是一个涵盖了目前全世界使用的所有已知字符的单一编码方案，也就是说Unicode为每一个字符提供唯一的编码。UTF-16是unicode的16位编码方式，是一种定长多字节编码，用2个字节表示一个unicode字符，AF16UTF16是UTF-16编码字符集。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.7飞刀，又见飞刀飞刀，又见飞刀 UTF-8 是unicode的8位编码方式，是一种变长多字节编码，这种编码可以用1、2、3个字节表示一个unicode字符，AL32UTF8，UTF8、UTFE是UTF-8编码字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.8 当一种字符集（字符集A）的编码数值包含所有另一种字符集（字符集B）的编码数值，并且两种字符集相同编码数值代表相同的字符时，则字符集A是字符集B的超级，或称字符集B是字符集A的子集。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.9 Oracle8i 和oracle9i官方文档资料中备有子集-超级对照表（subset-superset pairs），例如：WE8ISO8859P1是WE8MSWIN1252的子集。由于US7ASCII是最早的Oracle数据库编码格式，因此有许多字符集是US7ASCII的超集，例如WE8ISO8859P1、ZHS16CGB231280、ZHS16GBK都是US7ASCII的超集。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.10 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.11 查看字符集的工具:LBUILDER Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.12 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.13数据库字符集数据库字符集 数据库字符集（oracle服务器端字符集）数据库字符集在创建数据库时指定，在创建后通常不能更改。在创建数据库时，可以指定字符集(CHARACTER SET)和国家字符集(NATIONAL CHARACTER SET)。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.14数据库字符集数据库字符集 字符集 用来存储CHAR,VARCHAR2,CLOB,LONG等类型数据 用来标示诸如表名、列名以及PL/SQL变量等 用来存储SQL和PL/SQL程序单元等 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.15数据库字符集数据库字符集 国家字符集：用以存储NCHAR,NVARCHAR2,NCLOB等类型数据 国家字符集实质上是为oracle选择的附加字符集，主要作用是为了增强oracle的字符处理能力，因为NCHAR数据类型可以提供对亚洲使用定长多字节编码的支持，而数据库字符集则不能。国家字符集在oracle9i中进行了重新定义，只能在unicode编码中的AF16UTF16和UTF8中选择，默认值是 AF16UTF16 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.16数据库字符集数据库字符集 讨论：1、什么情况下用CHAR，NCHAR？2、如何决定数据库字符集？Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.17数据库字符集数据库字符集 可以查询以下数据字典或视图查看字符集设置情况 nls_database_parameters、props$、v$nls_parameters 查询结果中NLS_CHARACTERSET表示字符集，NLS_NCHAR_CHARACTERSET表示国家字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.18数据库字符集数据库字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.19数据库字符集数据库字符集 如何修改服务器端字符集 Alter database character set ZHS16GBK 此方法慎用!千万不要通过修改PROPS$的方式修改 新的字符集一定是老字符集的超集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.20数据库字符集数据库字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.21数据库字符集数据库字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.22客户端字符集客户端字符集 客户端字符集定义了客户端字符数据的编码方式，任何发自或发往客户端的字符数据均使用客户端定义的字符集编码,客户端可以看作是能与数据库直接连接的各种应用，客户端字符集是通过设置NLS_LANG参数来设定的。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.23客户端字符集客户端字符集 NLS_LANG 参数格式 NLS_LANG=LANGUAGE_TERRITORY.CHARACTERSET Language:显示oracle消息,校验，日期命名 Territory：指定默认日期、数字、货币等格式 Client character set：指定客户端将使用的字符集 例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII AMERICAN是语言，AMERICA是地区，US7ASCII是客户端字符 集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.24客户端字符集客户端字符集 客户端字符集设置方法 1)UNIX 环境$NLS_LANG=“simplified chinese”_china.zhs16gbk$export NLS_LANG 编辑oracle用户的profile文件 2)Windows 环境 编辑注册表 HKEY_LOCAL_MACHINE-SOFTWARE-ORACLE-HOME0(9i)HKEY_LOCAL_MACHINE-SOFTWARE-ORACLE-KEY_OraDb10g_home1(10g)Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.25客户端字符集客户端字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.26客户端字符集客户端字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.27字符集转换字符集转换 EXP/IMP时存在的字符集转换 1、源数据库字符集 2、Export过程中用户会话字符集（通过NLS_LANG设定）3、Import过程中用户会话字符集（通过NLS_LANG设定）4、目标数据库字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.28字符集转换字符集转换 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.29字符集转换字符集转换 导出的转换过程 在Export过程中，如果源数据库字符集与Export用户会话字符集不一致，会发生字符集转换，并在导出文件的头部几个字节中存储Export用户会话字符集的ID号。在这个转换过程中可能发生数据的丢失。例:如果源数据库使用ZHS16GBK，而Export用户会话字符集使用US7ASCII，由于ZHS16GBK是16位字符集,而US7ASCII是7位字符集，这个转换过程中，中文字符在US7ASCII中不能够找到对等的字符，所以所有中文字符都会丢失而变成“?”形式，这样转换后生成的Dmp文件已经发生了数据丢失。因此如果想正确导出源数据库数据，则Export过程中用户会话字符集应等于源数据库字符集或是源数据库字符集的超集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.30字符集转换字符集转换导入的转换过程 1、确定导出数据库字符集环境 通过读取导出文件头，可以获得导出文件的字符集设置 2、确定导入session的字符集，即导入Session使用的NLS_LANG环境变量 3、IMP读取导出文件 读取导出文件字符集ID，和导入进程的NLS_LANG进行比较 4、如果导出文件字符集和导入Session字符集相同，那么在这一步骤内就不需要转换，如果不同，就需要把数据转换为导入Session使用的字符集。可以看出，导入数据到数据库过程中发生两次字符集转换 第一次:导入文件字符集与导入Session使用的字符集之间的转换，如果这个转换过程不能正确完成，Import向目标数据库的导入过程也就不能完成。第二次:导入Session字符集与数据库字符集之间的转换。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.31字符集转换字符集转换 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.32字符集转换字符集转换 如果源字符集和目标字符集一致，则保证全部客户端一致。如果源和目标不一致，则保证导入和导出客户端一致，且与源或者目标保持一致。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.33字符集转换字符集转换 为了避免在数据库迁移过程中由于字符集不同导致的数据损失，oracle提供了字符集扫描工具（character set scanner），通过这个工具我们可以测试在数据迁移过程中由于字符集转换可能带来的问题，然后根据测试结果，确定数据迁移过程中最佳字符集解决方案。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.34字符集转换字符集转换 用sysdba用户登陆 执行$ORACLE_HOMErdbmsadmincsminst.sql 然后退回到命令提示符 执行 csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=UTF8 log=check.log capture=y array=1000000 process=2 执行完毕以后查看log文件，可以知道那些转换会失败 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.35字符集转换字符集转换 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.36字符集转换字符集转换 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.37字符集转换字符集转换 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.38OS字符集字符集 OS字符集以及OS字符集在字符集转换中起的作用。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.39OS字符集字符集查看OS字符集 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.40OS字符集字符集 DEMO:环境:OS:WINXP SP2 中文版ORACLE 10.2.0.1服务器和客户端在一台机器上服务器字符集:AL32UTF8 国家字符集 AL16UTF16客户端字符集:AL32UTF8 以下是在SQLPLUS里面的操作：Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.41OS字符集字符集 SQL create table test(a varchar2(10);琛凡鍒涘缓銆?SQL insert into test values(我);宸插垱寤?1 琛屻?SQL commit;鎻愪氦瀹屾垚銆?SQL select a,dump(a)from test;A -DUMP(A)-我 Typ=96 Len=2:206,210 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.42OS字符集字符集 可以看到,服务器端和客户端都是UTF8但是显示出来的提示全部是乱码,在看一下汉字我字的编码,查了一下编码表,在ZHS16GBK里面是 206 98 ,在UTF8里面是230 136 145,在UTF16里面应该是98 17 但是实际的值是什么呢？实际的编码值是206 210,既不是GBK,也不是UTF8和UTF16的编码，查了一下其它的编码表，最终发现 206 210是GB2312“我”字的编码 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.43OS字符集字符集 接着把数据库客户端编码切换成ZHS16GBK，看看会发生什么 SQL insert into test values(我);已创建 1 行。SQL select a,dump(a)from test;A DUMP(A)我 Typ=96 Len=3:230,136,145 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.44OS字符集字符集 可以发现,数据库在插入时,准确的把我字转换成UTF8的编码,而在显示的时候又转换成了准确的汉字我,怎么解释上面这个现象呢？Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.45OS字符集字符集 通过试验我们可以发现OS的字符集实际是GB2312的，但是由于客户端字符集和服务器端字符集一致，所以在上传数据的时候，认为不存在字符集转换，所以就直接把OS的汉字编码上传上去了，这也是我们看到的库中实际存放的是GB2312“我”字编码 206 210的原因，显示的时候同样的方式，认为数据库服务器与客户端字符集编码一致，不存在字符集转换，于是把206 210发到客户端，到了客户端，就正好原封不动的把206 210显示成为了汉字“我”。这也是造成提示乱码的原因，让GBK的窗口去解析UTF8的编码，从而产生乱码。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.46OS字符集字符集 当客户端字符集是GBK的时候，执行INSERT的时候，ORACLE意识到服务器与客户端字符集不一致，产生一次字符集转换，把“我”字转换成UTF8编码230,136,145存入数据库中，select的时候做反操作，将UTF8转换成GBK编码显示出来。Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.47OS字符集字符集经典“靠”问题 Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.48OS字符集字符集 在一个中文系统中所有的汉字全部显示为“靠”DEMO 服务器端：ZHS16GBK 客户端：WE8ISO8859P1 OS:GBK Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.49OS字符集字符集 库里存在汉字“我是从辉”客户端做select ORACLE判断服务器客户端字符集不一致，于是做字符集转换，西欧字符集里面是没有汉字，转换成特殊编码，倒问号，编码为”BF”客户端得到“BF BF BF BF”OS是GBK，认为是两个汉字，“BFBF”,”BFBF”最终显示结果“靠靠”！Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.50QA Copyright,Suzsoft Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.Copyright,Dextrys Co.,Ltd.Confidential and Proprietary Information.All Rights Reserved.51演讲完毕，谢谢观看！