如何检查字段是否为null或空的mysql?

I am trying to figure out how to check if a field is null or empty. I have this

我想知道如何检查字段是否为空。我有这个

SELECT IFNULL(field1, 'empty') as field1 from tablename

I need to add an additional check field1 != "" something like

我需要添加一个复选框1 != "之类的

SELECT IFNULL(field1, 'empty') OR field1 != ""  as field1 from tablename

Any idea how to accomplish this?

你知道怎么做吗?

6 个解决方案

#1

121

Either use

要么使用

SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 
from tablename

或

SELECT case when field1 IS NULL or field1 = ''
            then 'empty'
            else field1
       end as field1 
from tablename

If you only want to check for null and not for empty strings then you can also use ifnull() or coalesce(field1, 'empty'). But that is not suitable for empty strings.

如果您只想检查null而不是空字符串，那么还可以使用ifnull()或coalesce(field1， 'empty')。但这并不适用于空字符串。

#2

Alternatively you can also use CASE for the same:

或者你也可以用大小写来表示:

SELECT CASE WHEN field1 IS NULL OR field1 = '' 
       THEN 'empty' 
       ELSE field1 END AS field1
FROM tablename.

#3

You can create a function to make this easy.

您可以创建一个函数来简化这一过程。

create function IFEMPTY(s text, defaultValue text)
returns text deterministic
return if(s is null or s = '', defaultValue, s);

Using:

使用:

SELECT IFEMPTY(field1, 'empty') as field1 
from tablename

#4

If you would like to check in PHP , then you should do something like :

如果您想要用PHP进行检查，那么您应该这样做:

$query_s =mysql_query("SELECT YOURROWNAME from `YOURTABLENAME` where name = $name");
$ertom=mysql_fetch_array($query_s);
if ('' !== $ertom['YOURROWNAME']) {
  //do your action
  echo "It was filled";
} else { 
  echo "it was empty!";
}

#5

SELECT * FROM ( 
    SELECT  2 AS RTYPE,V.ID AS VTYPE, DATE_FORMAT(ENTDT, ''%d-%m-%Y'')  AS ENTDT,V.NAME AS VOUCHERTYPE,VOUCHERNO,ROUND(IF((DR_CR)>0,(DR_CR),0),0) AS DR ,ROUND(IF((DR_CR)<0,(DR_CR)*-1,0),2) AS CR ,ROUND((dr_cr),2) AS BALAMT, IF(d.narr IS NULL OR d.narr='''',t.narration,d.narr) AS NARRATION 
    FROM trans_m AS t JOIN trans_dtl AS d ON(t.ID=d.TRANSID)
    JOIN acc_head L ON(D.ACC_ID=L.ID) 
    JOIN VOUCHERTYPE_M AS V ON(T.VOUCHERTYPE=V.ID)  
    WHERE T.CMPID=',COMPANYID,' AND  d.ACC_ID=',LEDGERID ,' AND t.entdt>=''',FROMDATE ,''' AND t.entdt<=''',TODATE ,''' ',VTYPE,'
    ORDER BY CAST(ENTDT AS DATE)) AS ta

#6

You can use the IFNULL function inside the IF. This will be a little shorter, and there will be fewer repetitions of the field name.

您可以在IF中使用IFNULL函数。这会更短一点，字段名的重复次数也会更少。

SELECT IF(IFNULL(field1, '') = '', 'empty', field1) AS field1 
FROM tablename

#1

121