MySQL - 如何更好地检查列是否为空或为空字符串？

Question

好的，当我们需要选择某一列中没有任何内容的记录时，可以使用以下方法：

SELECT * FROM my_table WHERE NULLIF(my_column, '') IS NULL;

但是，在大多数情况下，我看到开发人员使用以下代码：

SELECT * FROM my_table WHERE my_column IS NULL OR my_column = ''

这两个有没有什么偏好？（例如性能，默认支持，版本支持等）

如果有，请解释。

- Mojtaba

---

我有点认为你应该将采纳的答案改为Solarflare的。我的回答涉及函数与OR情况的比较；他们的答案给出了“... IS NULL OR ...”更优越的明确原因。 - Uueerdo

@Uueerdo，慷慨又温柔 ;) - Mojtaba

Answer 1

在单个行上评估这两个表达式不应该有太大的差异。人们使用第二个版本的原因是使用索引。Mysql对此进行了特殊优化，即使使用or也可以使用它。

请参见IS NULL Optimization。

IS NULL 优化

MySQL可以对col_name IS NULL执行与col_name = constant_value相同的优化。例如，MySQL可以使用索引和范围来搜索IS NULL的NULL值。

如果WHERE子句包括对已声明为NOT NULL的列的col_name IS NULL条件，则该表达式被优化掉。但是，当列可能产生NULL时（例如，它来自LEFT JOIN右侧的表），则不会发生此优化。

MySQL还可以优化组合col_name = expr OR col_name IS NULL，这是已解析的子查询中常见的一种形式。当使用此优化时，EXPLAIN显示ref_or_null。

- Solarflare

Answer 2

据我的了解，就执行时间或性能而言，最佳方式是：

SELECT * FROM my_table WHERE my_column = ''

参考资料: 如何在mysql中检查列是否为空或null

- Naveed Ramzan

---

我在你提供的帖子中没有找到任何性能比较。 - Mojtaba

尽管如我在上面的回答中所说，“Naveed的解决方案对于任何看过它的人来说都是显而易见的。”并且“在我的书中，这赢得了比赛，每一次都是如此。”我并不真正关心微秒。但是，我确实关心清晰度。我不希望人类读者不得不去查找MySQL文档（就像我一样...）来弄清楚这个语句的含义，并确保它实际上正在做我认为它在做的事情和我想要的事情。 - Mike Robinson

Answer 3

在条件中对字段调用函数会自动丢弃该字段可能用于加速查询的任何潜在索引。在条件列表中使用 OR 通常也会这样做（但是对于所有索引都是如此）。

然而，对于前者，您最终会为每一行进行一次函数调用；对于后者，存在利用短路求值的可能性。

当然，还有第三种选项可以利用索引...

SELECT * FROM my_table WHERE my_column IS NULL
UNION
SELECT * FROM my_table WHERE my_column = ''

但是使用UNION/两个查询的成本可能会超过索引使用的好处，具体取决于实际数据。

- Uueerdo

---

这是一个有趣的问题，查询优化器是否可以“优化掉”NULLIF()并不实际将其处理为真正的函数调用。（它是否会像文档建议的那样替换为CASE？它可能会...）##我对您基于UNION的替代方案一开始并不“感到舒适”，而且只是“不想再花第二或第三次浏览来弄清楚它是否真的有效”。我敢说，查询优化器足够聪明以使用索引。所以，我宁愿看到“简单”而不是“聪明”。但是，这只是我的看法。* - Mike Robinson

UNION-based的替代方案总是我的最后选择，因为它几乎会复制一个查询（这可能比实际复制更糟），但是我可以从经验中确认MySQL不会优化OR子句以获得相同的好处。随着条件集变得更加复杂，它的适用性也变得不太可能，这可能是MySQL没有尝试以类似的方式进行优化的原因。@MikeRobinson - Uueerdo

Answer 4

这两个结构不同。来自MySQL文档页面的引用：

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. 
This is the same as:
  CASE WHEN expr1 = expr2 
  THEN NULL 
  ELSE expr1 
  END.

因此，这个逻辑是在测试一个列是否为“NULL或者是空字符串”。

• 如果my_column目前为NULL，则函数将返回它的值（NULL ...），因为它不等于空字符串。
• 如果my_column是一个空字符串，则函数将返回NULL。
• 如果是任何其他值，则会返回该值。

我完全同意Naveed的答案，他说：“这很‘可爱’...不要太‘可爱’。”他的答案（“使用OR...”）会导致非常简单的SQL语句，一眼就能看出来。而这个“聪明”的解决方案则不然。（而且可能稍微低效一些。）

当设计数据库时，当然你确实想要避免这种情况的发生。除非真正的情况是“我们有一个非NULL值，并且该值确实是‘空字符串’”，否则永远不要将“空字符串”存储在VARCHAR类型的字段中。

- Mike Robinson

---

明白了。关于你最后一段的内容，我并不总是从头设计数据库。有时候，我会面对一个有数百万条记录的表格，而且我没有权限更改其结构。顺便说一下，谢谢你的解释。 - Mojtaba

我不知道是否应该称其为“可爱”。在这种特定的情况下，似乎有些过度，但在更大的一组条件中（可能有许多类似这样的检查），使用这种形式 (或 `IFNULL(x, '') = '') 可以使查询更易读。...而那些需要“阅读”数据的人并不总是编写数据的人。 - Uueerdo

哦，没错，Mojtaba，那就是“现实世界的状态！”:-) 我们都必须处理包含数百万条记录的数据库，这些记录“就是它们的样子”。 - Mike Robinson

Answer 5

通常人们包含 ='' 部分的原因是因为 SQL Server 将其存储为空字符串，这与 NULL 不同。

参考链接： openshac 的回答

- Justin

---

我知道这一点。我提出的两个查询都是相同的。我的问题是哪一个更好？ - Mojtaba

我会两者都包含。这不会对性能造成太大影响，而且这样可以确保它正在执行你想要的操作。 - Justin