replace null or blank with 0 用空或者null来被0替换

梦里梦外; 2022-05-08 11:42 142阅读 0赞

在开发中遇到一个问题，在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?][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';

![在这里插入图片描述][70]  
这里你需要注意的是，用0来替换NULL和空的区别，NULL不等同于空。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzQzMjU_size_16_color_FFFFFF_t_70]

[what the main difference between NULL and Empty String]: https://www.daniweb.com/programming/databases/threads/145832/what-is-the-main-differences-between-null-value-and-blank
[70]: /images/20220508/ffa1f63147ad42e5949fa19dde32e8e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzQzMjU_size_16_color_FFFFFF_t_70]: /images/20220508/b9b3dcf3f96a4f349d9d7837f2511079.png