解析SQL中的Null

怪我咯
發布: 2017-04-05 11:52:37
原創
1337 人瀏覽過

        NULL 在電腦和程式世界中表示的是未知,不確定。雖然中文翻譯為 “空”, 但此空(null)非彼空(empty)。 Null表示的是一種未知狀態,未來狀態,例如小明兜里有多少錢我不清楚,但也不能肯定為0,這時在計算機中就使用Null來表示未知和不確定。

  雖然熟練掌握SQL的人對於Null不會有什麼疑問,但總結得很全的文章還是很難找,看到一篇英文版的, 感覺還不錯。

  Tony Hoare 在1965年發明了null 引用, 並認為這是他犯下的「幾十億美元的錯誤」. 即使是50年後的今天, SQL中的null 值還是導致許多常見錯誤的罪魁禍首.

  我們一起來看那些最令人震驚的情況。

  Null不支援大小/相等判斷

#下面的2個查詢,不管表users 有多少筆記錄,傳回的記錄都是0行:

select * from users where deleted_at = null;
 
– result: 0 rows
 
select * from users where deleted_at != null;
 
– result: 0 rows
登入後複製

  怎麼會這樣子? 一切只因為null 是表示一種「未知」的型別。也就是說,用常規的比較操作子(normal conditional operators)來將 null 與其他值比較是沒有意義的。 Null 也不等於 Null(近似理解: 未知的值不能等於未知的值,兩者間的關係也是未知,否則數學和邏輯上就亂套了)。

  – 注意: 下面的SQL適合於MySQL,如果是Oracle,你需要加上… from dual;

select null > 0;
 
– result: null
 
select null < 0;
 
– result: null
 
select null = 0;
 
– result: null
 
select null = null;
 
– result: null
 
select null != null;
 
– result: null
登入後複製

  將某個值與null 比較的正確方法是使用is 關鍵字, 以及is not 運算子:

select * from users
 
where deleted_at is null;
 
– result: 所有被标记为删除的 users
登入後複製

  如果想要判斷兩列的值是否不相同,則可以使用is distinct from:

select * from users
 
where has_address is distinct from has_photo
 
– result: 地址(address)或照片(photo)两者只有其一的用户
登入後複製

  not in 與Null

  子查詢(subselect)是一種很方便的過濾資料的方法。例如,如果想要查詢沒有任何包的用戶,可以編寫下面這樣一個查詢:

select * from users 
 
where id not in (select user_id from packages)
登入後複製

  但此時假若packages 表中某一行的user_id 是null 的話,問題就來了: 返回結果是空的! 要理解為什麼會發生這種古怪的事情, 我們需要理解SQL編譯器究竟乾了些什麼. 下面是一個更簡單的範例:

select * from users 
 
where id not in (1, 2, null)
登入後複製

  這個SQL語句會被轉換為:

select * from users 
 
where id != 1 and id != 2 and id != null
登入後複製

  我們知道,id != null 結果是個未知值, null. 而任意值和null 進行and 運算的結果都是null, 所以相當於沒有其他條件. 那麼出這種結果的原因就是null 的邏輯值不為true.

  如果條件調換過來, 查詢結果就沒有問題。 現在我們查詢有package的使用者.

select * from users 
 
where id in (select user_id from packages)
登入後複製

  同樣我們可以使用簡單的範例:

select * from users
 
where id in (1, 2, null)
登入後複製

  這條SQL被轉換為:

select * from users 
 
where id = 1 or id = 2 or id = null
登入後複製

  因為where 子句中是被轉換為:

select name, points
 
from users
 
order by 2 desc;
 
– points 为 null 的记录排在所有记录之前!
登入後複製

  因為where 子句中是一串的or 條件,所以其中某個的結果為null 也是無關緊要的。非真(non-true)值並不影響子句中其他部分的計算結果,相當於被忽略了。

  Null與排序

  在排序時, null 值被認為是最大的. 在降序排序時(descending)這會讓你非常頭大,因為null值排在了最前面。

  下面這個查詢是為了根據得分顯示用戶排名, 但它將沒有得分的用戶排到了最前面!

– 在输出时将 null 转换为 0 :
 
select name, coalesce(points, 0)
 
from users
 
order by 2 desc;
 
– 输出时保留 null, 但排序时转换为 0 :
 
select name, points
 
from users
 
order by coalesce(points, 0) desc;
登入後複製

  解決這類問題有兩種思路。最簡單的一種是用coalesce 消除null的影響:

select name, coalesce(points, 0)
 
from users
 
order by 2 desc nulls last;
登入後複製

還有一種方式需要資料庫的支持,指定排序時將null 值放在最前面還是最後面:

select case when num_users = 0 then 0 
 
else total_sales/num_users end;
登入後複製

當然, null 也可以用來防止錯誤的發生,例如處理除數為0的數學運算錯誤。

  被 0 除

  除數為0是一個非常 egg-painfull 的錯誤。昨天還運作得好好的SQL,突然被0除一下子就出錯了。一個常用的解法是先用 case 語句判斷分母(denominator)是否為0,再進行除法運算。

  ase 語句的方式其實很難看,而且分母被重複使用了。如果是簡單的情況還好,如果分母是個很複雜的

表達式

,那麼悲劇就來了: 很難讀,很難維護和修改,一不小心就是一堆BUG.

  這時候我們可以看看null 的好處. 使用nullif 使得分母為0時變成null. 這樣就不再報錯, num_users = 0 時返回結果變為null.

select coalesce(total_sales/nullif(num_users, 0), 0);
 
null 再转换回0
登入後複製

  如果不想要null,而是希望轉換為0 或其他數, 則可以在前一個SQL的基礎上使用coalesce函數:rrreee

  Conclusion#######

  Tony Hoare 也許會後悔自己的錯誤, 但至少null 存在的問題很容易地就解決了. 那麼快去練練新的大招吧,從此遠離null 挖出來的無效大坑(nullifying)!


以上是解析SQL中的Null的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網:公益線上PHP培訓,幫助PHP學習者快速成長!