Oracle 数据类型：我应该使用 VARCHAR2 还是 CHAR

Oracle datatype: Should I use VARCHAR2 or CHAR

提问by Elim99

Should I use VARCHAR2 or CHAR as a datatype in Oracle?

我应该使用 VARCHAR2 还是 CHAR 作为 Oracle 中的数据类型？

It's been suggested to me to use CHAR for these new tables I need but I'm concerned since these new tables which will be used to populat existing tables that use a VARCHAR2 datatype. I'm concerned about extra spaces being placed in the VARCHAR2 fields and with comparison issues. I know there are ways to compare them using by trimming or converting them but I'm afraid it will make my code messy and buggy.

有人建议我将 CHAR 用于我需要的这些新表，但我很担心，因为这些新表将用于填充使用 VARCHAR2 数据类型的现有表。我担心在 VARCHAR2 字段中放置额外的空格以及比较问题。我知道有一些方法可以通过修剪或转换它们来比较它们，但我担心它会使我的代码变得混乱和错误。

What are your opinions?

你有什么意见？

回答by ivanatpr

I'm concerned about extra spaces being placed in the VARCHAR2 fields and with comparison issues. I know there are ways to compare them using by trimming or converting them but I'm afraid it will make my code messy and buggy.

我担心在 VARCHAR2 字段中放置额外的空格以及比较问题。我知道有一些方法可以通过修剪或转换它们来比较它们，但我担心它会使我的代码变得混乱和错误。

It's actually quite the opposite. Using CHAR will force your strings to be a fixed length by padding them with spaces if they're too short. So when comparing CHARs to regular strings in whatever app is using the data, that app would need to add a trim every time. In other words, VARCHAR2 is the choice that naturally leads to cleaner code.

实际上恰恰相反。如果字符串太短，则使用 CHAR 将通过用空格填充它们来强制您的字符串为固定长度。因此，在使用数据的任何应用程序中将 CHAR 与常规字符串进行比较时，该应用程序每次都需要添加修剪。换句话说，VARCHAR2 是自然导致更清晰代码的选择。

In general you should alwaysuse VARCHAR2, unless you have a very specific reason why you want a CHAR column.

通常，您应该始终使用 VARCHAR2，除非您有非常具体的原因为什么需要 CHAR 列。

If you're worried about strings that have extra spaces in the front or end, then there's a few options that come to mind:

如果您担心前面或末尾有多余空格的字符串，那么您可以想到以下几个选项：

• Make sure whatever process is doing the inserts does a trim on them before inserting.
• Add a check constraint on the column that ensures that string = trim(string).
• Add a before insert row-level trigger that does a trim on the strings as they get inserted.
• Make sure that you do a trim on the strings whenever you query the table

• 在插入之前，请确保插入的任何过程都对它们进行了修剪。
• 在列上添加一个检查约束，以确保 string = trim(string)。
• 添加一个 before insert 行级触发器，在插入字符串时对其进行修剪。
• 确保在查询表时对字符串进行修剪

回答by gavenkoa

Read:

读：

• CHAR versus VARCHAR2 Semantics (documentation)
• Should I use char or varchar2 (AskTom)

• CHAR 与 VARCHAR2 语义（文档）
• 我应该使用 char 还是 varchar2 (AskTom)

Quote from AskTom article:

引自 AskTom 文章：

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot "find" their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed.

CHAR/NCHAR 实际上只不过是变相的 VARCHAR2/NVARCHAR2，这一事实使我认为实际上只有两种字符串类型需要考虑，即 VARCHAR2 和 NVARCHAR2。我从未在任何应用程序中找到 CHAR 类型的用途。由于 CHAR 类型总是将结果字符串空白填充到固定宽度，我们很快发现它在表段和任何索引段中都消耗了最大的存储空间。这已经够糟糕了，但还有另一个重要的原因要避免 CHAR/NCHAR 类型：它们在需要检索此信息的应用程序中造成混乱（许多在存储后无法“找到”他们的数据）。其原因与字符串比较的规则及其执行的严格性有关。

回答by Shannon Severance

CHAR has interesting comparison semantics. If you only use VARCHAR2, then you do not need to learn the CHAR semantics. Honestly, I think if I had a field with a known fixed lenth, I would still define it as a VARCHAR2 and use a check constraint to enforce it's fixed lengthiness, instead of learning the CHAR comparison semantics.

CHAR 具有有趣的比较语义。如果只使用 VARCHAR2，则无需学习 CHAR 语义。老实说，我认为如果我有一个已知固定长度的字段，我仍然会将它定义为 VARCHAR2 并使用检查约束来强制它的固定长度，而不是学习 CHAR 比较语义。

Some will argue that CHARs are more efficient for fixed length data because the length does not need to be stored, but that is untrue on Oracle.

有些人会争辩说 CHAR 对于固定长度的数据更有效，因为不需要存储长度，但这在 Oracle上是不正确的。

回答by Volker Weber

Choose VARCHAR2(size)over CHAR(size), since this is more space and time efficient:

选择VARCHAR2(size)over CHAR(size)，因为这样更节省空间和时间：

Surprisingly or not, CHAR(size)allows assignment of strings with a length lenshorter than size. In this case, ORACLE appends size-lenspaces to the string for datatypes CHARand VARCHARand stores sizecharacters. The VARCHAR2datatype comes without padding, only lencharacters are stored.

无论是否令人惊讶，都CHAR(size)允许分配长度len小于size. 在这种情况下，ORACLE追加size-len空格的字符串数据类型CHAR和VARCHAR并存储size字符。该VARCHAR2数据类型来没有填充，只有len字符被存储。

CREATE TABLE Demo(col1 CHAR(4), col2 VARCHAR2(4));
INSERT INTO Demo (col1, col2) VALUES ('c', 'v');

As a result,

其结果，

col1='c '(padded with 3 trailing spaces, since the sizeof col1is 4and the length of 'c'is only 1). col1='c'evaluates FALSE, only TRIM(col1)='c'evaluates TRUE,

col1='c '（用 3 个尾随空格填充，因为sizeofcol1是4并且长度'c'仅为 1）。col1='c'评估 FALSE，只TRIM(col1)='c'评估 TRUE，

whereas

然而

col2='v'evaluates TRUE withoutTRIM(), making the comparison more efficient.

col2='v'在没有 的情况下评估 TRUE TRIM()，使比较更有效。

Furthermore, comparisons between two VARCHAR2values fail fast if their lengths differ (independent of their size). In this case, no character-wise comparisons are required. With CHARand same size, the length check always fails due to padding. Thus, every character has to be compared until the first character mismatch or the end-of-string has been reached, whichever occurs first.

此外，VARCHAR2如果两个值的长度不同（独立于它们的size），则两个值之间的比较很快就会失败。在这种情况下，不需要按字符进行比较。使用CHARand same size，由于填充，长度检查总是失败。因此，必须比较每个字符，直到第一个字符不匹配或到达字符串末尾，以先发生者为准。

Since both CHAR(size)and VARCHAR2(size)don't prevent assignments of values shorter than size, define a length constraint if you need to assure that only values with a predefined length (which should equal size) can be assigned.

由于CHAR(size)和VARCHAR2(size)都不会阻止分配短于 的值size，如果您需要确保只能size分配具有预定义长度（应等于）的值，请定义长度约束。

回答by Rajeev Shenoy

I would suggest that you stick to VARCHAR2.

我建议你坚持使用 VARCHAR2。

You should use CHAR when the data is a known fixed length.

当数据是已知的固定长度时，您应该使用 CHAR。