MySQL 如何检查字段是否为空或空mysql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17832906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if field is null or empty mysql?
提问by veereev
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
我需要添加一个额外的检查field1 != ""，比如
SELECT IFNULL(field1, 'empty') OR field1 != "" as field1 from tablename
Any idea how to accomplish this?
知道如何做到这一点吗？
回答by juergen d
Either use
要么使用
SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1
from tablename
or
或者
SELECT case when field1 IS NULL or field1 = ''
then 'empty'
else field1
end as field1
from tablename
If you only want to check for nulland 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')。但这不适用于空字符串。
回答by hims056
Alternatively you can also use CASEfor the same:
或者，您也可以使用CASE相同的方法：
SELECT CASE WHEN field1 IS NULL OR field1 = ''
THEN 'empty'
ELSE field1 END AS field1
FROM tablename.
回答by Gleb Kemarsky
You can use the IFNULLfunctioninside the IF. This will be a little shorter, and there will be fewer repetitions of the field name.
您可以使用该IFNULL功能内IF。这会短一点，并且字段名称的重复次数会更少。
SELECT IF(IFNULL(field1, '') = '', 'empty', field1) AS field1
FROM tablename
回答by palhares
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
回答by a man from another galaxy
Try using nullif:
尝试使用nullif：
SELECT ifnull(nullif(field1,''),'empty') AS field1
FROM tablename;
回答by user7256715
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
回答by Adam Noor
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!";
}
