SQL 当 Oracle 中的 VARCHAR2 的大小声明为 1 个字节时，这意味着什么？

What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte?

提问by AndreaNobili

I know that I can declare a varchar2using the number of the characters that it should be able to contain.

我知道我可以varchar2使用它应该能够包含的字符数声明 a 。

However, in an Oracledatabase on which I am working, I found that a field (named PDF) is defined as follows:

但是，在Oracle我正在使用的数据库中，我发现一个字段（名为 PDF）的定义如下：

VARCHAR2(1 BYTE)

What does this mean? How many characters can it contain?

这是什么意思？它可以包含多少个字符？

Another, related question: What is the difference between a VARCHARand a VARCHAR2?

另一个相关问题： aVARCHAR和 a之间有什么区别VARCHAR2？

回答by Rene

You can declare columns/variables as varchar2(n CHAR) and varchar2(n byte).

您可以将列/变量声明为 varchar2(n CHAR) 和 varchar2(n 字节)。

n CHAR means the variable will hold n characters. In multi byte character sets you don't always know how many bytes you want to store, but you do want to garantee the storage of a certain amount of characters.

n CHAR 表示变量将包含 n 个字符。在多字节字符集中，您并不总是知道要存储多少字节，但您确实希望保证存储一定数量的字符。

n bytes means simply the number of bytes you want to store.

n 字节仅表示您要存储的字节数。

varchar is deprecated. Do not use it. What is the difference between varchar and varchar2?

varchar 已弃用。不要使用它。 varchar 和 varchar2 有什么区别？

回答by Wernfried Domscheit

The VARCHARdatatypeis synonymous with the VARCHAR2datatype. To avoid possible changes in behavior, always use the VARCHAR2datatype to store variable-length character strings.

该VARCHAR数据类型是同义VARCHAR2的数据类型。为避免可能的行为变化，请始终使用VARCHAR2数据类型来存储可变长度的字符串。

If your database runs on a single-byte character set (e.g. US7ASCII, WE8MSWIN1252or WE8ISO8859P1) it does not make any difference whether you use VARCHAR2(x BYTE)or VARCHAR2(x CHAR).

如果您的数据库在单字节字符集（例如US7ASCII,WE8MSWIN1252或WE8ISO8859P1）上运行，则使用VARCHAR2(x BYTE)或没有任何区别VARCHAR2(x CHAR)。

It makes only a difference when your DB runs on multi-byte character set (e.g. AL32UTF8or AL16UTF16). You can simply see it in this example:

只有当您的数据库在多字节字符集（例如AL32UTF8或AL16UTF16）上运行时才会有所不同。你可以简单地在这个例子中看到它：

CREATE TABLE my_table (
    VARCHAR2_byte VARCHAR2(1 BYTE), 
    VARCHAR2_char VARCHAR2(1 CHAR)
);

INSERT INTO my_table (VARCHAR2_char) VALUES ('');
1 row created.

INSERT INTO my_table (VARCHAR2_char) VALUES ('ü');
1 row created.

INSERT INTO my_table (VARCHAR2_byte) VALUES ('');
INSERT INTO my_table (VARCHAR2_byte) VALUES ('')
Error at line 10
ORA-12899: value too large for column "MY_TABLE"."VARCHAR2_BYTE" (actual: 3, maximum: 1)

INSERT INTO my_table (VARCHAR2_byte) VALUES ('ü')
Error at line 11
ORA-12899: value too large for column "MY_TABLE"."VARCHAR2_BYTE" (actual: 2, maximum: 1)

VARCHAR2(1 CHAR)means you can store up to 1 character, no matter how many byte it has. In case of Unicode one character may occupy up to 4 bytes.

VARCHAR2(1 CHAR)意味着您最多可以存储 1 个字符，无论它有多少字节。在 Unicode 的情况下，一个字符最多可占用 4 个字节。

VARCHAR2(1 BYTE)means you can store a character which occupies max. 1 byte.

VARCHAR2(1 BYTE)意味着您可以存储占用最大的字符。1 个字节。

If you don't specify either BYTEor CHARthen the default is taken from NLS_LENGTH_SEMANTICSsession parameter.

如果您未指定两者之一BYTE，CHAR则默认值取自NLS_LENGTH_SEMANTICSsession 参数。

Unless you have Oracle 12c where you can set MAX_STRING_SIZE=EXTENDEDthe limit is VARCHAR2(4000 CHAR)

除非您有 Oracle 12c，否则您可以将MAX_STRING_SIZE=EXTENDED限制设置为VARCHAR2(4000 CHAR)

However, VARCHAR2(4000 CHAR)does not mean you are guaranteed to store up to 4000 characters. The limit is still 4000 bytes, so in worst case you may store only up to 1000 characters in such field.

但是，VARCHAR2(4000 CHAR)这并不意味着您可以保证最多存储 4000 个字符。限制仍然是 4000字节，因此在最坏的情况下，您最多只能在此类字段中存储 1000 个字符。

See this example (in UTF-8 occupies 3 bytes):

看这个例子（在 UTF-8 中占用 3 个字节）：

CREATE TABLE my_table2(VARCHAR2_char VARCHAR2(4000 CHAR));

BEGIN
    INSERT INTO my_table2 VALUES ('');
    FOR i IN 1..7 LOOP
        UPDATE my_table2 SET VARCHAR2_char = VARCHAR2_char ||VARCHAR2_char;
    END LOOP;
END;
/

SELECT LENGTHB(VARCHAR2_char) , LENGTHC(VARCHAR2_char) FROM my_table2;

LENGTHB(VARCHAR2_CHAR) LENGTHC(VARCHAR2_CHAR)
---------------------- ----------------------
                  3840                   1280
1 row selected.


UPDATE my_table2 SET VARCHAR2_char = VARCHAR2_char ||VARCHAR2_char;

UPDATE my_table2 SET VARCHAR2_char = VARCHAR2_char ||VARCHAR2_char
Error at line 1
ORA-01489: result of string concatenation is too long

See also Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) (Doc ID 144808.1)

另请参阅BYTE 和 CHAR 语义用法的示例和限制 (NLS_LENGTH_SEMANTICS)（文档 ID 144808.1）

回答by Darshan Lila

To answer you first question:
Yes, it means that 1 byte allocates for 1 character. Look at this example

回答您的第一个问题：
是的，这意味着 1 个字节分配给 1 个字符。看这个例子

SQL> conn / as sysdba
Connected.
SQL> create table test (id number(10), v_char varchar2(10));

Table created.

SQL> insert into test values(11111111111,'darshan');
insert into test values(11111111111,'darshan')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


SQL> insert into test values(11111,'darshandarsh');
insert into test values(11111,'darshandarsh')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."V_CHAR" (actual: 12,
maximum: 10)


SQL> insert into test values(111,'Darshan');

1 row created.

SQL> 

And to answer your next one: The difference between varchar2and varchar:

并回答您的下一个：varchar2和之间的区别varchar：

1. VARCHARcan store up to 2000 bytesof characters while VARCHAR2can store up to 4000 bytesof characters.
2. If we declare datatype as VARCHARthen it will occupy space for NULL values, In case of VARCHAR2datatype it will notoccupy any space.

1. VARCHAR最多可以存储2000 bytesof个字符，而VARCHAR2最多可以存储4000 bytesof个字符。
2. 如果我们VARCHAR将数据类型声明为，那么它将占用空间NULL values，如果是VARCHAR2数据类型，它将not占用任何空间。

回答by thatjeffsmith

it means ONLY one byte will be allocated per character - so if you're using multi-byte charsets, your 1 character won't fit

这意味着每个字符只会分配一个字节 - 所以如果你使用多字节字符集，你的 1 个字符将不适合

if you know you have to have at least room enough for 1 character, don't use the BYTE syntax unless you know exactly how much room you'll need to store that byte

如果您知道必须至少有足够的空间容纳 1 个字符，请不要使用 BYTE 语法，除非您确切知道需要存储该字节的空间

when in doubt, use VARCHAR2(1 CHAR)

如有疑问，请使用 VARCHAR2(1 CHAR)

same thing answered here Difference between BYTE and CHAR in column datatypes

这里回答了同样的问题列数据类型中 BYTE 和 CHAR 之间的差异

Also, in 12c the max for varchar2 is now 32k, not 4000. If you need more than that, use CLOB

此外，在 12c 中，varchar2 的最大值现在是 32k，而不是 4000。如果您需要更多，请使用 CLOB

in Oracle, don't use VARCHAR

在 Oracle 中，不要使用 VARCHAR