What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

最新推荐文章于 2024-10-20 09:42:55 发布

原创最新推荐文章于 2024-10-20 09:42:55 发布 · 184 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

本文详细解释了数据库中CHAR、VARCHAR及VARCHAR2三种字符类型的差异。CHAR适用于固定长度字符串，会使用空格填充至指定长度；VARCHAR目前行为与VARCHAR2相同，但不推荐使用；VARCHAR2用于存储变长字符串，按实际长度保存。

What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
Submitted by admin on Sat, 2005-11-26 08:30

Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:

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.

SQL> CREATE TABLE char_test (col1 CHAR(10));

Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

Note: ASCII character 32 is a blank space.

VARCHAR

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

SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR2

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

SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;

COL1 LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121