亲手做实验来感受nvarchar2和varchar2的区别

SQL数据库运维 2022-10-18

4878

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

本文适用于Oracle版本11.2.0.1.0，不保证在其它版本的适用性。因为从Oracle 12C开始，可以为VARCHAR2数据类型最大长度为：32767。Oracle使用MAX_STRING_SIZE参数来控制最大长度。如果MAX_STRING_SIZE是STANDARD，则VARCHAR2的最大大小是4000字节。如果MAX_STRING_SIZE为EXTENDED，则VARCHAR2的大小限制为32767。

如果是字符串型字段，是设为varchar2还是nvarchar2好，如果设置了最大长度是多少？网上众说纷纭，下文试图从实验入手，亲身获取第一手资料，免得被人误导。

首先，要判断varchar2和nvarchar2存入字符的字节长度，必须先看看系统设置的字符集，如果字符集设置的不一样，结果就会显示出的不一样，文末有解释：

SELECT * FROM Nls_Database_Parameters w where w.PARAMETER like '%CHARACTERSET%'

让我们先建一个实验表test_01：

create table test_01(
id int,
name nvarchar2(10),
remark varchar2(10),
primary key(id));

准备执行的SQL插入语句，大家可先预判下执行结果。

insert into test_01(id,name,remark) values(1,'离离原上草一岁一枯荣','离离原上草');


insert into test_01(id,name,remark) values(2,'离离原上草一岁一枯荣','离离原上草一');


insert into test_01(id,name,remark) values(3,'1234567890','离离原上草');


insert into test_01(id,name,remark) values(4,'12345678901','离离原上草');


insert into test_01(id,name,remark) values(5,'1234567890','1234567890');


insert into test_01(id,name,remark) values(6,'1234567890','12345678901');

第一句：insert into test_01(id,name,remark) values(1,'离离原上草一岁一枯荣','离离原上草');

成功，因为nvarchar2(10)与字符集无关，最多容纳10个汉字或英数字，'离离原上草一岁一枯荣'正好十个汉字，没有超过限度；而varchar2(10)最多容纳5个汉字或是十个英数字，'离离原上草'是五个汉字也没有超限，故成功插入。

执行结果：

第二句：insert into test_01(id,name,remark) values(2,'离离原上草一岁一枯荣','离离原上草一');

这一句remark里塞入了'离离原上草一'，这是六个汉字，相当于12个英数字，所以超限了，故插入失败。

执行结果，提示如下：

第三句：insert into test_01(id,name,remark) values(3,'1234567890','离离原上草');

这一句name里塞'1234567890'，这是正好十个英数字，没有超限；remark里塞'离离原上草'，这是五个汉字相当于十个英数字，也没有超限，故成功插入.

执行结果：

第四句：insert into test_01(id,name,remark) values(4,'12345678901','离离原上草');

这一句往name里塞'12345678901'，这段文本是十一个英数字，超过限度，故插入失败。

执行结果：

第五句：insert into test_01(id,name,remark) values(5,'1234567890','1234567890');

这一句往name和remark里塞的都是'1234567890'，这段文本是十位英数字，故不超限，能成功插入。

执行结果：

第六句：insert into test_01(id,name,remark) values(6,'1234567890','12345678901');

这一句name不超，但remark里塞'12345678901'，这是十一个英数字，已经超过，故插入失败。

执行结果：

最后留存表中数据：

SELECT * FROM test_01;

结论：

nvarchar2(MAX)不分字符集，规定多少个就是多少个，英语字母数字汉字平假名片假名都一视同仁，没有换算时乘二除二的麻烦；

varchar2(MAX) 区分字符集，能容纳MAX个英数字，只能容纳MAX/2个汉字，换算有点烦。

为省心计，最好文本字段都按nvarchar2类型来设定。

扩展：

大家都知道Oracle里有个仿佛是硬编码的字符串上限，即最多容纳4000个英数字，即varchar2(n)型字段，n的上限便是4000；nvarchar2(N)，N的上限便是4000/2=2000。

咱们可以验证一下nvarchar2型字段：

--新建一个测试表，设置nvarchar2(2001)
create table tb(
       id int,
       remark nvarchar2(2001),
       primary key(id));

执行结果提示：创建失败

--新建一个测试表，设置nvarchar2(2000)
create table tb(
       id int,
       remark nvarchar2(2000),
       primary key(id));

执行结果提示：创建成功

结论：这说明nvarchar2类型字段能容纳字符个数为2000个，对中英文都一样

再验证一下varchar2型字段：

--新建一个测试表，设置varchar2(4001)
create table tb2(
       id int,
       name varchar2(4001),
       remark nvarchar2(2000),
       primary key(id));

执行结果提示：：创建成功

--新建一个测试表，设置varchar2(4000)
create table tb2(
       id int,
       name varchar2(4000),
       remark nvarchar2(2000),
       primary key(id));

执行结果提示：创建成功

结论：这说明varchar2类型最多可容纳4000个英数字，合2000个汉字。

注意：

如果oracle配置如下：
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
说明，varchar2是按UTF8来存中文，一个常用中文字符占3个字节，所以varchar2（10）只能存下3个汉字，占用9个字节（因为这里面的10，是指字节的长度10，还有一种声明方式可以让varchar2(10 char)，可以让他存10个字符，占30个字节）
而nvarchar2默认为UTF16，一个常用中文字符占2个字节，所以nvarchar2（10）能存下10个汉字，占用20个字节（因为这里面的10，是指字符的长度10），

其实oracle规定了无论varchar2和nvarchar2，最大长度用字节来算，都是4000，所以oracle官网有句话：
The maximum column size allowed is 4000 characters when the national character set is UTF8 and 2000 when it is AL16UTF16.
意思是，如果NLS_NCHAR_CHARACTERSET设置为UTF8,那么你就能创建nvarchar2(4000)的字段。

文章参考资料：博客园-逆火狂飙—https://www.cnblogs.com/heyang78/p/15269468.html

oracle官网关于nvarchar类型的解释：https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch7progrunicode.htm#s

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

亲手做实验来感受nvarchar2和varchar2的区别

评论