【ORACLE】对Oracle中char类型的研究分析

前言

在国产数据库做ORACLE兼容性实现时，无可避免会遇到CHAR类型的差异，但是对于大多数的应用开发者来说，并不会深入去了解char类型的机制，之前在ORACLE上就没弄懂，好不容易写出来的SQL在ORACLE能查到想要的东西了，但是一迁移到国产库的时候，发现相同的SQL，查询结果不一样了，可能是查到的数据变多了，也可能是查到的数据变少了，甚至排序结果都出现了不一致。
虽然，在ORACLE官方文档中，就有这个字符串比较的说明
## Datatype Comparison Rules
但是空对着规则看，没有实际的场景，想象不到实际的复杂度。
所以本文通过实验来分析一下，ORACLE的CHAR在各种场景下，到底是怎么处理的。

分析

一、基本原理

ORACLE里执行

CREATE TABLE test_a(a char(10));
INSERT INTO test_a VALUES ('a');
SELECT * FROM test_a WHERE a='a'; --查得到
SELECT * FROM test_a WHERE a=rpad('a',10); --查得到
SELECT * FROM test_a WHERE a=rpad('a',9); --查不到

第一个查询和第二个查询的特殊点在于，
一个补了空格，一个没补空格，
第三个查询同样是补了空格，但查不到，所以这并不意味着ORACLE是把空格都去掉再进行比较,也不是自动补充空格再比较。

‘a’ 和 rpad(‘a’,10) 的差别在于 ，前者是 char类型，后者是varchar2类型，因此：
第一条sql char = char
第二条sql char = varchar

根据ORACLE官方文档说，ORACLE对于 char=char ，它是把短的一侧的空格补到和长的一侧一样（不能直接按一列补，只能在每个值比较时，临时去补）。
对于 char =varchar ,则是将char 的空格保留，转换成varchar ，再进行判断。
可以用以下方式验证

SELECT * FROM test_a WHERE a = cast (rpad('a',9) as char(9)); --查得到
SELECT * FROM test_a WHERE a = cast('a' as varchar(10)); --查不到

但是这里再在C语言里处理有个问题，去掉空格的方式不会增加内存，原地处理即可；但增加空格则对应的内存可能存不下，需要申请新的内存再把字符串复制过去，由此会带来额外的内存开销，从而影响性能。

二、隐式转换不走索引？

但是此时存在一个问题，如果char列上是个索引，转换成varchar也是属于类型转换，此时索引应该走不到，但实际还是走到了索引，因此这里存了一个疑点：

对于OG系数据库， ‘a’ 则是unkonwn 类型，当它没有操作符或者函数使用时，默认会选择识别为text类型，而ORACLE则是char；当有操作符时，会选择识别为 操作符另一侧的类型, 比如 char=unknown 就识别为 char=char，虽然原理不一样，但此场景的结果和ORACLE是保持一致的。

如果是 char = varchar 时，则会将char转换成 varchar，并且默认没有保留空格，因此a=rpad(‘a’,10) 会查不到数据，形成和ORACLE的查询结果差异，并且此时会导致走不了char列的索引

所以，假设我们用char往varchar转换时是否带空格的方式去看差异(set behavior_compat_options =char_coerce_compat)，问题就会形成一个死循环:
可以通过保留空格的方式保证和ORACLE结果一致，但是会导致无法走索引；假设把varchar转换成char再去对比，又会导致和ORACLE的查询结果不一致。

所以，经过以上分析，在ORACLE中对于 char =varchar ，可能并不是真的把空格保留再进行判断，我进行了一个大胆的猜想，由于char列是有固定长度的，因此在判断的时候，它极有可能，是先判断两边的长度，如果长度不对就直接false掉了，如果长度对，就将varchar(n)转换成char(n) ，再进行比较。为什么要考虑长度？因为varchar这边可能自带空格，如果不考虑长度就直接转，可能会导致应该不相等的就变成了相等。

于是，理论上在OG数据库中创建下面这样的操作符，此场景在 仅考虑 “=” 的情况下，可以保持和ORACLE一致(这里暂不扩展其他操作符的情况)

create function pg_catalog.bpchar_text_eq (bpchar,text) returns bool IMMUTABLE AS
$$select ($1=$2::bpchar and length($1)=length($2));$$language sql;
CREATE OPERATOR = ( LEFTARG = bpchar, RIGHTARG = text, PROCEDURE = bpchar_text_eq);

create function pg_catalog.bpchar_unknown_eq (bpchar,unknown) returns bool IMMUTABLE AS
$$select $1=$2::bpchar;$$language sql;

CREATE OPERATOR = ( LEFTARG = bpchar, RIGHTARG = unknown, PROCEDURE = bpchar_unknown_eq);

三、函数和操作符

‘111’||‘222’ 这种在oracle是返回char类型，但是OG中返回的是TEXT，如果把这个表达式用于where条件判断里，可能会找不到数据。
此处在OG中可以创建一个这样的操作符来实现类似ORACLE的效果

create function unknowncat(unknown,unknown) returns bpchar immutable as
$$select concat($1,$2)::bpchar;$$language sql;

create operator ||(LEFTARG=unknown,RIGHTARG=unknown,PROCEDURE=unknowncat(unknown,unknown));

然后问题又来了，既然说到了"||"操作符，那么可以联想到，在Oracle中还有很多字符串函数，其中有一些函数是可以返回char类型的，但这些函数在OG系数据库中，返回的是TEXT类型，因此如果出现使用函数表达式的结果来进行判断时，可能又会引起各种和Oracle行为不一致的情况。

Oracle官方文档Upper函数

UPPER returns char, with all letters uppercase. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same data type as char

比如UPPER、LOWER函数，在oracle中，这两个函数是传varchar返回varchar,传char就返回char;而在OG系数据库中，这两个函数对于入参为一个字符串时，始终只会返回TEXT，如果仅有"="仅有一侧为upper，还可以通过前面的那个跨类型操作符来处理，但如果两侧是upper，就会让两边都是text,所以这个时候如果还保留空格，就会导致和ORACLE有差异，如下例

create table test_upper_char(a char(10),b varchar(10));
insert into test_upper_char values ('T_name  ','T_name  ');
select * from test_upper_char where upper(a)=upper('t_name');--应该要查得出
select * from test_upper_char where upper(b)=upper('t_name');--应该要查不出

所以此时只能采取一种选择，即增加upper函数对于char类型的重载，并且让unknown类型在往明确字符串类型转换时，转换成char类型。

前面说的都是"=" ,即怎么找到数据，但是还有"<" 或">"的场景，或者说怎么排序。看下面这两个SQL

select 1 from dual where  cast('a' as char(2))> cast('a'||chr(10) as char(2));

with x as 
(select 1 id, cast('a' as char(2)) col from dual 
union all 
select 2, cast('a'||chr(10) as char(2)) from dual )
select * from x order by col;

在ORACLE中，这里第一条SQL，可以查出数据，说明cast(‘a’ as char(2))大于cast(‘a’||chr(10) as char(2)) ，这很容易理解，因为前者补上去的第二个字符是个空格，空格的ascii码十进制是32，比10大；第2条SQL，显示的排序结果，第二行排在了第一行的前面，同样是 cast(‘a’ as char(2))大于cast(‘a’||chr(10) as char(2))
但是在OG中，结果却刚好相反， 说明char类型在OG中的比较，其实是去掉了尾部的空格，此处由于不涉及char到其他类型的转换，因此char_coerce_compat选项起不到作用。

其实还没这么简单，继续看下面这个sql

select 1 from dual where  cast(chr(10)||chr(32) as char(2))>cast(chr(10) as char(1));

大于号左右的长度不一致，如果我们把它按照常规字符串去理解，从左到右，第一个字节相等，第二个字节后者没数据，所以应该是大于的，但是实际上这里查不到数据，因为后面这个补了空格chr(32)，两个其实是相等的，也就是说，两个不一样长度的char类型，比较前，需要把短的一侧做补空格处理，补到和长的一侧一致

四、两个单引号包裹的字符串必须是CHAR？

执行下面这个SQL

with x as (select 'a' col from dual),
y as (select 'a ' col from dual)
select * from x,y where x.col=y.col

在ORACLE中，由于两个单引号包裹的字符串是char类型，所以后面的空格不影响这个值的比较；但是在OG中，子查询内’a’是unknown类型，结果集出来后，转换成了text类型，此时就变成了1长度和2长度的字符串进行比较，结果显然是不相等。
假设我们通过某种方式，让unknown类型不转成text，而是转成char类型，这条SQL就能查出来了。但是，实际上问题仍旧没有从根本上解决，因为下面这个SQL

select 1 from dual where 'a' ='a ';

什么函数都没有，也没有子查询，就两个字符串的比较，ORACLE返回一行，OG返回0行，怎么解？不能通过转换的方式了，单引号包裹的是char才能解决这个问题，或者创建unknown类型的操作符，但这不就是把unknown视为了char类型么？

五、UNION ALL ?

如果两个不同长度的char类型发生union all，是否会自动补长度？

create table test_char3 (char2 char(2) ,char3 char(3));
insert into test_char3 values ('a','a');
select * from test_char3 where char2=rpad('a',2,' ');

select x.*,dump(col) from
(select 2 id,char2 col from test_char3
union all
select 3 id,char3 col from test_char3) x
--where col=rpad('a',2,' ');

答案是不会，无论长的char在上面还是在下面，其数据的长度还是原字段定义的长度,此表现在OG中是一致的。

如果char类型和varchar类型发生union all呢？
经测试，char类型的数据带着空格变成了varchar类型，同样无视union的顺序。

但是在OG中，union下面的类型会转换成上面的类型,这样如果外面写了where条件，就可能导致查询结果发生变化。

六、插入空字符串到char会不会补空格？

create table test_char2 (a char(1));
insert into test_char2 values ('');
select dump(a) from test_char2;

最后的查询返回的是null，因为这里其实就是插入的null,所以结论是插入空字符串时不会补空格。这点在OG中表现一致，没什么好说的。

七、decode表现有差异？

select decode(cast(' ' as char(1)),cast('  ' as char(2)),1,0) from dual;--返回0
select 1 from dual where cast(' ' as char(1))=cast('  ' as char(2));--返回1

这两条sql ，根据前面的分析，后一条能查出数据是可以预期到的，但是前一条竟然是返回0，这说明在decode函数中，ORACLE并不认为cast(’ ’ as char(1)) 等于 cast(’ ’ as char(2)) ，这就出现了一个自相矛盾的情况。
但实际上，关于这一点，ORACLE的官方文档里有进行说明
DECODE

• Ifexprandsearchare character data, then Oracle compares them using nonpadded comparison semantics.expr,search, andresultcan be any of the data typesCHAR,VARCHAR2,NCHAR, orNVARCHAR2. The string returned is ofVARCHAR2data type and is in the same character set as the firstresultparameter.

decode比较的时候，使用不补空格的比较方式，所以纯空格的char(1)就不会等于纯空格的char(2)

总结

经过上述分析后，CHAR类型的行为就逐渐清晰起来了

1. 即两个单引号包裹的字符串应识别为char类型，或者说直接用char类型替代OG中的unknown类型，并且继承unknown类型的所有规则（ORACLE没有unknown类型的说法）
2. char类型在隐式转换到其他字符串类型时，应保留空格进行转换（即OG的set behavior_compat_options =char_coerce_compat）
3. 对于upper/lower/concat函数以及"||"操作符，需要新增char类型的重载，并且返回char类型（是否还有其他函数和操作符，需要进行调研）
4. 两个char类型比较时，要把短的一侧补空格补到和长的一侧一致（只考虑等于时是可以按去空格的方式，但是排序则不行）
5. char和varchar类型union all时，char类型要转成varchar类型，不论顺序

思考

有兴趣的读者，可以把上面的用例去其他数据库中测试对比一下。

我有尝试在很多个宣传ORACLE兼容性很高的国产数据库中进行这一系列的char类型测试，发现没有一家能百分百和ORACLE行为一样（传言基于ORACLE套壳的说法可以否了）。但是大多数国产数据库都隐含一种思路：即ORACLE能查出来的，自己就要能查出来;ORACLE查不出来的，就不管自己是不是能查出来了。但这样就引起了有些行为和Oracle不一致，甚至由此导致了排序结果也不一致。而且这种排序结果，很难通过后续升级的方式来修正，因为已经存在的索引是按之前的顺序排的，如果升级数据库，会需要把索引进行一次重建，对于数据量大的库，这是个非常耗时的事。
现在很多应用开发人员其实并不完全清楚ORACLE的char类型机制，写SQL经常写出BUG。那么对于国产数据库，就算研究清楚了ORACLE的机制，做得和ORACLE一模一样，也不过是把应用开发者们写的含有BUG的SQL换个库继续错误下去。
所以，真的有必要绕来绕去做得和ORACLE完全一致么？就为了平替？在ORACLE上的开发复杂度继续保持到国产数据库中去？是不是做一个更简单易懂的规则，提升开发友好度更好？

我没有答案。