CentOS4.8英文系统+Oracle10gR2,
数据库的Character Set=AL32UTF8
数据库的National Character Set=AL16UTF16
SQL> SELECT * FROM v$NLS_PARAMETERS;PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AMPARAMETER VALUE
------------------------------ ----------------------------------------
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 $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE19 rows selected.演示:10个字节的varchar2类型为什么放不下2个汉字?
SQL> create table t(val varchar2(10));Table created.SQL> insert into t values ('一二');
insert into t values ('一二')
*
ERROR at line 1:
ORA-12899: value too large for column "A"."T"."VAL" (actual: 18, maximum: 10)
问题:按理说AL32UTF8字符集是用3个字节保存一个汉字,10个字节就应该能存3个汉字,可为什么连2个汉字也放不下呢?SQL> insert into t values ('1234567890');1 row created.
插入单字节字符正常。
改用nvarchar2类型来试试,记得老师说过,nvarchar2类型的数据在数据库中的编码是由national character set值指定,当前数据库的national character set是AL16UTF16,这个字符集是采用2个字节存储一个汉字。照此计算,10个字节就应该能存储5个汉字,可以下演示中连4个汉字都放不下,为什么会这样?
SQL> create table t2 (val nvarchar2(10));Table created.SQL> insert into t2 values('一二三四');
insert into t2 values('一二三四')
*
ERROR at line 1:
ORA-12899: value too large for column "A"."T2"."VAL" (actual: 12, maximum: 10)
期待高手解答一下,Oracle数据库的字符集搞的我头都大了。
数据库的Character Set=AL32UTF8
数据库的National Character Set=AL16UTF16
SQL> SELECT * FROM v$NLS_PARAMETERS;PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AMPARAMETER VALUE
------------------------------ ----------------------------------------
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 $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE19 rows selected.演示:10个字节的varchar2类型为什么放不下2个汉字?
SQL> create table t(val varchar2(10));Table created.SQL> insert into t values ('一二');
insert into t values ('一二')
*
ERROR at line 1:
ORA-12899: value too large for column "A"."T"."VAL" (actual: 18, maximum: 10)
问题:按理说AL32UTF8字符集是用3个字节保存一个汉字,10个字节就应该能存3个汉字,可为什么连2个汉字也放不下呢?SQL> insert into t values ('1234567890');1 row created.
插入单字节字符正常。
改用nvarchar2类型来试试,记得老师说过,nvarchar2类型的数据在数据库中的编码是由national character set值指定,当前数据库的national character set是AL16UTF16,这个字符集是采用2个字节存储一个汉字。照此计算,10个字节就应该能存储5个汉字,可以下演示中连4个汉字都放不下,为什么会这样?
SQL> create table t2 (val nvarchar2(10));Table created.SQL> insert into t2 values('一二三四');
insert into t2 values('一二三四')
*
ERROR at line 1:
ORA-12899: value too large for column "A"."T2"."VAL" (actual: 12, maximum: 10)
期待高手解答一下,Oracle数据库的字符集搞的我头都大了。
你使用lengb看下,你分别的两种类型的字节长度看看呢?SQL> create table t(val varchar2(10));Table createdSQL> insert into t values ('一二');1 row insertedSQL> select lengthb(val) from t;LENGTHB(VAL)
------------
6SQL> drop table t;Table droppedSQL> create table t(val nvarchar2(10));Table createdSQL> insert into t values ('一二');1 row insertedSQL> commit;Commit completeSQL> select lengthb(val) from t;LENGTHB(VAL)
------------
4SQL> SELECT * FROM v$NLS_PARAMETERS;PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ¥
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 ¥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE19 rows selectedSQL>
我这一个汉字居然占用9个字节,不知道为什么会这样?
SQL> select * from t;no rows selectedSQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
VAL VARCHAR2(10)SQL> insert into t values('一');1 row created.SQL> select lengthb(val) from t;LENGTHB(VAL)
------------
9
地域是America
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA我将环境变量NLS_LANG设置为'Simplified Chinese_China.zhs16gbk',情况有所改观,一个汉字由原来的占用9个字节变为现在的占用6个字节,显然还没有达到正常的状态,正常时应该是2-3个字节。实验如下:
[oracle@myhost ~]$ export NLS_LANG='Simplified Chinese_China.zhs16gbk'
[oracle@myhost ~]$ sqlplus a/aSQL*Plus: Release 10.2.0.1.0 - Production on 26 02:23:02 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> col parameter format a30
SQL> col value format a40
SQL> select * from nls_session_parameters;PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMPARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE17 rows selected.SQL> truncate table t;Table truncated.SQL> insert into t values('一');1 row created.SQL> select lengthb(val) from t;LENGTHB(VAL)
------------
6
不知道思路是否正确?
-------------------- ----------- ------------
一 2 6SQL> select dump(val) from t;DUMP(VAL)
--------------------------------------------------------------------------------
Typ=1 Len=6: 230,182,147,226,130,172
我将SecureCRT的字符编码设为ANSI/OEM - 简体中文GBK,果然正常了,varchar2类型的汉字占用3个字节,nvarchar2类型的汉字占用2个字节。测试如下:
SQL> truncate table t;Table truncated.SQL> insert into t values ('一');1 row created.SQL> select val,length(val),lengthb(val) from t;VAL LENGTH(VAL) LENGTHB(VAL)
-------------------- ----------- ------------
一 1 3SQL> select dump(val) from t;DUMP(VAL)
--------------------------------------------------------------------------------
Typ=1 Len=3: 228,184,128SQL> desc t2;
Name Null? Type
----------------------------------------- -------- ----------------------------
VAL NVARCHAR2(10)SQL> truncate table t2;Table truncated.SQL> insert into t2 values ('一');1 row created.SQL> select val,length(val),lengthb(val) from t2;VAL LENGTH(VAL) LENGTHB(VAL)
-------------------- ----------- ------------
一 1 2SQL> select dump(val) from t2;DUMP(VAL)
--------------------------------------------------------------------------------
Typ=1 Len=2: 78,0