"Turn" CHAR CHARACTER VARCHAR NCHAR NVARCHAR NVARCHAR2 Difference

Source: Internet
Author: User

Http://blog.csdn.net/lhl6688/article/details/44156823?ref=myreadoracle provides five types of character data: char, nchar, varchar, VARCHAR2, Nvarchar2. Char: Uses the database character set to store data , fixed length, and automatically complements spaces if the stored data does not reach the specified length . When specifying the length, the unit of measure for the default length is determined by the Nls_length_semantics (default byte byte) parameter, but we can specify it manually as char or byte. Oracle recommends using Nls_length_semantics to specify units of measure, which can improve efficiency. The maximum storage length for a char type is 2000 bytes , and in Plsql, the maximum storage length can be 32,767 bytes . When using char, you can specify no maximum length, at which time the maximum length is 1. nchar: Using the national character set to store data, long -fixed, if the stored data does not reach the specified length, the database automatically complements the space . When specifying the length, char is used as the unit of measure and no other units can be specified manually. The maximum storage length is 2000 bytes , and in Plsql, the maximum storage length can be up to 32,767 bytes . When using nchar, you can specify no maximum length, at which point the maximum length is 1. varchar2: Use database character set to store data, variable length, if the stored data does not reach the specified length, does not automatically fill the space. You can use char,byte as the unit of measure, which is affected by the parameter nls_length_semantics by default. The maximum storage length is 4000 bytes, and in Plsql, the storage length can be up to 32767 bytes. The maximum length must be specified, and the minimum length is 1. nvarchar2: Using the national character set to store data, the length is variable, if the stored data does not reach the specified length, does not automatically complement the space. When specifying the length, char is used as the unit of measure and no other units can be specified manually. The maximum storage length is 4000 bytes, and in Plsql, the maximum storage length can be up to 32767 bytes. The maximum length must be specified, and the minimum length is 1. varchar: Oracle does not currently implement this data type, and in the current version, varchar is fully consistent with VARCHAR2, but does not guarantee that varchar will not be designed separately in the future.
Differentiate:
1. Char's length is fixed, while the length of the VARCHAR2 is able to Change, for example, to store the string "abc", for Char (20), which means that the characters you store will account for 20 bytes (including 17 null characters), while the same VARCHAR2 (20) occupies only 3 bytes in length, 20 is the maximum value, and when you store characters less than 20 o'clock, the actual length is stored.
2. Char's EfficiencyMore efficient than VARCHAR2. slightly higher
3. Now varchar is synonymous with VARCHAR2. The industry standard varchar type can store an empty string, but Oracle does not, although he reserves the right to do so later. Oracle has developed a data type of its own VARCHAR2, this type is not a standard varchar, he will be in the database varchar column can store the empty string attribute to storing null values。 If you want to have backward-compatible capabilities, Oracle It is recommended to use VARCHAR2 instead of varchar.

When should I use char and when should I use VARCHAR2?

Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations.
  VARCHAR2 saves space than Char and is slightly less efficient than char, that is, to achieve efficiency, we must sacrifice a certain amount of space, which is what we often say in database design, "space for efficiency."
Although VARCHAR2 is more space-saving than char, if a VARCHAR2 column is often modified and the length of each modified data is different, this can cause ' Row Migration' (Row migration) phenomenon, which causes redundant I/O, is to be avoided in database design and tuning, in which case it would be better to replace VARCHAR2 with Char. nvarcharDo not distinguish between Chinese and English, for example: you define nvarchar (20), you can deposit 20 English letters/kanji or Chinese and English combinations, this 20 defines the number of characters instead of the number of bytes

NVARCHAR2 is basically the same as nvarchar, except that the English alphabet, which is stored in nvarchar2, also accounts for two bytes .

Nvarchar/nvarchar2 for storing Chinese
Http://www.cnblogs.com/lovewife/articles/2467663.htmlhttp://www.cnblogs.com/iyangyuan/archive/2013/12/25/3491215.html

whether it is varchar2 or nvarchar2? , the maximum number of bytes is 4000 .

varchar2 (Byte): Is the default representation, such as we write: VARCHAR2 (100), which is equivalent to VARCHAR2 (byte), which indicates that the maximum number of bytes is , the field can hold up to bytes, emphasizing the size of the space . Since we are describing bytes, we should be careful when saving characters such as Chinese characters. If your database uses GBK encoding, then a Chinese character will occupy 2 bytes, up to 50 characters, if your database is UTF8 encoded, then a Chinese character will occupy 3 bytes, up to 33 characters.

varchar2 (char): Indicates the maximum number of characters,which can hold up to a maximum of four characters, emphasizing the number. Suppose we write Varchar2 (), then either the number, the letter, the Chinese character, all as a character, write up to 100, of course, the more Chinese characters, occupy more space, also follow the database coding principle above. For example: In a Chinese character, the bottom 2 or 3 bytes, deposited in a letter, accounting for 1 bytes, is definitely not some of the article said 1 letters or numbers also accounted for 2 or 3 bytes!

nvarchar2 (): No byte,char , similar to VARCHAR2 (char), except Nvarchar2 () block the database encoding, regardless of the encoding, NVARCHAR2 () A Chinese character occupies two bytes.

In practice, this is likely to be the case: VARCHAR2 (1400 char), we subjectively believe that this field can be no longer than 1400 characters, which means that we may be depositing 1399 characters, which seems to be the correct look.

However, if these 1399 characters are Chinese characters, the character length is not more than 1400, it looks normal, but in fact we lost a part of the data, why?

because 1399 a Chinese character, according to UTF8 code (99% project is UTF8 code it. ), need to occupy 1399*3=4197 bytes, and the article begins to say, no matter what char, the maximum length is 4000 bytes, one can not more, So the extra 197 bytes, will erase, and the whole process, without any error hints, your data will evaporate like this!

"Go" CHAR CHARACTER VARCHAR NCHAR NVARCHAR NVARCHAR2 difference

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.