oracle字符集

一，汉字在oracle中占用字节数

一定要在建库的时候就选择好字符集，否则可能给后续的开发或者迁移带来问题。在开发中字符集问题通常会导致应用层面上的字符越界或者乱码问题。

先来看看2个使用不同字符集的数据库：

先来看server1：

SQL> select * from v$nls_parameters a ;



PARAMETER                                                       VALUE

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

NLS_LANGUAGE                                                    SIMPLIFIED CHINESE

NLS_TERRITORY                                                   CHINA

NLS_CURRENCY                                                    RMB

NLS_ISO_CURRENCY                                                CHINA

NLS_NUMERIC_CHARACTERS                                          .,

NLS_CALENDAR                                                    GREGORIAN

NLS_DATE_FORMAT                                                 DD-MON-RR

NLS_DATE_LANGUAGE                                               SIMPLIFIED CHINESE

NLS_CHARACTERSET                                                WE8ISO8859P1

NLS_SORT                                                        BINARY

NLS_TIME_FORMAT                                                 HH.MI.SSXFF AM

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                                               RMB

NLS_NCHAR_CHARACTERSET                                          AL16UTF16

NLS_COMP                                                        BINARY

NLS_LENGTH_SEMANTICS                                            BYTE

NLS_NCHAR_CONV_EXCP                                             FALSE



19 rows selected



SQL> select '汉字' from dual;



'？？'

------

？



再看看server2：

SQL> select * from v$nls_parameters a ;



PARAMETER                                                       VALUE

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

NLS_LANGUAGE                                                    SIMPLIFIED CHINESE

NLS_TERRITORY                                                   CHINA

NLS_CURRENCY                                                    RMB

NLS_ISO_CURRENCY                                                CHINA

NLS_NUMERIC_CHARACTERS                                          .,

NLS_CALENDAR                                                    GREGORIAN

NLS_DATE_FORMAT                                                 DD-MON-RR

NLS_DATE_LANGUAGE                                               SIMPLIFIED CHINESE

NLS_CHARACTERSET                                                AL32UTF8

NLS_SORT                                                        BINARY

NLS_TIME_FORMAT                                                 HH.MI.SSXFF AM

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                                               RMB

NLS_NCHAR_CHARACTERSET                                          AL16UTF16

NLS_COMP                                                        BINARY

NLS_LENGTH_SEMANTICS                                            BYTE

NLS_NCHAR_CONV_EXCP                                             FALSE



19 rows selected



SQL> select '汉字' from dual;



'汉字'

------

汉字



通过比较上述两个数据库的区别，发现他们的NLS_CHARACTERSET不同，一个是WE8ISO8859P1，而另外一个是AL32UTF8。WE8ISO8859P1是单字节8位字符集，AL32UTF8是变长多字节编码。

现在的客户端的字符集为：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

WE8ISO8859P1没有汉字编码，一般来讲，有中文字符就不应该使用这个字符集，虽然修改客户端和服务器端相同时可以解决乱码问题。

有关汉字在oracle中占用的字节数问题：

在WE8ISO8859P1字符集下，一个汉字占了1个字节。

在AL32UTF8字符集下面，一个汉字占了3个字节。

在ZHS16GBK是占用了2个字节。

Varchar2，varchar，nvarchar2均为变长字符类型，char则是固定长度。

用vsize函数来看的话很明显：

Server1:

SQL> select vsize('汉') from dual;



VSIZE('？')

-----------

         1

Server2:

SQL> select vsize('汉') from dual;



VSIZE('汉')

-----------

         3

而用length来看，则均为1：

Server1：

SQL> select length('汉') from dual;



LENGTH('？')

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

          1

Server2：

SQL> select length('汉') from dual;



LENGTH('汉')

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

          1



下面具体来看个对比：

Server1和server2中均建立下面的表：

create table t_test_var

(

v_char2 char(2),

v_char3 char(3),

v_varchar22 varchar2(2),

v_varchar23 varchar2(3),

v_varchar2 varchar(2),

v_varchar3 varchar(3),

v_nvarchar22 nvarchar2(2),

v_nvarchar23 nvarchar2(3)

);

然后分别在两个数据库中插入测试数据：

Server1:

SQL> insert into t_test_var values ('我','我','我','我','我','我','我','我');



1 row inserted

SQL> insert into t_test_var values ('我们','我们','我们','我们','我们','我们','我们','我们');



1 row inserted

SQL> insert into t_test_var values ('我们是','我们是','我们是','我们是','我们是','我们是','我们是','我们是');



insert into t_test_var values ('我们是','我们是','我们是','我们是','我们是','我们是','我们是','我们是')



ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_CHAR2" (actual: 3, maximum: 2)



说明server1中char用一个字节来存储一个汉字。

修改一下最后一条插入语句，使得char类型不越界：



SQL> insert into t_test_var values ('我们','我们是','我们是','我们是','我们是','我们是','我们是','我们是');



insert into t_test_var values ('我们','我们是','我们是','我们是','我们是','我们是','我们是','我们是')



ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_VARCHAR22" (actual: 3, maximum: 2)



SQL>

则说明了server1中的varchar2类型也是一个字节存储一个汉字，再次修改：

SQL> insert into t_test_var values ('我们','我们是','我们','我们是','我们是','我们是','我们是','我们是');



insert into t_test_var values ('我们','我们是','我们','我们是','我们是','我们是','我们是','我们是')



ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_VARCHAR2" (actual: 3, maximum: 2)

则说明varchar也是一个汉字占一个字节存储空间，继续修改：



SQL> insert into t_test_var values ('我们','我们是','我们','我们是','我们','我们是','我们是','我们是');



insert into t_test_var values ('我们','我们是','我们','我们是','我们','我们是','我们是','我们是')



ORA-12899: value too large for column "TEST"."T_TEST_VAR"."V_NVARCHAR22" (actual: 3, maximum: 2)

则说明nvarchar2也是一个汉字占一个字节存储空间。

这是因为在WE8ISO8859P1字符中，根本没有汉字编码。所以得出以上的实验结果。



再来看看Server2:

SQL> insert into t_test_var values ('我','我','我','我','我','我','我','我');



insert into t_test_var values ('我','我','我','我','我','我','我','我')



ORA-01401: inserted value too large for column

SQL> insert into t_test_var values ('我们','我们','我们','我们','我们','我们','我们','我们');



insert into t_test_var values ('我们','我们','我们','我们','我们','我们','我们','我们')



ORA-01401: inserted value too large for column

SQL> insert into t_test_var values ('我们是','我们是','我们是','我们是','我们是','我们是','我们是','我们是');



insert into t_test_var values ('我们是','我们是','我们是','我们是','我们是','我们是','我们是','我们是')



ORA-01401: inserted value too large for column



SQL> select cast('汉' as char(1)) from dual;



select cast('汉' as char(1)) from dual



ORA-25137: Data value out of range



SQL> select cast('汉' as char(2)) from dual;



select cast('汉' as char(2)) from dual



ORA-25137: Data value out of range



SQL> select cast('汉' as char(3)) from dual;



CAST('汉'ASCHAR(3))

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

汉

SQL> insert into t_test_var (v_varchar22) values ('汉');



insert into t_test_var (v_varchar22) values ('汉')



ORA-01401: inserted value too large for column

SQL> insert into t_test_var (v_varchar23) values ('汉');



1 row inserted



oracle 不同的编码，汉字占用的字节数步同，如下

sql: select * from nls_database_parameters;
当NLS_CHARACTERSET=AL32UTF8时（）
NLS_LENGTH_SEMANTICS=BYTE时，一个汉字代表三个字节
NLS_LENGTH_SEMANTICS=CHAR时，一个汉字代表一个字节
当NLS_CHARACTERSET=US7ASCII时（字符集为单字节）
NLS_LENGTH_SEMANTICS=BYTE时，一个汉字代表两个字节
NLS_LENGTH_SEMANTICS=CHAR时，一个汉字代表两个字节

NLS_LENGTH_SEMANTICS=BYTE时



在WE8ISO8859P1字符集下，一个汉字占了1个字节。



在ZHS16GBK是占用了2个字节。