MySQL 如果不是 NULL，则显示 1，否则显示 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9387839/
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 IF NOT NULL, then display 1, else display 0
提问by coffeemonitor
I'm working with a little display complication here. I'm sure there's an IF/ELSE capability I'm just overlooking.
我正在处理一些显示复杂问题。我确定我只是忽略了 IF/ELSE 功能。
I have 2 tables I'm querying (customers, addresses). The first has the main record, but the second may or may not have a record to LEFT JOIN to.
我有 2 个表正在查询（客户、地址）。第一个有主记录，但第二个可能有也可能没有 LEFT JOIN 的记录。
I want to display a zero if there is no record in the addresses table. And I want to only display 1, if a record exists.
如果地址表中没有记录，我想显示一个零。如果存在记录，我只想显示 1。
What I've attempted so far:
到目前为止我尝试过的：
SELECT c.name, COALESCE(a.addressid,0) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123
This first example does not do it. But I may be utilizing COALESCE wrong.
第一个例子没有这样做。但我可能错误地使用了 COALESCE。
How can I display a 0, if null, and a 1, if something exists?
如果存在，如何显示 0（如果为空）和 1（如果存在）？
回答by ypercube??
Instead of COALESCE(a.addressid,0) AS addressexists, use CASE:
而不是COALESCE(a.addressid,0) AS addressexists，使用CASE：
CASE WHEN a.addressid IS NOT NULL
THEN 1
ELSE 0
END AS addressexists
or the simpler:
或者更简单的：
(a.addressid IS NOT NULL) AS addressexists
This works because TRUEis displayed as 1in MySQL and FALSEas 0.
这是有效的，因为在 MySQL 中TRUE显示为.1FALSE0
回答by Eugen Rieck
SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123
回答by Slawomir
Careful if you're coming from C/C++ and expecting this to work:
如果您来自 C/C++ 并希望它起作用，请小心：
select if(name, 1, 0) ..
Even if 'name' is not NULL, unlike in C, a false-condition still triggers and the above statement returns 0. Thus, you have to remember to explicitly check for NULL or empty string:
即使 'name' 不是 NULL，与 C 不同，仍然会触发假条件并且上述语句返回 0。因此，您必须记住显式检查 NULL 或空字符串：
select if(name is null or name = '', 0, 1)
PS Eugen's example up above is correct, but I wanted to clarify this nuance as it caught me by surprise.
PS Eugen 上面的例子是正确的，但我想澄清这个细微差别，因为它让我感到惊讶。
回答by bucko
SELECT
c.name,
CASE WHEN a.addressid IS NULL THEN 0 ELSE 1 END AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123
回答by PodTech.io
Another method without WHERE, try this..
另一种没有WHERE的方法，试试这个..
Will select both Empty and NULL values
将同时选择 Empty 和 NULL 值
SELECT ISNULL(NULLIF(fieldname,'')) FROM tablename
It will set null if it is an empty string, then be true on that also.
如果它是一个空字符串，它将设置为 null，然后也为 true。
回答by Salvi Pascual
You can actually use an IF statement in the latest versions of MySQL.
您实际上可以在最新版本的 MySQL 中使用 IF 语句。
IF(expr,if_true_expr,if_false_expr)
IE:
IE：
SELECT name, IF(ISNULL(name), 'robot', 'human') AS type
FROM visitors
回答by Kevin Chen
If within TSQL, you can try :
如果在 TSQL 中，您可以尝试：
SELECT IIF(a.addressid IS NULL, 0, 1) AS addressexists
SQL Server should work
SQL Server 应该可以工作
