在開發中遇到一個問題,在mysql中字段都為varchar型,但是有的沒有值查出來是NULL,有的查出來是空。
先來看一下mysql中的NULL和empty String的差別:
1.A NULL value represents the absence of a value for a record in a field (others softwares call it also a missing value).
2.An empty value is a "field-formatted" value with no significant data in it.
3.NULL isn't allocated any memory, the string with NUll value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".
4.Null has no bounds, it can be used for string, integer, date, etc. fields in a database. Empty string is just regarding a string; it's a string like 'asdfasdf' is, but is just has no length. If you have no value for a field, use null, not an empty string.
5.Null is the database's determination of an absense of a value logically, so to speak. You can query like: where FIELD_NAME is NULL
上述的這段文字來源于這個網址:what the main difference between NULL and Empty String?
從上面的解釋中可以看出:
(1)NULL是一個丢失的值或者說是記錄中不存在的值,我們叫做NULL。NULL在資料存儲中是不會被配置設定存儲空間的。它可以是一個字元串,對象,整形,日期等等資料庫的字段類型值。
(2)Empty String可以了解為空,它是一種沒有意義的資料,但是它在資料存儲中需要被配置設定資料存儲空間,會以
''
的形式進行存儲。
上面就是對NULL和空的解釋,總而言之,NULL就是不知道的東西,而空是知道的東西,隻是以
''
的形式進行了存儲。
下面來讓我們看看在mysql中如何把null替換為0或者把空替換為0,具體的實作如下,有多種方式來實作:
SELECT
IFNULL(T.HJXGB,0),
COALESCE(T.HJXGB,0),T.HJXGB,
T.SZZTM,
COALESCE(NULLIF(T.SZZTM, ''),'0'),
CASE WHEN T.SZZTM='' THEN 0 ELSE T.SZZTM END,
T.SZZTM
FROM T_JMJZDAXX T WHERE T.SZZBM='310106010120180001';
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiAzNvwVZ2x2bzNXak9CX90TQNNkRrFlQKBTSvwFbslmZvwFMwQzLcVmepNHdu9mZvwFVywUNMZTY18CX052bm9CX9UlaNpXU61UNJRVT3V1MMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2LcRHelR3LcJzLctmch1mclRXY39DM1QzMwMDM2ETMxATM4EDMy8CX0Vmbu4GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.jpg)
這裡你需要注意的是,用0來替換NULL和空的差別,NULL不等同于空。