腦海中一直存著清晰的一幕,牙牙學語摸索SQL語法的青澀少年Jeffrey,端坐SQL大師Ryan的跟前,聽著諄諄教誨:

孩子,閉上眼晴,用原力去感受... 在SQL世界裡NULL具有獨特的魔力,VARCHAR欄位儲存著NULL或是空字串? 在意義上是絕對不同的...

後來在DB、Web領域打滾多年,我常常採行這項Schema設計理念:NULL代表使用者未曾輸入過任何資料,空字串則代表使用者輸入了,但未指定任何值。在SQL Server的世界裡,這條鐵律一直運行得很好,直到我遇到了ORACLE...

今天在寫一個動態的SQL複合條件查詢,為了簡化起見,用了以下的技巧:

SELECT SomeColumns....
FROM SomeTable
WHERE (:userId = '' OR UserIdCol = :userId)
AND SomeOtherCriteria...

我打的如意算盤是,如果userId傳入空字串,就不限定UserIdCol的值;否則就透過指定userId的方式,只傳回UserIdCol=:userId的結果。

 

測試了一下,將:userId參數設為空字串,理應列出所有的資料。很好!! 什麼都沒傳回來... orz

Debug了一下,才發現在ORACLE裡,根本沒有空字串這種東西,空字串會被視為NULL。因此,'' = ''等同於NULL = NULL,等於比對不會傳回True或False,而是傳回NULL。

接著,用Google找到相關說明。雖然違背了ANSI SQL 92的定義,但ORACLE的確會將空字串當作NULL處理,導致了非我預期的結果。

由於不能因為這點特性要求更換DataBase,山不轉路轉,路不轉人轉,上述的SQL語法要改寫成:

SELECT SomeColumns....
FROM SomeTable
WHERE (:userId IS NULL OR UserIdCol = :userId)
AND SomeOtherCriteria...

繼續幹活去!


Comments

# by ryan

孩子, 對Oracle的怨恨或是orz, 只會引領你投入黑暗的勢力. 不過....既然你已經自封為Darkthread黑武士, 也就沒什麼關係, 反正你兒女會把宇宙帶回合諧的原力世界, 該罵的還是盡量罵吧. May Force with You!

# by QOO

Good

# by Peter

剛好遇到這問題 謝謝黑大的文章指導

# by Scott

MSSQL派跳過去的最容易踩到這個雷XD

# by X.K.

Oracle 真的對空字串的處理真的很雷.. 之前有個 table column [varchar] is not null, 但是又要塞空資料,遇到 Oracle 不能塞 '',只能塞 ' ' 囧

Post a comment