oracle Oracle中的varchar和varchar2有什么区别？

What is the difference between varchar and varchar2 in Oracle?

提问by hrishi

What is the difference between varchar and varchar2?

varchar 和 varchar2 有什么区别？

回答by Quassnoi

As for now, they are synonyms.

至于现在，它们是同义词。

VARCHARis reserved by Oracleto support distinction between NULLand empty string in future, as ANSIstandard prescribes.

VARCHAR保留 byOracle以支持NULL将来区分和空字符串，如ANSI标准规定。

VARCHAR2does not distinguish between a NULLand empty string, and never will.

VARCHAR2不区分 aNULL和空字符串，永远不会。

If you rely on empty string and NULLbeing the same thing, you should use VARCHAR2.

如果您依赖空字符串并且NULL是同一件事，则应该使用VARCHAR2.

回答by Brian

Currently VARCHAR behaves exactly the same as VARCHAR2. However, the type VARCHARshould not be used as it is reserved for future usage.

目前 VARCHAR 的行为与 VARCHAR2 完全相同。但是，VARCHAR不应使用该类型，因为它保留供将来使用。

Taken from: Difference Between CHAR, VARCHAR, VARCHAR2

摘自：CHAR, VARCHAR, VARCHAR2之间的区别

回答by sandman

Taken from the latest stable Oracle production version 12.2: Data Types

取自最新的稳定 Oracle 生产版本 12.2： 数据类型

The major difference is that VARCHAR2is an internal data typeand VARCHARis an external data type. So we need to understand the difference between an internal and external data type...

主要区别在于VARCHAR2是内部数据类型和VARCHAR是外部数据类型。所以我们需要了解内部和外部数据类型之间的区别......

Inside a database, values are stored in columns in tables. Internally, Oracle represents data in particular formats known as internal data types.

在数据库中，值存储在表的列中。在内部，Oracle 以称为内部数据类型的特定格式表示数据。

In general, OCI (Oracle Call Interface) applications do not work with internal data type representations of data, but with host language data types that are predefined by the language in which they are written. When data is transferred between an OCI client application and a database table, the OCI libraries convert the data between internal data types and external data types.

通常，OCI（Oracle 调用接口）应用程序不使用数据的内部数据类型表示，而是使用由编写它们的语言预定义的宿主语言数据类型。当数据在 OCI 客户端应用程序和数据库表之间传输时，OCI 库会在内部数据类型和外部数据类型之间转换数据。

External types provide a convenience for the programmer by making it possible to work with host language types instead of proprietary data formats. OCI can perform a wide range of data type conversions when transferring data between an Oracle database and an OCI application. There are more OCI external data types than Oracle internal data types.

外部类型通过使使用宿主语言类型而不是专有数据格式成为可能，为程序员提供了便利。在 Oracle 数据库和 OCI 应用程序之间传输数据时，OCI 可以执行多种数据类型转换。OCI 外部数据类型多于 Oracle 内部数据类型。

The VARCHAR2data type is a variable-length string of characters with a maximum length of 4000 bytes. If the init.ora parameter max_string_size is default, the maximum length of a VARCHAR2can be 4000 bytes. If the init.ora parameter max_string_size = extended, the maximum length of a VARCHAR2can be 32767 bytes

的VARCHAR2数据类型是具有4000个字节的最大长度字符的可变长度的字符串。如果 init.ora 参数 max_string_size 为默认值，则 a 的最大长度VARCHAR2可以为 4000 字节。如果init.ora参数max_string_size=extended，a的最大长度VARCHAR2可以是32767字节

The VARCHARdata type stores character strings of varying length. The first 2 bytes contain the length of the character string, and the remaining bytes contain the string. The specified length of the string in a bind or a define call must include the two length bytes, so the largest VARCHARstring that can be received or sent is 65533 bytes long, not 65535.

的VARCHAR不同长度的数据类型存储字符串。前 2 个字节包含字符串的长度，其余字节包含字符串。在绑定或定义调用中指定的字符串长度必须包括两个长度字节，因此VARCHAR可以接收或发送的最大字符串长度为 65533 字节，而不是 65535。

A quick test in a 12.2 database suggests that as an internal data type, Oracle still treats a VARCHARas a pseudotypefor VARCHAR2. It is NOT a SYNONYMwhich is an actual object type in Oracle.

在12.2数据库快速测试表明，作为内部数据类型中，Oracle仍然把一个VARCHAR作为假对VARCHAR2。它不是SYNONYMOracle 中的实际对象类型。

SQL> select substr(banner,1,80) from v$version where rownum=1;
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    

SQL> create table test (my_char varchar(20));
Table created.

SQL> desc test
Name                 Null?    Type
MY_CHAR                       VARCHAR2(20)

There are also some implications of VARCHARfor ProC/C++ Precompiler options. For programmers who are interested, the link is at: Pro*C/C++ Programmer's Guide

VARCHAR对于 ProC/C++ 预编译器选项，还有一些含义。有兴趣的程序员，链接在：Pro*C/C++程序员指南

回答by Steve Chambers

After some experimentation (see below), I can confirm that as of September 2017, nothing has changed with regards to the functionality described in the accepted answer:-

经过一些实验（见下文），我可以确认，截至 2017 年 9 月，已接受的答案中描述的功能没有任何变化：-

1. Rextester demo for Oracle 11g:Empty strings are inserted as NULLs for both VARCHARand VARCHAR2.
2. LiveSQL demo for Oracle 12c:Same results.

1. Oracle 11g 的 Reextester 演示：空字符串作为NULLs插入sVARCHAR和VARCHAR2。
2. 适用于 Oracle 12c 的 LiveSQL 演示：结果相同。

The historical reason for these two keywords is explained well in an answer to a different question.

这两个关键字的历史原因在另一个问题的答案中得到了很好的解释。

回答by Ramkumar P

Currently, they are the same. but previously

目前，它们是相同的。但以前

1. Somewhere on the net, I read that,

1. 在网上的某个地方，我读到了，

VARCHARis reserved by Oracle to support distinction between NULLand empty string in future, as ANSI standard prescribes.

VARCHARNULL按照 ANSI 标准的规定，Oracle 保留了将来支持区分和空字符串的功能。

VARCHAR2does not distinguish between a NULLand empty string, and never will.

VARCHAR2不区分 aNULL和空字符串，永远不会。

1. Also,

2. 还，

Emp_name varchar(10)- if you enter value less than 10 digits then remaining space cannot be deleted. it used total of 10 spaces.

Emp_name varchar(10)- 如果您输入的值少于 10 位，则无法删除剩余空间。它总共使用了10个空格。

Emp_name varchar2(10)- if you enter value less than 10 digits then remaining space is automatically deleted

Emp_name varchar2(10)- 如果您输入的值少于 10 位，则剩余空间将被自动删除

回答by Palak Jain

1. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

2. If we declare datatype as VARCHAR then it will occupy space for NULL values. In the case of VARCHAR2 datatype, it will not occupy any space for NULL values. e.g.,

   name varchar(10)

1. VARCHAR 最多可以存储 2000 个字节的字符，而 VARCHAR2 最多可以存储 4000 个字节的字符。

2. 如果我们将数据类型声明为 VARCHAR，那么它将占用 NULL 值的空间。在 VARCHAR2 数据类型的情况下，它不会为 NULL 值占用任何空间。例如，

   name varchar(10)

will reserve 6 bytes of memory even if the name is 'Ravi__', whereas

即使名称是“Ravi__”，也会保留 6 个字节的内存，而

name varchar2(10) 

will reserve space according to the length of the input string. e.g., 4 bytes of memory for 'Ravi__'.

将根据输入字符串的长度预留空间。例如，'Ravi__' 的 4 字节内存。

Here, _ represents NULL.

这里，_代表NULL。

NOTE: varchar will reserve space for null values and varchar2 will not reserve any space for null values.

注意：varchar 将为空值保留空间，varchar2 不会为空值保留任何空间。

回答by Premraj

VARCHAR is the synonym of VARCHAR2. However, you should not use VARCHAR because Oracle may change its semantics in the future.

VARCHAR 是 VARCHAR2 的同义词。但是，您不应使用 VARCHAR，因为 Oracle 将来可能会更改其语义。