SQL Varchar2 和 char 之间的主要区别是什么

What is the major difference between Varchar2 and char

提问by ram12393

Creating Table:

创建表：

CREATE TABLE test (
charcol    CHAR(10),
varcharcol VARCHAR2(10));

SELECT LENGTH(charcol), LENGTH(varcharcol) FROM test;

Result:

结果：

LENGTH(CHARCOL) LENGTH(VARCHARCOL)
--------------- ------------------
             10                  1 

Please Let me know what is the difference between Varchar2 and char? At what times we use both?

请让我知道 Varchar2 和 char 有什么区别？我们什么时候使用两者？

采纳答案by Wernfried Domscheit

Simple example to show the difference:

显示差异的简单示例：

SELECT 
    '"'||CAST('abc' AS VARCHAR2(10))||'"', 
    '"'||CAST('abc' AS CHAR(10))||'"' 
FROM dual;


'"'||CAST('ABC'ASVARCHAR2(10))||'"' '"'||CAST('ABC'ASCHAR(10))||'"'
----------------------------------- -------------------------------
"abc"                               "abc       "                   
1 row selected.

The CHAR is usefull for expressions where the length of charaters is always fix, e.g. postal code for US states, for example CA, NY, FL, TX

CHAR 对于字符长度总是固定的表达式很有用，例如美国各州的邮政编码，例如 CA、NY、FL、TX

回答by William Robertson

This is an old thread, but it just came top of a Google search for 'Oracle char vs varchar2', and while there are already several answers correctly describing the behaviour of char, I think it needs to be said that you should not use it except in two specific situations:

这是一个旧线程，但它只是在谷歌搜索'Oracle char vs varchar2' 的顶部，虽然已经有几个正确描述了 行为的答案char，但我认为需要说的是你不应该使用它，除非在两种特定情况下：

1. You are building a fixed-length file or report, and assigning a non-null value to a charavoids the need to code an rpad()expression. For example, if firstnameand lastnameare both defined as char(20), then firstname || lastnameis a shorter way of writing rpad(firstname,20) || rpad(lastname,20).
2. You need to distinguish between the explicit empty string ''and null. Normally they are the same thing in Oracle, but assigning ''to a charvalue will trigger its blank-padding behaviour while nullwill not, so if it's important to tell the difference, and I can't really think of a reason why it would be, then you have a way to do that.

1. 您正在构建一个固定长度的文件或报告，并且将非空值分配给 achar避免了对rpad()表达式进行编码的需要。例如，如果firstname和lastname都定义为char(20)，则firstname || lastname是一种更短的写法rpad(firstname,20) || rpad(lastname,20)。
2. 您需要区分显式空字符串''和null. 通常它们在 Oracle 中是一样的，但是分配''给一个char值会触发它的空白填充行为而null不会，所以如果区分差异很重要，我真的想不出为什么会这样，那么你有办法做到这一点。

There is really no reason to use charjust because some length is fixed (e.g. a Y/Nflag or an ISO currency code such as 'USD'). It's not more efficient, it doesn't save space (there's no mythical length indicator for a varchar2, there's just a blank padding overhead for char), and it doesn't stop anyone entering shorter values. (If you enter 'ZZ'in your char(3)currency column, it will just get stored as 'ZZ '.) It's not even backward-compatible with some ancient version of Oracle that once relied on it, because there never was one.

真的没有理由char仅仅因为某些长度是固定的（例如Y/N标志或 ISO 货币代码，例如'USD'）而使用。它不是更有效，它不节省空间（ a 没有神话般的长度指示器varchar2，只有一个空白填充开销char），并且它不会阻止任何人输入更短的值。（如果您输入'ZZ'您的char(3)货币列，它只会被存储为'ZZ '。）它甚至与曾经依赖它的某些古老版本的 Oracle 不向后兼容，因为从来没有一个。

And the contagion can spread, as (following best practice) you might anchor a variable declaration using something like sales.currency%type. Now your l_sale_currencyvariable is a stealth charwhich will get invisibly blank-padded for shorter values (or ''), opening the door to obscure bugs where l_sale_currencydoes not equal l_refund_currencyeven though you assigned 'ZZ'to both of them.

并且传染可以传播，因为（遵循最佳实践）您可能会使用类似sales.currency%type. 现在，您的l_sale_currency变量是一个隐身变量char，对于较短的值（或''），它会被无形地填充空白，从而打开了隐藏错误的大门，即使您分配给它们l_sale_currency也不相等。l_refund_currency'ZZ'

CHARwas introduced in Oracle 6 for, I'm sure, ANSI compatibility reasons. Probably there are potential customers deciding which database product to purchase and ANSI compatibilityis on their checklist (or used to be back then), and CHARwith blank-padding is defined in the ANSI standard, so Oracle needs to provide it. You are not supposed to actually use it.

CHAR我敢肯定，这是出于 ANSI 兼容性原因在 Oracle 6 中引入的。可能有潜在客户决定购买哪种数据库产品，并且ANSI 兼容性在他们的清单上（或曾经是），并且CHAR在 ANSI 标准中定义了空白填充，因此 Oracle 需要提供它。你不应该实际使用它。

回答by Anddo

Just to avoid confusion about much wrong information. Here are some information about difference including performance

只是为了避免混淆许多错误信息。以下是一些有关差异的信息，包括性能

Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

参考：https: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 2668391900346844476

Since a char is nothing more than a VARCHAR2 that is blank padded out to the maximum length - that is, the difference between the columns X and Y below:

create table t ( x varchar2(30), y char(30) ); insert into t (x,y) values ( rpad('a',' ',30), 'a' );

IS ABSOLUTELY NOTHING, and given that the difference between columns X and Y below:

insert into t (x,y) values ('a','a')

is that X consumes 3 bytes (null indicator, leading byte length, 1 byte for 'a') and Y consumes 32 bytes (null indicator, leading byte length, 30 bytes for 'a ' )

Umm, varchar2 is going to be somewhat "at an advantage performance wise". It helps us NOT AT ALL that char(30) is always 30 bytes - to us, it is simply a varchar2 that is blank padded out to the maximum length. It helps us in processing - ZERO, zilch, zippo.

Anytime you see anyone say "it is up to 50% faster", and that is it - no example, no science, no facts, no story to back it up - just laugh out loud at them and keep on moving along.

There are other "made up things" on that page as well, for example:

"Searching is faster in CHAR as all the strings are stored at aspecified position from the each other, the system doesnot have tosearch for the end of string. Whereas in VARCHAR the system has tofirst find the end of string and then go for searching."

FALSE: a char is just a varchar2 blank padded - we do not store strings "at a specified position from each other". We do search for the end of the string - we use a leading byte length to figure things out.

由于 char 只不过是一个空白填充到最大长度的 VARCHAR2 - 即下面 X 和 Y 列之间的差异：

创建表 t ( x varchar2(30), y char(30) ); 插入 t (x,y) 值（ rpad('a',' ',30), 'a' ）；

绝对没有，并且考虑到下面 X 列和 Y 列之间的差异：

插入 t (x,y) 值 ('a','a')

是 X 消耗 3 个字节（空指示符，前导字节长度，'a' 为 1 个字节），Y 消耗 32 个字节（空指示符，前导字节长度，'a' 为 30 个字节）

嗯，varchar2 将在某种程度上“在性能方面具有优势”。char(30) 总是 30 字节，这对我们有帮助——对我们来说，它只是一个填充空白到最大长度的 varchar2。它帮助我们处理 - ZERO、zilch、zippo。

任何时候你看到有人说“它快了 50%”，就是这样 - 没有例子，没有科学，没有事实，没有故事支持 - 只是对他们大声笑并继续前进。

该页面上还有其他“编造的东西”，例如：

“在 CHAR 中搜索更快，因为所有字符串都存储在彼此的指定位置，系统不必搜索字符串的结尾。而在 VARCHAR 中，系统必须先找到字符串的结尾，然后再查找搜索。”

错误：char 只是一个 varchar2 空白填充 - 我们不会“在彼此的指定位置”存储字符串。我们确实搜索字符串的结尾 - 我们使用前导字节长度来解决问题。

回答by Wolf

CHARtype has fixed size, so if you say it is 10 bytes, then it always stores 10 bytes in the database and it doesn't matter whether you store any text or just empty 10 bytes

CHAR类型有固定的大小，所以如果你说它是 10 个字节，那么它总是在数据库中存储 10 个字节，无论你存储任何文本还是空 10 个字节都没有关系

VARCHAR2size depends on how many bytes you are actually going to store in the database. The number you specify is just the maximum number of bytes that can be stored (although 1 byte is minimum)

VARCHAR2大小取决于您实际要在数据库中存储多少字节。您指定的数字只是可以存储的最大字节数（尽管 1 个字节是最小的）

You should use CHAR when dealing with fixed length strings (you know in advance the exact length of string you will be storing) - database can then manipulate with it better and faster since it knows the exact lenght

在处理固定长度的字符串时，您应该使用 CHAR（您事先知道将要存储的字符串的确切长度）——然后数据库可以更好更快地操作它，因为它知道确切的长度

You should use VARCHAR2 when you don't know the exact lenght of stored strings.

当您不知道存储字符串的确切长度时，您应该使用 VARCHAR2。

Situation you would use both may be:

您将同时使用两者的情况可能是：

name VARCHAR2(255),
zip_code CHAR(5) --if your users have only 5 place zip codes

回答by Shree

CHAR

字符

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

CHAR 应该用于存储固定长度的字符串。字符串值在存储到磁盘之前将被空间/空白填充。如果用这种类型来存储可变长度的字符串，会浪费大量的磁盘空间。

VARCHAR2

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

VARCHAR2 用于存储变长字符串。字符串值的长度将与值本身一起存储在磁盘上。

And

和

At what times we use both?

Its all depend upon your requirement.

这一切都取决于您的要求。

回答by Rahul Tripathi

CHARis used for storing fix length character strings. It will waste a lot of disk space if this type is used to store varibale length strings.

CHAR用于存储定长字符串。如果使用这种类型来存储可变长度的字符串，会浪费大量磁盘空间。

VARCHAR2is used to store variable length character strings.

VARCHAR2用于存储变长字符串。

At what times we use both?

我们什么时候使用两者？

This may vary and depend on your requirement.

这可能会有所不同，具体取决于您的要求。

EDIT:-

编辑：-

Lets understand this with an example, If you have an student name column with size 10; sname CHAR(10)and If a column value 'RAMA'is inserted, 6 empty spaceswill be inserted to the right of the value. If this was a VARCHAR column; sname VARCHAR2(10).Then Varchar will take 4 spaces out of 10 possible and free the next 6 for other usage.

让我们通过一个例子来理解这一点，如果你有一个大小为 10 的学生姓名列；snameCHAR(10)和 如果'RAMA'插入列值，则值右侧将插入6 个空格。如果这是一个 VARCHAR 列；snameVARCHAR2(10).然后 Varchar 将占用 10 个空格中的 4 个，并释放接下来的 6 个用于其他用途。