Shulou(Shulou.com)06/01 Report--

Little X, let me ask you a question. I want to insert the value of another table into one table. Are all vachar2 () fields, with length () look, the original table of those values are less than 20, the target table is varchar2 (22), how can the error insert failure, exceeding the maximum value?

Director, is it the same library? How do you judge the length of the value?'

'it 's the same library, using length ()'

Hearing this, I wonder if members understand the reason.

With regard to length, there are two functions that can be easily confused, lengthb () and length ().

Length () represents the number of characters.

Lengthb () represents the number of bytes.

For example, in varchar2 (), "abc" counts as three characters, and "Alfa" counts as three characters.

"abc" has three bytes, but "Alfa" is not necessarily.

Examples are as follows:

Select a.*, length (a), lengthb (a) from test1 a

As shown above, the number of characters is not equal to the number of bytes!

In the above database, one English character is 1 byte and one Chinese character is 3 bytes. Among the values taken by the customer, there are a lot of Chinese, which is why the maximum value is exceeded.

Expand 1

Different character sets, the length of Chinese characters is different, even the length of English will be different.

For example, take the common UTF16,UTF8,GBK as an example, the English length is 4 bytes, 1 byte, 1 byte respectively, while the Chinese length is 4 bytes, 3 bytes, 2 bytes. It should be noted that the larger the number of bytes, the more space it takes up to store the same data, and the more waste it causes.

Expansion 2

In oracle, there are two character sets that always appear in pairs, varchar2 () and char (). The difference between them is that char () is automatically completed and fixed in length, while varchar2 () is of variable length. Examples are as follows:

Create table table_char (a char (20))

Create table table_varchar2 (a varchar2 (20))

Insert into table_char values ('hello')

Insert into table_char values ('Hello')

Insert into table_char values ('Hello')

Insert into table_varchar2 values ('hello')

Insert into table_varchar2 values ('Hello')

Insert into table_varchar2 values ('Hello')

Table_char is as follows

Table_varchr2 is as follows:

As you can see, the byte length of char is always 20, and even if the real length is not enough, it will be completed with spaces. Therefore, varchar2 () generally saves more space than char (). But char also has its advantages. Generally speaking, the efficiency of char () is higher than that of vharchar2 (), which is often said to trade space for time.

In addition, due to the variable length of varchar2 (), row migration may occur during modification, affecting the IO of the database, so, generally speaking, columns that do not modify or modify very few columns with a uniform length are recommended to be set to char () during the business design period, such as ID card number, bank card number and so on.

A little thinking

I don't know if you have found it, but in the screenshot of table_char, length (a) is 20 ~ (th) and 16 ~ (12) respectively. Why? The answer will not be announced here. You can think about it a little bit.

Expansion 3

In fact, as far as varchar2 () is concerned, it can be specified by the number of characters or byte size, as shown in the following example:

By default, it is varchar2 (X byte), and this byte can be omitted. As you can see, when specifying byte, inserting a Chinese language is failed, while when specifying char, inserting a Chinese language is successful. To tell you the truth, it is rare to use a specified char at present.

Create table varchar_byte (a varchar2 (1 byte))

Create table varchar_char (a varchar2 (1 char))

Insert into varchar_byte values ('good')

Insert into varchar_char values ('good')

For both, the maximum length is 4000, namely varchar2 (4000 char) and varchar2 (4000 byte), so varchar2 (4000 char) > = varchar2 (4000).

Coincidentally, a customer has set a parameter NLS_LENGTH_SEMANTICS in the test environment, if set to byte, then varchar2 () defaults to bytes, if set to char, then the default becomes char, the customer sets char in the test environment, and the setting is particularly large, resulting in the same program running through the test library, but the maximum value reported in the production library is insufficient. This needs to be noted.

Summary

From this point of view, in fact, the problem of the customer is very simple. In the process of learning the database, the most important thing is to draw lessons from the example. We must think more and try more, so that we can take our time when we encounter problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.