mysql - 我怎样才能替换空值？

Question

我有两个这样的表:

// 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 时设置一些默认值或 name or rep or avatar is null .所以这是预期的结果:

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

我该怎么做？

---

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

$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 做到这一点。

Answer 1

您可以简单地使用 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() 可以处理多个参数。