mySQL 同时查询空值和 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12939690/
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
mySQL query for empty and NULL value together
提问by U.B.A.R
In a database, among the fields, I have two fields which are picNameand UserName.
The picNamecan have a NULLvalue, a picture's name or it can be empty.
在数据库中，在字段中，我有两个字段是picName和UserName。该picName可以有一个NULL值，图片的文件名，也可以是空的。
I want to query the userNameof those entries whose picNameis either NULLor empty.
我想查询userName那些picName为NULL或 为空的条目。
I tried these commands separately
我分别尝试了这些命令
Select userName from user where picName = ''
Select userName from user where picName IS NULL
But I want to get both the results in 1 query.
但我想在 1 个查询中获得两个结果。
I tried using INoperator like this
我尝试使用这样的IN运算符
Select userName from user where picName IN ('' , IS NULL)
But it didn't work.
但它没有用。
How should I do it...?
我该怎么做……？
回答by John Woo
use ORif you have multiple filters in your condition
使用OR，如果你在你的病情有多重过滤器
Select userName
from user
where picName IS NULL OR
picName = ''
回答by eggyal
An alternative to the other answers is to use MySQL's IFNULL()or COALESCE()functions:
其他答案的替代方法是使用 MySQLIFNULL()或COALESCE()函数：
SELECT userName FROM user WHERE IFNULL(picName='',TRUE);
SELECT userName FROM user WHERE COALESCE(picName,'') = '';
回答by fvu
You want one of both conditions to be satisfied, so condition one ORcondition two must be true, and that's exactly what you need to write:
您希望满足OR两个条件之一，因此条件一条件二必须为真，这正是您需要编写的：
Select userName from user where picName = '' OR picName IS NULL
回答by Kermit
SELECT userName FROM `user` WHERE picName IS NULL OR picName = ''
回答by Kalle
...ISNULL(picName ,'') is a function of MSSQL
...ISNULL(picName ,'') 是MSSQL的函数
select case when (picname is null or picname = '' then username else 'filled' end from user
select case when (picname is null or picname = '' then username else 'filled' end from user
gives you the UserName if picname = '' or null else the text 'Filled'. If you want to filter these ones use (like the other said): ...where picname is null or picname = ''
如果 picname = '' 或 null，则为您提供用户名，否则为文本“已填充”。如果你想过滤这些使用（就像其他人说的）： ... where picname is null or picname = ''
回答by Guru
select userName from user WHERE ISNULL(picName ,'') = ''
