PostgreSQL与Oracle在数据比较上存在差异,本节简单介绍Oracle的数据比较原则.

一、数值型

所有负数小于0或者整数,如-1 < 100/-100 < -1.
浮点数NaN(not a number)比其他所有数值都要大,但等于自身即NaN.

二、日期型

较晚的日期大于较早的日期.比如2005年3月29日小于2006年1月18日.

三、字符型

字符值的比较基于两个度量:
A.二进制或语言排序(Binary or linguistic sorting)
B.空格填充或非空格填充比较语义(Blank-padded or nonpadded comparison semantics)
Binary and Linguistic Comparisons
Binary Comparisons是默认的比较方法,Oracle根据数据库字符集中字符的数字代码的串联值比较字符串.如字符串S1中按顺序某个字符的编码比另外一个字符串S2的字符编码大,则认为S1比S2要大.
Oracle认为空格比其他所有字符都要小(在大多数字符集下这个结论是成立的),如:ASCII/EBCDIC/ISO8859-P1等.实际测试中,如果空格与小于ASCII 32的不可见字符比较,空格会比这些字符要大,实际上可以认为仍然是使用空格对应的ASCII值与另外一端字符ASCII进行比较.


TEST-orcl@DESKTOP-21SUADG>show parameter NLS_SORT
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
TEST-orcl@DESKTOP-21SUADG>show parameter NLS_COMP
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_comp                             string      BINARY
TEST-orcl@DESKTOP-21SUADG>select case when '7 ' > '7'||chr(10)
  2              then 'Blank is larger'
  3              else 'Blank is smaller'
  4              end as ret
  5  from dual;
RET
----------------
Blank is larger

Blank-Padded and Nonpadded Comparison Semantics
在补空格的语义下,如果两个值有不同的长度,Oracle首先会在较短以便的字符中补足空格再去比较,方法是逐个字符比较直至第一个不同的字符或者全部比较完毕.在第一个字符不同的地方,那个字符较大,则认为那个字符串更大.如无不同的字符,则认为两者相等.该规则意味着两个字符串就算后面的空格数不同也认为是相等的.Oracle只在类型为CHAR/NCHAR/文本文字/用户自定义函数返回值时才会使用空格填充比较语义.在这种语义下,’a ‘ = ‘a’.
在不补足空格的语义下,Oracle逐个比较字符串的方法类似,不同的地方是遍历完毕较短字符串仍未找到不同的字符,则认为较长字符串较大,如果长度一致字符一致则认为字符串是相等的.在比较操作符任意一端的字符类型为VARCHAR2/NVARCHAR2时Oracle使用这种比较语义.在这种语义下,’a ‘ > ‘a’.

四、对象(Object)

使用MAP和ORDER两个对比函数进行对比,两个函数对比的是Object类型实例.

五、数据类型优先级

Oracle使用数据类型优先级来判定隐式数据类型转换(下面会讨论),Oracle数据类型遵循以下优先级:
A.Datetime和interval数据类型
B.BINARY_DOUBLE
C.BINARY_FLOAT
D.NUMBER
E.字符数据类型
F.其他内置数据类型

六、数据类型转换

通常来说,表达式中不能包含不同的数据类型,比如表达式5*10+’JAMES’是不合法的.但是,Oracle支持类型之间的隐式和显式转换.
隐式 VS 显式转换
基于以下原因,Oracle建议使用显式转换:
A.增强SQL语句可读性
B.隐式类型转换会影响性能,特别是把列值转换为常量的数据类型时
C.隐式转换依赖于上下文,因此不是每种情况下转换结果都是一样的.比如,不同的NLS_DATE_FORMAT会导致datetime转换为字符串结果不同
D.隐式转换的算法在不同版本之间可能会发生变化。而显式转换则相对稳定
E.隐式转换可能会导致用不上索引

隐式类型转换
Oracle在可转换的情况下自动转换数据类型,下面是转换矩阵:

隐式转换规则如下:
1.在INSERT/UPDATE操作中,转换数据值为相应的列数据类型
2.在SELECT FROM操作中,转换列数据类型为目标变量类型
3.在操作数值型数据时,通常会调整为最大可用的精度&刻度.在这种情况下,这些操作产生的数据类型可能与基表中的数据类型不同
4.在比较字符型和数值型数据时,转换字符型为数值型
5.在字符型/数值型数据与浮点数之间转换时可能不精确,因为字符类型和NUMBER使用十进制精度,而浮点数使用二进制精度
6.转换CLOB为字符类型(如VARCHAR2),或者转换BLOB转换为RAW时,如果需要转换的数据大小比目标数据类型可表示的大小要大,则返回错误
7.TIMESTAMP转换为DATE时,时间戳中秒的小数部分将被截断(较老的版本会四舍五入)
8.BINARY_FLOAT转换为BINARY_DOUBLE时不会丢失精度
9.BINARY_DOUBLE转换为BINARY_FLOAT会丢失精度(如使用超过float更大的位数来表示精度)
10.在比较字符型和日期型数据时,转换字符型为日期型
11.输入的参数与函数或者操作符参数不匹配时,转换为相应的数据类型
12.在赋值时,把等号右边的数据转换为左边目标数据类型
13.在字符串拼接操作时,转换非字符类型为字符类型
14.在对字符/非字符数据类型进行算术运算/比较时,根据需要会将所有字符类型转换为数值/日期/Rowid
15.大多数的SQL字符函数可接受CLOB类型作为参数,会执行CLOB和字符类型之间的转换.如果CLOB大小超过4000B,则只获取CLOB中的4000B
16.在RAW/LONG RAW和字符类型之间相互转换时,二进制数据会被表示为十六进制的格式,一个十六进制字符表示RAW数据中的4位
17.在CHAR和VARCHAR2以及NCHAR和NVARCHAR2之间比较时可能需要不同的字符集.这种情况下默认的转换方向是从数据库字符集转换为国家字符集
下面是一些例子:


TEST-orcl@DESKTOP-V430TU3>set autotrace on exp
TEST-orcl@DESKTOP-V430TU3>select c1 + '100' from t_cmp;
  C1+'100'
----------
       101
       102
       103
Execution Plan
----------------------------------------------------------
Plan hash value: 598432719
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     3 |    39 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_CMP |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
TEST-orcl@DESKTOP-V430TU3>select to_char(c1) as c1 from t_cmp where c2 > 1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 598432719
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_CMP |     1 |    25 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("C2")>1)
Note
-----
   - dynamic sampling used for this statement (level=2)
TEST-orcl@DESKTOP-V430TU3>select to_char(c1) as c1 from t_cmp where c3 > c2;
C1
------------------------------
1
2
3
Execution Plan
----------------------------------------------------------
Plan hash value: 598432719
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     3 |   141 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_CMP |     3 |   141 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C3">"C2")
Note
-----
   - dynamic sampling used for this statement (level=2)
TEST-orcl@DESKTOP-V430TU3>

显式类型转换
可使用SQL转换函数显式执行类型转换,下面是转换矩阵:

七、安全性考虑

在日期型数据转换为文本时,在没有指定日期格式时不管是隐式还是显式都会依赖于全局会话参数:NLS_DATE_FORMAT/NLS_TIMESTAMP_FORMAT/NLS_TIMESTAMP_TZ_FORMAT.
在动态SQL语句文本与没有显式格式指定datetime数据类型串接时,格式对会话参数的依赖可能会对数据库安全产生负面影响.通过SQL注入可以修改数据库参数.

八、参考资料

Data Type Comparison Rules

Oracle vs PostgreSQL，研发注意事项（8）- Oracle数据比较规则