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在可轉換的情況下自動轉換資料型別,下面是轉換矩陣:
![](http://img.blog.itpub.net/blog/2019/06/05/d967f7550fe60ad2.png?x-oss-process=style/bb)

隱式轉換規則如下:
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轉換函式顯式執行型別轉換,下面是轉換矩陣:
![](http://img.blog.itpub.net/blog/2019/06/05/ff4606859d227ec2.png?x-oss-process=style/bb)

### 七、安全性考慮
在日期型資料轉換為文字時,在沒有指定日期格式時不管是隱式還是顯式都會依賴於全域性會話引數:NLS_DATE_FORMAT/NLS_TIMESTAMP_FORMAT/NLS_TIMESTAMP_TZ_FORMAT.
在動態SQL語句文字與沒有顯式格式指定datetime資料型別串接時,格式對會話引數的依賴可能會對資料庫安全產生負面影響.透過SQL隱碼攻擊可以修改資料庫引數.

### 八、參考資料
[Data Type Comparison Rules ](https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF30027)

Oracle vs PostgreSQL，研發注意事項（8）- Oracle資料比較規則

相關文章