Oracle 关于varchar2(2)和varchar2(2 char)的区别
当使用oracle数据库时,我们在创建表要存储不定数量字符时常规的创建字段类型为varchar2(n)这种,但有时候也可以见到varchar2(n char)这样来创建的。
那么,关于varchar2(n)和varchar2(n char)有何区别呢?
如下问了chatgpt的回答:
问:varchar2(2)和varchar2(2 char)区别
答:在Oracle数据库中,varchar2(n)用于存储可变长度字符串,其中n代表最大字符数。而varchar2(n char)用于存储具有指定字符数的Unicode字符。
区别在于varchar2(n)是以字节为单位计算长度,而varchar2(n char)是以字符为单位计算长度。在多字节字符集(如UTF-8)中,单个字符可能由多个字节组成。因此,使用varchar2(n)时,存储的字符数可能受到单个字符占用字节数的限制;而使用varchar2(n char)时,字符数受到指定的字符数限制。
例如,假设一个汉字在UTF-8编码中占3个字节,若使用varchar2(6),则最多能存储2个汉字(6个字节);而使用varchar2(2 char),则最多能存储2个汉字(6个字节)。
因此,当需要考虑多字节字符(如汉字)的情况时,最好使用varchar2(n char),以确保能够正确计算和存储字符数。
简而言之就是说:varchar2(n)里的n是字节为单位的,像1个汉字可能最多占用3个字节,那么varchar2(3)最多只能存一个汉字,而varchar2(n char)里的n是字符为单位的,1个汉字也是一个字符,那么varcha2(3 char)最多可以存3个汉字。
实际情况是不是如此呢,以下为测试过程:
先直接创建表create table test(id int,name varchar2(3),name1 varchar2(3 char));,直接进行插入,发现插入都失败了
SYS@test11g>create table test(id int,name varchar2(3),name1 varchar2(3 char));
Table created.
SYS@test11g>desc test;
Name Null? Type
------------------------------------------------------------
ID NUMBER(38)
NAME VARCHAR2(3)
NAME1 VARCHAR2(3 CHAR)
SYS@test11g>insert into test values(1,'你','你');
insert into test values(1,'你','你')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."NAME" (actual: 6, maximum: 3)
SYS@test11g>insert into test values(1,'你好','你好');
insert into test values(1,'你好','你好')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."NAME" (actual: 12, maximum: 3)
SYS@test11g>insert into test values(1,'nb','你好');
insert into test values(1,'nb','你好')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."NAME1" (actual: 4, maximum: 3)
SYS@test11g>insert into test values(1,'nb','你好');
insert into test values(1,'nb','你好') *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."NAME1" (actual: 6, maximum: 3)
检查数据库字符集及客户端字符集
SYS@test11g>select * from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
SYS@test11g>select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
数据库字符集为AL32UTF8,确定是支持存储中文的,
客户端字符集是AMERICAN_AMERICA.AL32UTF8,这里将客户端字符集设置为简体中文:
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
再次进行插入可以正常插入了。
[oracle@DBaaS-test1 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
[oracle@DBaaS-test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期二 7月 9 10:09:21 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@test11g>select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8
SYS@test11g>insert into test values(1,'nb','你好');
1 row created.
SYS@test11g>desc test;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ID NUMBER(38)
NAME VARCHAR2(3)
NAME1 VARCHAR2(3 CHAR)
SYS@test11g>insert into test values(1,'nb','你好呀');
1 row created.
SYS@test11g>select * from test;
ID NAM NAME1
---------- --- ------------
1 nb 你好
1 nb 你好呀
SYS@test11g>commit;
Commit complete.
SYS@test11g>
SYS@test11g>insert into test values(1,'好','你好呀');
1 row created.
SYS@test11g>insert into test values(1,'你好','你好呀');
insert into test values(1,'你好','你好呀')
*
ERROR at line 1:
ORA-12899: 列 "SYS"."TEST"."NAME" 的值太大 (实际值: 6, 最大值: 3)
SYS@test11g>select * from test;
ID NAM NAME1
---------- --- ------------
1 nb 你好
1 nb 你好呀
1 好 你好呀
总结:虽然我们在建库的时候都记得要创建支持中文字符集的库NLS_CHARACTERSET 为AL32UTF8 或者ZHS16GBK,但实际在进行中文插入或者查询的时候,客户端字符集也会对操作产生很大影响,这里的参数主要有NLS_LANG参数控制的,在sqlplus 可以通过 select userenv(‘language’) from dual 进行查询;NLS_LANG 不设置的话一般为AMERICAN_AMERICA.AL32UTF8 ,而使用简体中文时需要设置为SIMPLIFIED CHINESE_CHINA.AL32UTF8。
建议:在要使用简体中文的环境中首先将环境变量设置为SIMPLIFIED CHINESE_CHINA.AL32UTF8
Linux系统设置环境变量:export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
Windows系统添加全局环境变量NLS_LANG 值配置为SIMPLIFIED CHINESE_CHINA.AL32UTF8。