Oracle Varchar2 length and PHP length judgment

Oracle database We are all familiar with the fact that there is a very common data type in the database: string type.

For the supposed type, there are three more commonly used types in Oracle: VARCHAR2 (Byte), varchar2 (char), NVARCHAR2 ().

So what's the difference between these three types?

First of all, we must always remember: 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 a maximum of four 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 that the maximum number of characters is , the field can hold up to characters, emphasizing the number of words. 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) , just Nvarchar2 () block the database encoding, regardless of the encoding, NVARCHAR2 () A Chinese character occupies two bytes.

General tutorial, that's it, but if you think about it one more step, you'll find a deadly problem.

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 kanji, press UTF8 code (99% items are UTF8 encoded bar. ), 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 more out of the 197 bytes will erase, and the whole process, without any error, your data will evaporate!

Therefore, for the GBK encoded database, security is written as: Varchar2 (2000 char), NVARCHAR2, for the UTF8 encoded database, security is written as: varchar2 (1333 char), NVARCHAR2 ( 2000).


Php

In PHP ,strlen and Mb_strlen are functions that seek the length of a string, but for some beginners it may not be clear what the difference is if you don't read the manual.
The difference between the two is explained in the following example.

First look at the example:

Results analysis: In the strlen calculation, the treatment of a UTF8 character is 3 length, so "Chinese a character 1 characters" length is 3*4+2=14, in the Mb_strlen calculation, the selected inner code is UTF8, will be a Chinese character as the length of the calculation, so "a character 1 characters" The length is 6.

The two functions can be combined to calculate the number of placeholders for a mixed Chinese-English string (a placeholder for a Chinese character is 2, the English character is 1)

For example, the strlen ($STR) value of the Chinese a character 1 character is 14,mb_strlen ($STR) value is 6, then you can calculate the placeholder for the "Chinese-a-word 1-character" is 10.

PHP built-in string length function strlen cannot handle Chinese strings correctly, it only gets the number of bytes that the string occupies. For the Chinese encoding of GB2312, strlen get the value is twice times the number of Chinese characters, and for UTF-8 encoded in Chinese, is 3 times times the difference (in UTF-8 encoding, a Chinese character accounted for 3 bytes).

Using Mb_strlen function can solve this problem well. The usage of Mb_strlen is similar to strlen, except that it has a second optional parameter for specifying character encoding. For example get UTF-8 string $str length, can be used Mb_strlen ($str, ' UTF-8 '). If you omit the second argument, the internal encoding of PHP is used. The internal code can be obtained through the mb_internal_encoding () function.

It is important to note that Mb_strlen is not a PHP core function and needs to be sure that the Php_mbstring.dll is loaded in the php.ini before use, that is, to ensure that the "Extension=php_mbstring.dll" line is present and not commented out. Otherwise, there is an issue with undefined functions.

Oracle Varchar2 length and PHP length judgment