When initializing DM database, there is a parameter LENGTH_IN_CHAR feels difficult to understand, so refer to the official documents and Study on the online blog
First look at the official documents for length_ IN_ Interpretation of char parameters
Whether the length of an object of type VARCHAR is in characters. Values: 1, Y means yes, 0, N means No. The default value is 0. Optional parameters.
1 or Y: Yes, the length of all VARCHAR type objects is in characters. In this case, the defined length is not really adjusted according to the character length, but the stored length value is enlarged according to the theoretical character length. Therefore, the actual number of characters that can be inserted exceeds the defined length, which is also allowed. At the same time, the upper limit of the stored byte length 8188 remains unchanged, that is, even if the column length is defined as 8188 characters, the total byte length occupied by the actually inserted string cannot exceed 8188;
0 or N: No, the length of all VARCHAR type objects is in bytes.
Test according to the default character set GB18030 first
select lengthb('in') from dual
union all
select lengthb('A') from dual
union all
select lengthb('z') from dual;
As a result of the above execution, in the character set GB18030, one Chinese character occupies 2 bytes and one English letter occupies 1 byte
When length_ IN_ When char = 0;
Use SQL to view the character set and length of the instance_ IN_ Char parameter value
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='LENGTH_IN_CHAR' OR PARA_NAME like '%CHARSET%';
Test table creation
create table test(c1 varchar(10));
Import test data
--Insert 10 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJ');
--Insert 11 English letters, execution failed, column length exceeds the definition
insert into test(c1) values('ABCDEFGHIJK');
--Insert 5 Chinese characters and execute successfully
insert into test(c1) values('Ha ha ha ha ha ha ha');
--Insert 6 Chinese characters, execution failed, column length exceeds the definition
insert into test(c1) values('Ha ha ha ha');
– check the number of bytes of inserted data
select *, lengthb(c1)
from test;
LENGTH_ IN_ When char = 1;
Use SQL to view the character set and length of the instance_ IN_ Char parameter value
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='LENGTH_IN_CHAR' OR PARA_NAME like '%CHARSET%';
Create test table
create table test(c1 varchar(10));
Import test data
--Insert 10 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJ');
--Insert 11 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJK');
--Insert 20 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJABCDEFGHIJ');
--Insert 21 English letters, execution failed, column length exceeds definition
insert into test(c1) values('ABCDEFGHIJABCDEFGHIJK');
--Insert 10 Chinese characters and execute successfully
insert into test(c1) values('Ha ha ha ha ha ha ha ha');
--Insert 11 Chinese characters, execution failed, column length exceeds the definition
insert into test(c1) values('Ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha');
--Insert 10 Chinese characters and 1 English letter. Execution failed. The column length exceeds the definition
insert into test(c1) values('Ha ha ha ha ha ha ha ha A');
– check the number of bytes of inserted data
select *, lengthb(c1)
from test;
Then test according to character set UTF-8
select lengthb('in') from dual
union all
select lengthb('A') from dual
union all
select lengthb('z') from dual;
As a result of the above execution, in the character set UTF-8, a Chinese character occupies 3 bytes and an English letter occupies 1 byte
LENGTH_ IN_ When char = 0;
Use SQL to view the character set and length of the instance_ IN_ Char parameter value
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='LENGTH_IN_CHAR' OR PARA_NAME like '%CHARSET%';
Create test table
create table test(c1 varchar(10));
Import test data
--Insert 10 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJ');
--Insert 11 English letters, execution failed, column length exceeds the definition
insert into test(c1) values('ABCDEFGHIJK');
--Insert 3 Chinese characters and execute successfully
insert into test(c1) values('Ha ha ha');
--Insert three Chinese characters and one English character. The execution is successful
insert into test(c1) values('Ha ha ha A');
--Insert 4 Chinese characters, execution failed, column length exceeds the definition
insert into test(c1) values('Ha ha ha ha');
--Insert 3 Chinese characters and 2 English letters. Execution failed. The column length exceeds the definition
insert into test(c1) values('Ha ha ha AB');
select *, lengthb(c1)
from test;
LENGTH_ IN_ When char = 1;
Use SQL to view the character set and length of the instance_ IN_ Char parameter value
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='LENGTH_IN_CHAR' OR PARA_NAME like '%CHARSET%';
Create test table
create table test(c1 varchar(10));
Import test data
--Insert 10 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJ');
--Insert 20 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJABCDEFGHIJ');
--Insert 40 English letters and execute successfully
insert into test(c1) values('ABCDEFGHIJABCDEFGHIJABCDEFGHIJABCDEFGHIJ');
--Insert 41 English letters, execution failed, column length exceeds the definition
insert into test(c1) values('ABCDEFGHIJABCDEFGHIJABCDEFGHIJABCDEFGHIJA');
--Insert 10 Chinese characters and execute successfully
insert into test(c1) values('Ha ha ha ha ha ha ha ha');
--Insert 13 Chinese characters and execute successfully
insert into test(c1) values('Ha ha ha ha ha ha ha ha oh oh');
--Insert 14 Chinese characters, execution failed, column length exceeds the definition
insert into test(c1) values('Ha ha ha ha ha ha ha ha ha oh oh oh oh');
--Insert 13 Chinese characters and 1 English letter, and the execution is successful
insert into test(c1) values('Ha ha ha ha ha ha ha ha oh oh A');
--Insert 13 Chinese characters and 2 English letters, failed to execute, and the column length exceeds the definition
insert into test(c1) values('Ha ha ha ha ha ha ha ha oh oh AB');
– check the number of bytes of inserted data
select *, lengthb(c1)
from test;
Summary:
UNICODE_FLAG=0
LENGTH_IN_CHAR=0
VARCHAR(10) can store 10 English letters, 5 Chinese characters, up to 10 bytes of data, and Chinese accounts for 2 bytes
UNICODE_FLAG=0
LENGTH_IN_CHAR=1
VARCHAR(10) can store 20 English letters, 10 Chinese characters, up to 20 bytes of data, and Chinese accounts for 2 bytes. It is automatically expanded to double the number of bytes in character units
UNICODE_FLAG=1
LENGTH_IN_CHAR=0
VARCHAR(10) can store 10 English letters, 3.3 Chinese characters, up to 10 bytes of data, and Chinese accounts for 3 bytes
UNICODE_FLAG=1
LENGTH_IN_CHAR=1
VARCHAR(10) can store 40 English letters, 13.3 Chinese characters, up to 40 bytes of data, Chinese accounts for 2 bytes, and is automatically expanded to 4 times the number of bytes in character units
Reference blog link: https://blog.csdn.net/fachu121/article/details/105290551/