如何替换空值?

问题描述

我有两张这样的表:

// users
+----+--------+------------+----------------+------------+
| id |  name  | reputation |     avatar     | visibility |
+----+--------+------------+----------------+------------+
| 1  | John   | 344        | er0942334r4... | 1          |
| 2  | Peter  | 65544      | 340ojfc343f... | 0          |
+----+--------+------------+----------------+------------+

// posts
+----+--------+------------+-----------+
| id | title  |  content   | author_id |
+----+--------+------------+-----------+
| 1  | title1 | content1   | 2         |
| 2  | title2 | content2   | 1         |
+----+--------+------------+-----------+

这里也是我的查询:

SELECT p.*, u.name, u.reputation rep, u.avatar
FROM posts p
LEFT JOIN users u
ON u.id = p.author_id AND u.visibility = 1
// please focus on this-> ^^^^^^^^^^^^^^^^

这是当前的结果:

+----+--------+------------+-----------+--------+------+----------------+
| id | title  |  content   | author_id |  name  | rep  |     avatar     |
+----+--------+------------+-----------+--------+------+----------------+
| 1  | title1 | content1   | 2         |        |      |                |
| 2  | title2 | content2   | 1         | John   | 344  | er0942334r4... |
+----+--------+------------+-----------+--------+------+----------------+

现在我想在 visibility <> 时设置一些默认值.1 or name or rep or avatar is null.所以这是预期结果:

Now I want to set some default values when visibility <> 1 or name or rep or avatar is null. So here is the expected result:

+----+--------+------------+-----------+--------+------+----------------+
| id | title  |  content   | author_id |  name  | rep  |     avatar     |
+----+--------+------------+-----------+--------+------+----------------+
| 1  | title1 | content1   | 2         | NoName | 1    | default        |
| 2  | title2 | content2   | 1         | John   | 344  | er0942334r4... |
+----+--------+------------+-----------+--------+------+----------------+

我该怎么做?

注意:我可以像这样通过 PHP 做到这一点:

Note: I can do that by PHP like this:

$name = is_null($result['name'])   ? 'NoName'  : $result['name'];
$name = is_null($result['rep'])    ? 1         : $result['rep'];
$name = is_null($result['avatar']) ? 'default' : $result['avatar'];

但我想知道如何通过纯 sql 做到这一点.

But I want to know how can I do that by pure sql.

推荐答案

您可以简单地使用 COALESCE() :

You can simply use COALESCE() :

SELECT p.*,
       COALESCE(u.name,'NoName') as `name`,
       COALESCE(u.reputation,1) as rep,
       COALESCE(u.avatar,'default') as avatar
FROM posts p
LEFT JOIN users u
ON u.id = p.author_id AND u.visibility = 1

您也可以将其替换为 IFNULL.它们之间的区别在于 COALESCE() 可以处理多个参数.

You can also replace it with IFNULL . The difference between them is that COALESCE() can handle multiple arguments.

这篇关于如何替换空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！