Varchar2 traps in oracle databases

Oracle databases are familiar to everyone. There is a very common data type in the database: string type.

There are three common types in oracle: varchar2 (byte), varchar2 (char), and nvarchar2 ().

So what are the differences between the three types?

First, we need to remember:Whether it isVarchar2Or nvarchar2The maximum number of bytes is 4000..

Varchar2 (byte): Is the default representation. For example, if we write it as varchar2 (100), it is equivalent to varchar2 (100 byte ),Indicates that the maximum number of bytes is100This field can contain up to 100Bytes, emphasizing the size of space. Because we describe bytes, we must be careful when saving Chinese characters and other characters. If your database uses GBK encoding, a Chinese character occupies 2 bytes and can store up to 50 Chinese characters. If your database uses UTF8 encoding, A Chinese Character occupies three bytes and can store up to 33 Chinese characters.

Varchar2 (char): Indicates that the maximum number of characters is 100.This field can contain up to 100Characters, emphasizing the number of characters.Assume that we are writing varchar2 (100 char), so no matter it is a number, letter, Chinese character, it is regarded as a single character, a maximum of 100 write, of course, the more Chinese characters, the larger the space occupied, the above database encoding principles are also followed. For example, if a Chinese character is saved, the bottom layer occupies 2 or 3 bytes, and a letter occupies 1 byte,Definitely not in some articles12 letters or numbersOr 3Bytes!

Nvarchar2 (): No byte, CharSimilar to varchar2 (char), But nvarchar2 ()No matter what encoding, nvarchar2 ()Each Chinese Character occupies two bytes.

This is the general tutorial, but if you think more, you will find a fatal problem.

In practice, it is very likely that this writing method will appear: varchar2 (1400 char). We believe that this field cannot exceed 1400 characters at most, this means that we may store 1399 characters, which seems to be correct.

However, if these 1399 characters are all Chinese characters and the length does not exceed 1400, it seems that everything is normal, but we actually lose part of the data. Why?

Because 1399Chinese characters, press UTF8Encoding (99%All projects are UTF8Encoding ..), Requires 1399*3 = 4197In the beginning of this article, no matter what charThe maximum length is 4000.One byte cannot be more than one, so the additional 197Bytes will be erased, and there is no error prompt throughout the process, so your data will evaporate!

Therefore, for GBK-encoded databases, the safe syntax is varchar2 (2000 char) and nvarchar2 (2000). For UTF8-encoded databases, the safe syntax is: varchar2 (1333 char), nvarchar2 (2000 ).

Check your database!

The level of cooking is limited. Do not spray it. Please contact me ~~~