Oracle varchar and varchar2____oracle

CHAR, VARCHAR, VARCHAR2 difference:
1. The length of the char is fixed, the length of the VARCHAR2 can vary, for example, the storage string "abc", for Char (20), which means that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) occupies a length of 3 bytes. 20 is only the maximum value when you store the characters less than 20 o'clock, stored by the actual length.
2. The efficiency of char is slightly higher than that of VARCHAR2.
3. At present varchar is synonymous with VARCHAR2. Industry-standard varchar types can store empty strings, but Oracle does not, although it retains the right to do so later. Oracle himself developed a data type VARCHAR2, which is not a standard varchar that stores null values in a database where varchar columns can store an empty string. Oracle recommends using VARCHAR2 instead of varchar if you want to be able to have backwards compatibility.
When to use char and when to use VARCHAR2.
Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations.
VARCHAR2 saves more space than Char, and is slightly less efficient than char, which means that if you want to be efficient, you have to sacrifice a certain amount of space, which is what we often say in database design, ' space for efficiency '.
VARCHAR2 saves space than Char, but if a VARCHAR2 column is often modified and the length of each modified data varies, this causes a ' row migration ' (row migration) phenomenon, which creates redundant I/O, which is avoided in database design and tuning. , it would be better to use char instead of VARCHAR2 in this case.

A Varchar,nvarchar,
All four types belong to variable-length character types, and the difference between varchar and VARCHAR2 is two bytes for all characters, the former is only two bytes for Chinese characters and full-width characters. The difference between nvarchar and nvarchar2 is the same as the above, which differs from the above in that it is a type of definition based on the Unicode standard and is typically used to support the definition of similar systems in multinational languages.

1.char

Char length is fixed, for example, you define char (20), even if you insert ABC, less than 20 bytes, the database will automatically add 17 spaces after ABC to complement 20 bytes;

Char is a distinction between Chinese and English, which occupies two bytes in char, and English is one, so char (20) You can only save 20 letters or 10 characters.

Char applies to a fixed length, generally excluding Chinese

2.varchar/varchar2

VarChar is not fixed in length, for example, you define varchar (20), and when you insert ABC, it takes up only 3 bytes in the database.

VarChar also distinguishes between Chinese and English, this same char.

VARCHAR2 is essentially equivalent to varchar, which is a non-industrial standard varchar defined by Oracle, except that VARCHAR2 NULL in place of varchar null string

The VARCHAR/VARCHAR2 is suitable for the length not fixed, generally does not contain the Chinese situation

3.nvarchar/nvarchar2

Nvarchar and nvarchar2 are not fixed in length.

Nvarchar does not distinguish between Chinese and English, for example: you define nvarchar (20), you can deposit 20 English letters/Chinese characters or a Chinese-English combination, this 20 defines the number of characters rather than the number of bytes

NVARCHAR2 is basically equivalent to nvarchar, the difference is that the nvarchar2 in the English alphabet also accounted for two bytes

NVARCHAR/NVARCHAR2 is suitable for storing Chinese

char [(N)]

Fixed length, non-Unicode character data, length n bytes. The value range of n is 1 to 8,000, and the storage size is n bytes.

varchar [(n | max)]

Variable length, non-Unicode character data. The value range of n is 1 to 8,000. Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the input data plus 2 bytes, which reflect the length of the stored data. The data entered can be 0 characters in length.

* Use char if the column data items are of the same size.
* Use varchar if the size of the column data items varies considerably.
* Use varchar (max) If the column data item size varies greatly and the size may exceed 8,000 bytes.

If the char or varchar data type does not specify N in a data definition or variable declaration statement, the default length is 1. If the char or varchar data type does not specify n when using the CAST and CONVERT functions, the default length is 30.
When a CREATE table or ALTER table is executed, a char column that is defined as a NULL is treated as varchar if SET ansi_padding is off.
In addition to help understand, for reference only: from http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197
can also refer to learning Http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=1
1.NULL value (null value).

The null value of a. Char column occupies storage space.

B. The null value of the Varcahr column does not occupy storage space.

C. Insert the same number of NULL values, the insertion efficiency of the varchar column is significantly higher than the Char column.
2. Inserting data

Char is significantly less efficient than varchar, regardless of whether the columns involved in the insertion data are indexed.

3. Update data

If no index is established on the updated column, the efficiency of char is lower than varchar, the difference is small, and indexing is more efficient.

4. Modify the structure

A. Whether the added or deleted column type is char or varchar, the operation can be completed faster, and there is no difference in efficiency.

B. For increasing the width of columns, there is a very significant efficiency difference between char and varchar, and modifying the Varcahr column does not take much time, and modifying the Char column takes a long time.

5. Data retrieval

The varchar type of data retrieval is slightly better than the char scan, regardless of whether or not it is indexed.

Select char or select varchar Suggestions

1. Conditions suitable for char:

A. The data length of each row in the column is basically consistent, and the length varies no more than 50 bytes;

B. Frequent data changes and less demand for data retrieval.

C. The length of the column does not change, and the value of modifying the width of the char type column is more expensive.

D. A large number of null values do not appear in the column.

E. There is no need to set up too many indexes on columns, and too many indexes have a greater impact on data changes in char columns.

2. Conditions suitable for varchar;

A. The length of each row of data in the column is quite different.

B. There are very few updates to the data in the column, but the queries are very frequent.
C. Often there is no data in the column, null value or null value

nchar [(N)]

N-Character fixed-length Unicode character data. The n value must be between 1 and 4,000 (inclusive). Storage size is twice times n bytes.

nvarchar [(n | max)]

Variable-length Unicode character data. n values are between 1 and 4,000 (inclusive). Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is twice times + 2 bytes of the number of characters entered. The data entered can be 0 characters in length.

Comments

If n is not specified in a data definition or variable declaration statement, the default length is 1. If n is not specified using the CAST function, the default length is 30.

If the size of the column data items may be the same, use nchar.

If the size of the column data items may vary widely, use nvarchar.

sysname is a system-supplied user-defined data type that is functionally identical to nvarchar (128) In addition to a nullable value. sysname is used to reference database object names.

objects that use nchar or nvarchar are assigned the default database collation, but you can use the COLLATE clause to assign a specific collation.

SET ansi_padding on always applies to nchar and nvarchar. SET ansi_padding off does not apply to nchar or nvarchar data types.

Char,nchar,varchar,varchar2,nvarchar2 these five types of differences in Oracle

difference between 1.CHAR (size) and varchar (size)
Char is a fixed-length field with a maximum length of 2K bytes;
VARCHAR is a variable length field with a maximum length of 4K bytes;

difference between 2.CHAR (size) and nchar (size)
Char if the number of letters for 1 bytes, storing GBK encoded Chinese characters stored 2 bytes, storing UTF-8 encoded Chinese characters occupy 3 bytes;
NCHAR defines the number of bytes occupied by the stored character according to the selected character set, typically holding one character (regardless of character or kanji) for 2 bytes.

difference between 3.VARCHAR (size) and VARCHAR2 (size)
In the current version, the two are indistinguishable; the maximum length is 4K bytes; The recommended use of VARCHAR2;

difference between 4.varchar2 (size) and NVARCHAR2 (size)
The maximum length is 4K bytes, which distinguishes the same char from the nchar; (NVARCHAR2 Max is 2K if the database character set length is 2)

5. Common characteristics
When the insert is executed, the value inserted is "," and then converted to NULL, insert ... values (') <=> insert ... values (NULL)
Search conditions must be null with where XX is

6. Examples
For example, there is a gender field, which holds "male, female" one of the values, two common choices
CHAR (2) and NCHAR (1)

--varchar,varchar2

Contact:
1.varchar/varchar2 used to store variable-length strings
such as varchar (20), stored in the string ' ABC ', the field in the database is only 3 bytes, not 20 bytes
The maximum value for 2.size is 4000, and the minimum value is 1, and its value indicates the number of bytes, such as
varchar (20) indicates that the maximum number of 20 bytes of content can be stored

Difference:
1.varchar2 all characters are accounted for two bytes (in general), varchar only for Chinese characters and Full-width and other characters accounted for two bytes, numbers, English characters, etc. are a byte;
2.VARCHAR2 the null string equivalent to NULL processing, and varchar still in accordance with the empty string processing;
3.varchar2 characters need to be stored in several bytes, looking at the character set used by the database,
For example GBK, Chinese characters will occupy two bytes, English one
If it is UTF-8, the Chinese character generally occupies 3 bytes, English or 1.
But in general, we think it is two byte processing, because the Oracle installation time by default we all choose GBK encoding format, but we are in the page to do the input string length of the check, or the maximum length of the database design field except 3来 as the maximum-----to prevent the database from transplanting when the different encoding format;
For example: VARCHAR2 (10), in general, the maximum storage of 5 characters, 10 characters

--nvarchar,nvarchar2
Contact:
1.nvarchar/nvarchar2 used to store variable-length strings
The maximum value for 2.size is 4000, and the minimum value is 1, and its value represents the number of characters, not the number of bytes
3. These two types are more suitable for storing Chinese

Difference:
1.nvarchar characters in Chinese are generally in the 2 byte calculation, English numerals, etc. according to one's own calculation
All characters in 2.NVARCHAR2 are calculated according to 2 bytes;
3.nvarchar2 Although more space, but it has better compatibility, all recommended use;

VARCHAR2 (size)

A variable-length string with a maximum length of size bytes, a maximum size of 4000, and a minimum of 1; You must specify a VARCHAR2 size;

NVARCHAR2 (size)

A variable-length string, based on the selected national character set, with a maximum length of size characters or bytes; The maximum size depends on the number of bytes required to store each character, with an upper limit of 4000; You must specify a NVARCHAR2 size;

Number (p,s)

A numerical value with a precision of p and a value range of S; the range of precision p is from 1 to 38; The range of the range S is from-84 to 127;
For example: Number (5,2) indicates that the integer part is the largest 3 digits, and the decimal part is 2 bits;
Number (5,-2) represents the integer part of the maximum of 7, where the reciprocal 2 digits of the integer are 0, and the preceding rounding.
Number means the default value, which is equivalent to number (5);

LONG

Variable-length character data with a length of up to 2G bytes;

DATE

Valid date range from January 1, 4712 to A.D. December 31, 4712

RAW (size)

Raw binary data of size byte, the maximum size is 2000 bytes; You must specify a size for raw;

LONG RAW

Raw binary data of variable length, which can reach up to 2G bytes;

CHAR (size)

Fixed-length character data whose length is size byte; The maximum size is 2000 bytes, while the minimum and default values are 1;

NCHAR (size)

is also a fixed length. Defined according to the Unicode standard

CLOB

A large object that can hold single-byte characters, a character set with unequal widths, and a maximum of 4G bytes

NCLOB

A large object that accommodates a single byte of character, does not support a character set of unequal widths, a maximum of 4G bytes, and stores a national character set

BLOB

A binary large object; maximum 4G bytes

BFILE

A locator that contains a large binary file that is stored outside the database, allowing the presence of an external LOB on the database server with character stream I/O, and a maximum size of 4G bytes.