目录
1、前言
在Oracle数据库中,存储和处理字符数据是日常开发中的重要任务。而在字符数据类型中,CHAR、NCHAR、VARCHAR2和NVARCHAR2是常见的选择,但它们之间具有一些关键的区别。了解这些区别对于正确选择和使用适当的字符数据类型至关重要。本文将深入比较CHAR、NCHAR、VARCHAR2和NVARCHAR2字符类型之间的区别,帮助大家理解它们的特性、应用场景以及如何进行选择。
注:本文仅适用于Oracle 11.2.0.4.0。
进入正文之前还是要先介绍下Unicode编码以及UTF-8和GBK编码方式。
Unicode编码是一种用于字符表示的标准编码系统,它定义了每个符号的唯一数字标识符,并且包含了全球范围内所有语言所需要的符号和文字,包括字母、数字、标点符号、特殊符号和控制字符等。Unicode编码的目的是为了解决不同语言间字符集的互换问题,使得全球范围内的计算机程序和数据都能够使用同一种标准编码方式来表示和交换文本信息,从而实现跨语言平台的通用性和互通性。Unicode编码采用16位或32位的整数来表示每个字符,因此可以表示超过100万种不同的字符。
UTF-8和GBK16都是字符编码方式,用于将字符映射到计算机存储和表示的二进制数据。
1.字符范围:
UTF-8:UTF-8是一种可变长度编码,能够表示Unicode字符集中的所有字符。它可以表示全球范围内的字符,包括英文、中文、日文、韩文等各种语言文字。
GBK16:GBK16是国标扩展字符集,主要用于中文字符的编码,能够表示中文汉字以及少量其他语言的字符。
2.编码方式
UTF-8:UTF-8采用可变长度编码,使用1至4个字节来表示不同的字符。对于ASCII字符(0-127),UTF-8使用单字节表示,而非ASCII字符则使用多个字节表示,根据需要进行扩展。
GBK16:GBK16采用固定长度编码,使用双字节表示所有的字符。
3.兼容性:
UTF-8:UTF-8是一种通用的字符编码方式,在国际化应用中被广泛使用,几乎支持所有语言的字符编码。
GBK16:GBK16主要用于中文环境,对于其他语言的字符支持较为有限。
4.字符表示大小:
UTF-8:UTF-8编码的字符长度可变,对于大部分常用字符占用1个字节,而较少用到的、罕见的字符占用2个或更多字节。
GBK16:GBK16编码的字符长度固定,每个字符都占用2个字节。
总的来说,UTF-8是一种更为通用、灵活且兼容性较广的字符编码方式,适用于全球化应用,而GBK16主要用于中文环境,对其他语言支持有限。
2、CHAR数据类型
2.1、数据类型定义
CHAR [(size [BYTE | CHAR])]:
数据类型指定固定长度的字符串,长度字节或字符数据。最大值为 2000 字节或字符,默认值和最小值为 1 字节。
如果存储的字符串长度少于指定的长度,Oracle会自动在字符串末尾填充空格字符,使其达到指定长度。如果尝试插入的值对于列来说太长,则 Oracle 将返回错误。
2.2、示例1-固定长度验证
//基于字符集ZHS16GBK环境
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
---------------------------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL> create table eg_char1(num number(5),a char(20));
SQL> insert into eg_char1 values (1,'你');
SQL> insert into eg_char1 values (2,'你好');
SQL> insert into eg_char1 values (3,'你好hello');
SQL> insert into eg_char1 values (4,'hello');
SQL> insert into eg_char1 values (5,'hellodba');
SQL> insert into eg_char1 values (6,'helloDBA');
SQL> commit;
SQL> select num,a,lengthb(a) from eg_char1;
NUM A LENGTHB(A)
------ -------------------- ----------
1 你 20
2 你好 20
3 你好hello 20
4 hello 20
5 hellodba 20
6 helloDBA 20
//基于字符集UTF8环境
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
---------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8
SQL> select num,a,lengthb(a) from eg_char1;
NUM A LENGTHB(A)
------ -------------------- ----------
1 你 20
2 你好 20
3 你好hello 20
4 hello 20
5 hellodba 20
6 helloDBA 20
//结论
在定义完CHAR类型列长度后,如果存储的字符串长度少于指定的长度,Oracle会自动在字符串末尾填充空格字符,使其达到指定长度。
复制
2.3、示例2-单个中英文所占用字节数
//基于字符集ZHS16GBK环境
SQL> create table eg_char2(num number(5),a char(20));
SQL> insert into eg_char2 values (1,'你');
SQL> insert into eg_char2 values (2,'你好');
SQL> insert into eg_char2 values (3,'h');
SQL> insert into eg_char2 values (4,'H');
SQL> insert into eg_char2 values (5,'你好hello');
SQL> commit;
SQL> select num,a,lengthb(trim(a)) from eg_char2;
NUM A LENGTHB(TRIM(A))
------ -------------------- ----------------
1 你 2
2 你好 4
3 h 1
4 H 1
5 你好hello 9
//基于字符集UTF8环境
SQL> select num,a,lengthb(trim(a)) from eg_char2;
NUM A LENGTHB(TRIM(A))
------ -------------------- ----------------
1 你 3
2 你好 6
3 h 1
4 H 1
5 你好hello 11
//结论
在字符集ZHS16GBK环境下,一个汉字占两个字节,一个字母占一个字节。
在字符集UTF8环境下,一个汉字占三个字节,一个字母占一个字节。
复制
2.4、示例3-最小值和最大值
//基于字符集ZHS16GBK环境
SQL> create table eg_char3(num number(5),a char,b char(2001));
create table eg_char3(num number(5),a char,b char(2001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_char3(num number(5),a char,b char(2000));
SQL> insert into eg_char3 values (1,'h','H');
SQL> commit;
SQL> select num,a,lengthb(trim(a)),lengthb(b) from eg_char3;
NUM A LENGTHB(TRIM(A)) LENGTHB(B)
------ - ---------------- ----------
1 h 1 2000
//基于字符集UTF8环境
SQL> create table eg_char3(num number(5),a char,b char(2001));
create table eg_char3(num number(5),a char,b char(2001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_char3(num number(5),a char,b char(2000));
SQL> insert into eg_char3 values (1,'h','H');
SQL> commit;
SQL> select num,a,lengthb(trim(a)),lengthb(b) from eg_char3;
NUM A LENGTHB(TRIM(A)) LENGTHB(B)
------ - ---------------- ----------
1 h 1 2000
//结论
最大值为 2000 字节,默认值和最小值为 1 字节,如果插入的值对于列来说太长,则 Oracle 将返回错误。
复制
3、NCHAR数据类型
3.1、数据类型定义
NCHAR [(size)]:
NCHAR数据类型是仅支持unicode的数据类型,它支持存储所有语言的字符。类似于CHAR,NCHAR也是定长的,需要指定固定长度,以字符为单位定义列长度,列的最大长度由国家字符集定义决定。字符数据类型 NCHAR 的宽度规格指的是字符个数。允许的最大列大小是2000字节。
如果存储的字符串长度少于指定的长度,Oracle会自动在字符串末尾填充空格字符,使其达到指定长度。
3.2、示例1-固定长度验证
//基于字符集ZHS16GBK环境
SQL> create table eg_nchar1(num number(5),a nchar(20));
SQL> insert into eg_nchar1 values (1,'你');
SQL> insert into eg_nchar1 values (2,'你好');
SQL> insert into eg_nchar1 values (3,'你好hello');
SQL> insert into eg_nchar1 values (4,'hello');
SQL> insert into eg_nchar1 values (5,'hellodba');
SQL> insert into eg_nchar1 values (6,'helloDBA');
SQL> commit;
SQL> select num,a,length(a) from eg_nchar1;
NUM A LENGTH(A)
------ ----------------------------------------- ----------
1 你 20
2 你好 20
3 你好hello 20
4 hello 20
5 hellodba 20
6 helloDBA 20
//基于字符集UTF8环境
SQL> select num,a,length(a) from eg_nchar1;
NUM A LENGTH(A)
------ ------------------------------------------------------------ ----------
1 你 20
2 你好 20
3 你好hello 20
4 hello 20
5 hellodba 20
6 helloDBA 20
//结论
在定义完NCHAR类型列长度后,如果存储的字符串长度少于指定的长度,Oracle会自动在字符串末尾填充空格字符,使其达到指定长度。
复制
3.3、示例2-单个中英文所占用字节数
//基于字符集ZHS16GBK环境
SQl> create table eg_nchar2(num number(5),a nchar(20));
SQl> insert into eg_nchar2 values (1,'你');
SQl> insert into eg_nchar2 values (2,'你好');
SQl> insert into eg_nchar2 values (3,'h');
SQl> insert into eg_nchar2 values (4,'H');
SQl> insert into eg_nchar2 values (5,'你好hello');
SQl> commit;
SQL> select a,lengthc(trim(a)),lengthb(trim(a)) from eg_nchar2;
A LENGTHC(TRIM(A)) LENGTHB(TRIM(A))
----------------------------------------- ---------------- ----------------
你 1 2
你好 2 4
h 1 2
H 1 2
你好hello 7 14
//基于字符集UTF8环境
SQL> select a,lengthc(trim(a)),lengthb(trim(a)) from eg_nchar2;
A LENGTHC(TRIM(A)) LENGTHB(TRIM(A))
------------------------------------------------------------ ---------------- ----------------
你 1 3
你好 2 6
h 1 1
H 1 1
你好hello 7 11
//结论
NCHAR的长度指的是字符个数,在字符集ZHS16GBK环境下,单个汉字和字母都占两个字节,在字符集UTF8环境下,一个汉字占三个字节,一个字母占一个字节。
复制
3.4、示例3-最小值和最大值
//基于字符集ZHS16GBK环境
SQL> create table eg_nchar3(num number(5),a nchar,b nchar(1001));
create table eg_nchar3(num number(5),a nchar,b nchar(1001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_nchar3(num number(5),a nchar,b nchar(1000));
SQL> insert into eg_nchar3 values (1,'h','H');
SQL> commit;
SQL> select num,a,lengthc(trim(a)),length(b) from eg_nchar3;
NUM A LENGTHC(TRIM(A)) LENGTH(B)
------ --- ---------------- ----------
1 h 1 1000
//基于字符集UTF8环境
SQL> create table eg_nchar3(num number(5),a nchar,b nchar(2001));
create table eg_nchar3(num number(5),a nchar,b nchar(2001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_nchar3(num number(5),a nchar,b nchar(2000));
SQL> insert into eg_nchar3 values (1,'h','H');
SQL> commit;
SQL> select num,a,lengthc(trim(a)),length(b) from eg_nchar3;
NUM A LENGTHC(TRIM(A)) LENGTH(B)
------ --- ---------------- ----------
1 h 1 2000
//结论
在字符集ZHS16GBK环境下,NCHAR长度最大能定义1000个字符,最小为1个字符长度。
字符集UTF8环境下,NCHAR长度最大能定义2000个字符,最小为1个字符长度。
复制
4、VARCHAR2数据类型
4.1、数据类型定义
VARCHAR2(size [BYTE | CHAR]):
VARCHAR2数据类型指定一个可变长度的字符串,最小长度1字节,最大长度4000字节。在创建 VARCHAR2 列时,需要提供它可以保存的最大字节数或数据字符数。Oracle随后将按照您指定的值在列中存储每个值,前提是该值不超过列的最大长度。如果您尝试插入超过指定长度的值,那么Oracle将返回一个错误。
4.2、示例1-可变长度验证
//基于字符集ZHS16GBK环境
SQL> create table eg_varchar21(num number(5),a varchar2(20));
SQL> insert into eg_varchar21 values (1,'');
SQL> insert into eg_varchar21 values (2,' ');
SQL> insert into eg_varchar21 values (3,'你');
SQL> insert into eg_varchar21 values (4,'你好');
SQL> insert into eg_varchar21 values (5,'你好hello');
SQL> insert into eg_varchar21 values (6,'hello');
SQL> insert into eg_varchar21 values (7,'hellodba');
SQL> insert into eg_varchar21 values (8,'helloDBA');
SQL> commit;
SQL> select num,a,lengthb(a) from eg_varchar21;
NUM A LENGTHB(A)
------ -------------------- ----------
1
2 1
3 你 2
4 你好 4
5 你好hello 9
6 hello 5
7 hellodba 8
8 helloDBA 8
//基于字符集UTF8环境
SQL> select num,a,lengthb(a) from eg_varchar21;
NUM A LENGTHB(A)
------ -------------------- ----------
1
2 1
3 你 3
4 你好 6
5 你好hello 11
6 hello 5
7 hellodba 8
8 helloDBA 8
//结论
虽然在创建表的时候对字段预先定义了最大字节长度,但是实际所占用的字节数是按照所存储的数据计算的。
复制
4.3、示例2-单个中英文所占用字节数
//基于字符集ZHS16GBK环境
SQL> create table eg_varchar22(num number(5),a varchar2(20));
SQL> insert into eg_varchar22 values (1,'你');
SQL> insert into eg_varchar22 values (2,'你好');
SQL> insert into eg_varchar22 values (3,'h');
SQL> insert into eg_varchar22 values (4,'H');
SQL> insert into eg_varchar22 values (5,'你好hello');
SQL> commit;
SQL> select num,a,lengthb(a) from eg_varchar22;
NUM A LENGTHB(A)
------ -------------------- ----------
1 你 2
2 你好 4
3 h 1
4 H 1
5 你好hello 9
//基于字符集UTF8环境
SQL> select num,a,lengthb(a) from eg_varchar22;
NUM A LENGTHB(A)
------ -------------------- ----------
1 你 3
2 你好 6
3 h 1
4 H 1
5 你好hello 11
//结论
在字符集ZHS16GBK环境下,一个汉字占两个字节,一个英文占一个字节。
在字符集UTF8环境下,一个汉字占三个字节,一个英文占一个字节。
复制
4.4、示例3-最小值和最大值
//基于字符集ZHS16GBK环境
SQL> create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4001));
create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4000));
SQL> insert into eg_varchar23 values (1,'h','你');
SQL> insert into eg_varchar23 values (1,'H','你好');
SQL> commit;
SQL> select num,a,lengthb(a),b,lengthb(b) from eg_varchar23;
NUM A LENGTHB(A) B LENGTHB(B)
------ - ---------- -------------------------------------------------------------------------------- ----------
1 h 1 你 2
1 H 1 你好 4
//基于字符集UTF8环境
SQL> create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4001));
create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4000));
SQL> insert into eg_varchar23 values (1,'h','你');
SQL> insert into eg_varchar23 values (1,'H','你好');
SQL> commit;
SQL> select num,a,lengthb(a),b,lengthb(b) from eg_varchar23;
NUM A LENGTHB(A) B LENGTHB(B)
------ - ---------- -------------------------------------------------------------------------------- ----------
1 h 1 你 3
1 H 1 你好 6
//结论
无论在符集ZHS16GBK环境下还是字符集UTF8环境下,VARCHAR2的长度最大是4000字节,最小1字节。
复制
5、NVARCHAR2数据类型
5.1、数据类型定义
NVARCHAR2 (size):
NVARCHAR2 数据类型是仅支持unicode的可变长度的数据类型。在创建一个包含 NVARCHAR2 列的表时,需要提供它可以容纳的最大字符数,Oracle随后将按照您指定的值在列中存储每个值,前提是该值不超过列的最大长度,列的最大长度由国家字符集定义决定,允许的最大列大小是4000字节。
5.2、示例1-可变长度验证
//基于字符集ZHS16GBK环境
SQL> create table eg_nvarchar21(num number(5),a varchar2(20));
SQL> insert into eg_nvarchar21 values (1,'');
SQL> insert into eg_nvarchar21 values (2,' ');
SQL> insert into eg_nvarchar21 values (3,'你');
SQL> insert into eg_nvarchar21 values (4,'你好');
SQL> insert into eg_nvarchar21 values (5,'你好hello');
SQL> insert into eg_nvarchar21 values (6,'hello');
SQL> insert into eg_nvarchar21 values (7,'hellodba');
SQL> insert into eg_nvarchar21 values (8,'helloDBA');
SQL> commit;
SQL> select num,a,lengthc(a) from eg_varchar21;
NUM A LENGTHC(A)
------ -------------------- ----------
1
2 1
3 你 1
4 你好 2
5 你好hello 7
6 hello 5
7 hellodba 8
8 helloDBA 8
//基于字符集UTF8环境
SQL> select num,a,lengthc(a) from eg_varchar21;
NUM A LENGTHC(A)
------ -------------------- ----------
1
2 1
3 你 1
4 你好 2
5 你好hello 7
6 hello 5
7 hellodba 8
8 helloDBA 8
//结论
虽然在创建表的时候对字段预先定义了最大字节长度字符,但是实际所占用的字符数是按照所存储的数据计算的,并不是最大长度,且不受字符集影响。
复制
5.3、示例2-单个中英文所占用字节数
//基于字符集ZHS16GBK环境
SQL> create table eg_varchar22(num number(5),a varchar2(20));
SQL> insert into eg_varchar22 values (1,'你');
SQL> insert into eg_varchar22 values (2,'你好');
SQL> insert into eg_varchar22 values (3,'h');
SQL> insert into eg_varchar22 values (4,'H');
SQL> insert into eg_varchar22 values (5,'你好hello');
SQL> commit;
SQL> select num,a,lengthc(a),lengthb(a) from eg_varchar22;
NUM A LENGTHC(A) LENGTHB(A)
------ -------------------- ---------- ----------
1 你 1 2
2 你好 2 4
3 h 1 1
4 H 1 1
5 你好hello 7 9
//基于字符集UTF8环境
SQL> select num,a,lengthc(a),lengthb(a) from eg_varchar22;
NUM A LENGTHC(A) LENGTHB(A)
------ -------------------- ---------- ----------
1 你 1 3
2 你好 2 6
3 h 1 1
4 H 1 1
5 你好hello 7 11
//结论
在字符集ZHS16GBK环境下,一个汉字占两个字节,一个英文占一个字节。
在字符集UTF8环境下,一个汉字占三个字节,一个英文占一个字节。
复制
5.4、示例3-最小值和最大值
//基于字符集ZHS16GBK环境
SQL> create table eg_nvarchar23(num number(5),a nvarchar2(1),b nvarchar2(2001));
create table eg_nvarchar23(num number(5),a nvarchar2(1),b nvarchar2(2001))
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table eg_nvarchar23(num number(5),a nvarchar2(1),b nvarchar2(2000));
SQL> insert into eg_nvarchar23 values (1,'h','你');
SQL> insert into eg_nvarchar23 values (1,'H','你好');
SQL> commit;
SQL> select num,a,lengthb(a),b,lengthb(b) from eg_nvarchar23;
NUM A LENGTHB(A) B LENGTHB(B)
------ --- ---------- -------------------------------------------------------------------------------- ----------
1 h 2 你 2
1 H 2 你好 4
//基于字符集UTF8环境
SQL> create table eg_nvarchar23(num number(5),a nvarchar2(1),b nvarchar2(4001));
create table eg_nvarchar23(num number(5),a nvarchar2(1),b nvarchar2(4001))
ORA-00910: 指定的长度对于数据类型而言过长
create table eg_nvarchar23(num number(5),a nvarchar2(1),b nvarchar2(4000));
insert into eg_nvarchar23 values (1,'h','你');
insert into eg_nvarchar23 values (1,'H','你好');
commit;
SQL> create table eg_varchar23(num number(5),a varchar2(1),b varchar2(4000));
SQL> insert into eg_varchar23 values (1,'h','你');
SQL> insert into eg_varchar23 values (1,'H','你好');
SQL> commit;
SQL> select num,a,lengthb(a),b,lengthb(b) from eg_nvarchar23;
NUM A LENGTHB(A) B LENGTHB(B)
------ --- ---------- -------------------------------------------------------------------------------- ----------
1 h 1 你 3
1 H 1 你好 6
//结论
在字符集ZHS16GBK环境下,NVARCHAR2最大长度是2000个字符,最小长度是1个字符。
在字符集UTF8环境环境下,NVARCHAR2最大长度是4000个字符,最小长度是1个字符。
复制
6、应用场景
1.CHAR:CHAR数据类型适合存储长度固定的数据,常见的应用场景包括:
存储国家或地区的代号或编码,这些代号通常具有固定的长度。
存储身份证号码、车牌号等特定格式的标识符,这些标识符的长度是固定的
2.NCHAR:NCHAR数据类型适合存储Unicode字符数据,主要应用场景包括:
多语言环境下的应用程序,例如存储用户的姓名、地址等信息。因为不同语言的字符可能具有不同的编码和表示方式,使用NCHAR可以确保正确存储和检索这些字符数据。
存储包含表情符号、特殊符号等Unicode字符的文本数据。
3.VARCHAR2:VARCHAR2数据类型适合存储可变长度的字符数据,常见的应用场景包括:
存储用户输入的文本数据,例如评论、短信内容等。由于文本长度不确定,并且可变长数据类型可以根据实际长度分配存储空间,VARCHAR2非常适合这种场景。
存储文章、博客、新闻等大段文本的内容,可以根据实际需求分配足够的存储空间。
4.NVARCHAR2:NVARCHAR2数据类型适合存储可变长度的Unicode字符数据,常见的应用场景包括:
多语言环境下的应用程序,例如存储用户的评论、留言等文本数据。Unicode字符集可以支持多种语言的字符,而NVARCHAR2可以根据实际长度分配存储空间。
存储包含表情符号、特殊符号等Unicode字符的文本数据,NVARCHAR2可以确保这些字符正确存储和检索。
综上所述,不同的数据类型适用于不同的场景。CHAR和NCHAR适合存储固定长度的字符数据,VARCHAR2和NVARCHAR2适合存储可变长度的字符数据。此外,NCHAR和NVARCHAR2还适用于需要支持多语言字符集和Unicode字符的应用程序。
7、总结
总结一下,CHAR和NCHAR是定长数据类型,而VARCHAR2和NVARCHAR2是可变长数据类型。CHAR和VARCHAR2适用于存储非Unicode字符数据,而NCHAR和NVARCHAR2适用于存储Unicode字符数据。定长类型CHAR和NCHAR在存储空间方面可能会浪费一些空间,而可变长类型VARCHAR2和NVARCHAR2则可以更有效地利用存储空间。正确选择适当的字符数据类型对于数据库的性能和数据完整性非常重要。通过清晰地理解Oracle数据库提供的字符数据类型及其特点,正确选择数据类型不仅可以提高数据库性能和存储效率,还可以确保数据的完整性和一致性。
8、写在后面
由于本人有限的能力和知识储备,可能存在错误或疏漏之处,如有错误敬请批评指正!
公众号:Hello DBA