這篇文章主要為大家介紹了關於mysql中替代null的IFNULL()與COALESCE()函數的相關資料,文中透過範例程式碼介紹的非常詳細,對大家具有一定的參考學習價值,需要的朋友們下面來一起看看看吧。
在MySQL中isnull()
函數不能作為替代null值!
如下:
首先有個名字為business的表:
##
SELECT ISNULL(business_name,'no business_name') AS bus_isnull FROM business WHERE id=2直接運行就會報錯:錯誤代碼:1582
Incorrect parameter count in the call to native function 'isnull'所以,
isnull() 函數在mysql就行不通了。可以用
ifnull()和
coalesce()來代替。如下:
ifnull()函數:
#
SELECT IFNULL(business_name,'no business_name') AS bus_ifnull FROM business WHERE id=2執行結果:
查詢
的值不為null時:
SELECT IFNULL(business_name,'no business_name') AS bus_ifnull FROM business WHERE id=1
結果如下:
使用
函數:
<pre class="brush:sql;">SELECT COALESCE(business_name,&#39;no business_name&#39;) AS bus_coalesce FROM business WHERE id=2</pre>
結果如下:
SELECT COALESCE(business_name,'no business_name') AS bus_coalesce FROM business WHERE id=1
coalesce()
也可以傳回第一個不為null的值。如下:
SELECT COALESCE(business_name,district_id,id) AS bus_coalesce FROM business WHERE id=2
那麼,
isnull()
在mysql怎麼用呢?答案就是用在where後面。如下:
SELECT * FROM business WHERE ISNULL(business_name)
結果如下:
同樣,
和is not null
也是用在where後面。SELECT * FROM business WHERE business_name IS NULL
結果如下:
SELECT * FROM business WHERE business_name IS NOT NULL################################################################ ############總結##########
以上是關於mysql中替代null的IFNULL()與COALESCE()函數詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章!