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
