如果数据集为空，MySQL返回默认值

Question

我正在尝试使用以下语句查询数据库：

SELECT coalesce(value, "NA") as value 
FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
WHERE chrom = 1 AND site = 120

如果数据库中有条目，这个查询就能正常工作。然而，如果没有找到该条目，它会返回一个空数据集。我想改变这个查询，使得即使在数据库表中没有找到它，它仍然会返回某种值（对于“value”），而不是一个空数据集。

- user2165857

---

你能发布一些相关的数据吗？ - John Ruddell

Answer 1

你考虑加一个UNION怎么样？

(   SELECT coalesce(value, "NA") as value 
    FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
    WHERE chrom = 1 AND site = 120
)
UNION ALL
(   SELECT 'NA' as value
    FROM mytable
    WHERE NOT EXISTS 
    (   SELECT coalesce(value, "NA") as value 
        FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
        WHERE chrom = 1 AND site = 120
    )
)

或者，如果您想要执行一次合并操作，以便在返回部分为 null 时更轻松地更改返回的部分，可以像下面这样从中选择。

SELECT COALESCE(value, "NA") as value
FROM
(   
    (   SELECT value
        FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
        WHERE chrom = 1 AND site = 120
    )
    UNION ALL
    (   SELECT NULL as value
        FROM mytable
        WHERE NOT EXISTS 
        (   SELECT value as value 
            FROM mytable FORCE INDEX (chrs) FORCE INDEX (sites) 
            WHERE chrom = 1 AND site = 120
        )
    )
) t

演示

- John Ruddell

Answer 2

一种（丑陋的）方法是：创建一个只有一行的表格（该表格中的数据无关紧要），然后使用以下代码：

SELECT coalesce(m.value, "NA") from oneline left join mytable m on m.chrom=1 and m.site=120;

下面是你将获得的内容：

mysql> select * from oneline;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0,00 sec)

mysql> select * from mytable;
+-------+-------+------+
| value | chrom | site |
+-------+-------+------+
| test  |     1 |  120 |
+-------+-------+------+
1 row in set (0,00 sec)

mysql> SELECT coalesce(m.value, "NA") from oneline left join mytable m on m.chrom=1 and m.site=120;
+-------------------------+
| coalesce(m.value, "NA") |
+-------------------------+
| test                    |
+-------------------------+
1 row in set (0,00 sec)

mysql> SELECT coalesce(m.value, "NA") from oneline left join mytable m on m.chrom=1 and m.site=119;
+-------------------------+
| coalesce(m.value, "NA") |
+-------------------------+
| NA                      |
+-------------------------+
1 row in set (0,00 sec)

- Antoine