不同字符集下字符串类型的比对以及存汉字的比较

今天上午，之前测试都没有问题的过程，今天报错ora-06052的异常，查询之后发现原来是同一个字段在不同的表中定义的长度不一样，一个表中定义的是512，另一个表中定义的是500，现在分别用这两个字段定义了两个变量，当用512的变量给500的变量赋值时，如果赋值超过500就出问题了（确实超过500）。

随后我查了一下char，varchar，varchar，nvarchar这四种字符类型，以及这四种类型在utf8和gbk两种字符集下存储中文的情况，length和lengthb函数。

ora-12899:是向表中插入的值超过了字段的长度。

ora-06052:是在赋值时值超过了变量的长度。

length：统计的是字符串中字符的个数。

lengthb：统计的是字符串的字节数。

char2(10)，varchar(10)，varchar2(10)的字段：

在gbk数据库中可以存5个汉字，lengthb统计到每个汉字是2个字节。

在utf8数据库中可以存3个汉字，lengthb统计到每个汉字是3个字节。

nvarchar2(10)的字段：

在gbk数据库中可以存10个汉字。

在utf8数据库中可以存10个汉字。

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

char：定长字符型，[1,2000]字节，不足则在后面补空格。

varchar：不定长字符型，[1,2000]字节。

varchar2：不定长字符型，[1,4000]字节，在pl/sql中最大长度是32767（没有测试）。

nvarchar：不定长字符型，[1,4000]字符。从下面测试情况看，在这两个不同字符集中都是按照2个字节来存储数据。

--这个地方不明白，utf8下不是每个汉字三个字节吗，怎么这里是两个字节。

---下面是测试语句

SQL> --查看数据库服务端的字符集SQL> select userenv('language') from dual; USERENV('LANGUAGE')----------------------------------------------------SIMPLIFIED CHINESE_CHINA.UTF8SQL> --查看dmp的字符集SQL> select nls_charset_name(to_number('0354','xxxx')) from dual; NLS_CHARSET_NAME(TO_NUMBER('03----------------------------------------ZHS16GBKSQL> --查看数据库字符集SQL> select * from nls_database_parameters; PARAMETER                      VALUE------------------------------ --------------------------------------------NLS_LANGUAGE                   AMERICANNLS_TERRITORY                  AMERICANLS_CURRENCY                   $NLS_ISO_CURRENCY               AMERICANLS_NUMERIC_CHARACTERS         .,NLS_CHARACTERSET               UTF8NLS_CALENDAR                   GREGORIANNLS_DATE_FORMAT                DD-MON-RRNLS_DATE_LANGUAGE              AMERICANNLS_SORT                       BINARYNLS_TIME_FORMAT                HH.MI.SSXFF AMNLS_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              10.2.0.4.0 20 rows selectedSQL> --查看客户端字符集SQL> select * from nls_instance_parameters; PARAMETER                                                                        VALUE-------------------------------------------- --------------------------------------------NLS_LANGUAGE                                                                     AMERICANNLS_TERRITORY                                                                    AMERICANLS_SORT                                                                         NLS_DATE_LANGUAGE                                                                NLS_DATE_FORMAT                                                                  NLS_CURRENCY                                                                     NLS_NUMERIC_CHARACTERS                                                           NLS_ISO_CURRENCY                                                                 NLS_CALENDAR                                                                     NLS_TIME_FORMAT                                                                  NLS_TIMESTAMP_FORMAT                                                             NLS_TIME_TZ_FORMAT                                                               NLS_TIMESTAMP_TZ_FORMAT                                                          NLS_DUAL_CURRENCY                                                                NLS_COMP                                                                         NLS_LENGTH_SEMANTICS                                                             BYTENLS_NCHAR_CONV_EXCP                                                              FALSE 17 rows selectedSQL> --查看会话字符集SQL> select * from nls_session_parameters; PARAMETER                                                                        VALUE-------------------------------------------- --------------------------------------------NLS_LANGUAGE                                                                     SIMPLIFIED CHINESENLS_TERRITORY                                                                    CHINANLS_CURRENCY                                                                     ￥NLS_ISO_CURRENCY                                                                 CHINANLS_NUMERIC_CHARACTERS                                                           .,NLS_CALENDAR                                                                     GREGORIANNLS_DATE_FORMAT                                                                  DD-MON-RRNLS_DATE_LANGUAGE                                                                SIMPLIFIED CHINESENLS_SORT                                                                         BINARYNLS_TIME_FORMAT                                                                  HH.MI.SSXFF AMNLS_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                                                              FALSE 17 rows selectedSQL> drop table ttt1 purge; Table droppedSQL> drop table ttt2 purge; Table droppedSQL> drop table ttt3 purge; Table droppedSQL> drop table ttt4 purge; drop table ttt4 purge ORA-00054: resource busy and acquire with NOWAIT specifiedSQL> create table ttt1(name char(10)); Table createdSQL> insert into ttt1 values('123'); 1 row insertedSQL> insert into ttt1 values('我爱你'); 1 row insertedSQL> insert into ttt1 values('我爱你我你'); insert into ttt1 values('我爱你我你') ORA-12899: value too large for column "CDYX"."TTT1"."NAME" (actual: 15, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt1; NAME       LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)---------- ------------ ------------- --------------------------------------------123                  10            10 Typ=96 Len=10: 49,50,51,32,32,32,32,32,32,32我爱你                4            10 Typ=96 Len=10: 230,136,145,231,136,177,228,189,160,32SQL> create table ttt2(name varchar(10)); Table createdSQL> insert into ttt2 values('123'); 1 row insertedSQL> insert into ttt2 values('我爱你'); 1 row insertedSQL> insert into ttt2 values('我爱你我爱你'); insert into ttt2 values('我爱你我爱你') ORA-12899: value too large for column "CDYX"."TTT2"."NAME" (actual: 18, maximum: 10)SQL> insert into ttt2 values('我爱你我你'); insert into ttt2 values('我爱你我你') ORA-12899: value too large for column "CDYX"."TTT2"."NAME" (actual: 15, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt2; NAME       LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)---------- ------------ ------------- --------------------------------------------123                   3             3 Typ=1 Len=3: 49,50,51我爱你                3             9 Typ=1 Len=9: 230,136,145,231,136,177,228,189,160SQL> create table ttt3(name varchar2(10)); Table createdSQL> insert into ttt3 values('123'); 1 row insertedSQL> insert into ttt3 values('我爱你'); 1 row insertedSQL> insert into ttt3 values('我爱你我爱你'); insert into ttt3 values('我爱你我爱你') ORA-12899: value too large for column "CDYX"."TTT3"."NAME" (actual: 18, maximum: 10)SQL> insert into ttt3 values('我爱你我你'); insert into ttt3 values('我爱你我你') ORA-12899: value too large for column "CDYX"."TTT3"."NAME" (actual: 15, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt3; NAME       LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)---------- ------------ ------------- --------------------------------------------123                   3             3 Typ=1 Len=3: 49,50,51我爱你                3             9 Typ=1 Len=9: 230,136,145,231,136,177,228,189,160SQL> create table ttt4(name nvarchar2(10)); create table ttt4(name nvarchar2(10)) ORA-00955: name is already used by an existing objectSQL> insert into ttt4 values('123'); 1 row insertedSQL> insert into ttt4 values('我爱你'); 1 row insertedSQL> insert into ttt4 values('我爱你我爱你'); 1 row insertedSQL> insert into ttt4 values('我爱你我你'); 1 row insertedSQL> insert into ttt4 values('我爱你我你我爱你我你'); 1 row insertedSQL> insert into ttt4 values('我爱你我你我爱你我你1'); insert into ttt4 values('我爱你我你我爱你我你1') ORA-12899: value too large for column "CDYX"."TTT4"."NAME" (actual: 11, maximum: 10)SQL> select name,length(name),lengthb(name),dump(name) from ttt4; NAME                 LENGTH(NAME) LENGTHB(NAME) DUMP(NAME)-------------------- ------------ ------------- --------------------------------------------123                             3             6 Typ=1 Len=6: 0,49,0,50,0,51我爱你                          3             6 Typ=1 Len=6: 98,17,114,49,79,96我爱你我爱你                    6            12 Typ=1 Len=12: 98,17,114,49,79,96,98,17,114,49,79,96我爱你我你                      5            10 Typ=1 Len=10: 98,17,114,49,79,96,98,17,79,96我爱你我你我爱你我你           10            20 Typ=1 Len=20: 98,17,114,49,79,96,98,17,79,96,98,17,114,49,79,96,98,17,79,96123                             3             6 Typ=1 Len=6: 0,49,0,50,0,51我爱你                          3             6 Typ=1 Len=6: 98,17,114,49,79,96我爱你我爱你                    6            12 Typ=1 Len=12: 98,17,114,49,79,96,98,17,114,49,79,96我爱你我你                      5            10 Typ=1 Len=10: 98,17,114,49,79,96,98,17,79,96我爱你我你我爱你我你           10            20 Typ=1 Len=20: 98,17,114,49,79,96,98,17,79,96,98,17,114,49,79,96,98,17,79,96 10 rows selectedSQL> commit; Commit complete SQL>