聊聊 oracle varchar2 字段的gbk/utf8编码格式和字段长度问题

聊聊 oracle varchar2 字段的gbk/utf8编码格式和字段长度问题

1 问题现象

最近在排查某客户现场的数据同步作业报错问题时，发现了部分 ORACLE 表的 varchar2 字段，因为上游 ORACLE数据库采用 GBK 编码格式，而下游 ORACLE 数据库采用UTF8 编码格式，导致部分包含中文的字段在插入下游是，因为长度问题导致插入失败，报错信息类似“ORA-12899: value too large for column “dbName”.“tableName”.“colName” (actual: xxx, maximum: yyy)”。
以下是详细信息。

2 问题原因

• 上游数据库中 ORACLE 使用了 GBK 编码，而下游 ORACLE 中使用了 UTF8 编码；在 GBK 编码格式下，每个中文字符占两个字节，而在 UTF8 编码格式下，每个中文字符占三个字节；（当然两种编码格式下，英文字符都是占1个字节）；
• 在ORACLE 中通过DDL 声明 VARCHAR2 可变长度的字符串类型字段时，必须指定字段的最大长度，而长度的单位可以是字节也可以是字符，当不指定长度单位时，其默认值跟 session 级别的参数 nls_length_semantics 有关，该参数一般默认配置为字节；
• 由于声明下游表 VARCHAR2 字段时没有指定长度单位，采用的是类似” remark VARCHAR2(2000)” 的格式，所以实际最大长度的单位是字节；如果最大长度设计不当，从上游同步包含中文字符的数据时，就可能报上述错；

3 问题解决

• 从技术角度，建表语句也可以优化下：为确保 DDL 中指定的 VARCHAR2 字段的最大长度的语义的一致性，而不依赖 session 级别的参数 nls_length_semantics，DDL 语句中可以显示指定长度单位，比如 VARCHAR2(size BYTE) 或 VARCHAR2(size CHAR])；
• 相关微服务负责人梳理排查下是否需要扩充下游 ORACLE VARCHAR2 字段的长度；

4 技术背景

• 可以使用 length( ) 函数查看 VARCHAR2 字段的实际长度，此时其返回值代表的实际存储的字符的个数（每个中文和英文字符都算1个字符），而不是字节数；
• 可以使用 lengthb( ) 函数查看 VARCHAR2 字段实际存储占用的字节数；
• 在 GBK 编码格式下，每个中文字符占两个字节，而在 UTF8 编码格式下，每个中文字符占三个字节（当然两种编码格式下，英文字符都是占1个字节）；
• Oracle LENGTH( ) function can be defined as a function which is used to calculate the length of a string and it measures the length of the string in characters (A single string is made of many characters and the data type of the string expression can be VARCHAR2, CHAR, NCHAR, CLOB or NCLOB) as defined by the input character set and it returns a positive value upon execution which is a integer representing the number of characters present in the string.
• V$SYSTEM_PARAMETER displays information about the initialization parameters that are currently in effect for the instance. A new session inherits parameter values from the instance-wide values.
• V$PARAMETERdisplaysinformationabouttheinitializationparametersthatarecurrentlyineffectforthesession.Anewsessioninheritsparametervaluesfromtheinstance-widevaluesdisplayedbytheV$SYSTEM_PARAMETER view.

5 相关示例SQL

select name,value from V$PARAMETER where name='nls_length_semantics';
SELECT cust_id,length(cust_id),lengthb(cust_id) FROM test2 ORDER BY lengthb(cust_id) desc ;
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'BYTE';
SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';
-- to check v$parameter or v$SYSTEM_PARAMETER, for normal users，they need ask the admin to grant them below access rights, or they might get erros like ORA-00942: table or view does not exist
GRANT SELECT_CATALOG_ROLE to xx;
GRANT SELECT ANY DICTIONARY to xx;
INSERT INTO xxx (CUST_ID,ORDINAL,ORGAN_FLAG,CLIENT_NAME,CLIENT_GENDER,FULL_NAME,ID_KIND,ID_NO) VALUES ('李明李明明',0,'0','姓名31288580000004726','0','wuhl','0','110101199003077117');