MySQL - 哪种方法更好地检查列是否为 null 或空

Question

好吧，当我们要选择列中不包含任何内容的记录时，我们可以使用:

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

但是，在大多数情况下，我看到开发人员使用这个:

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

这两者之间有什么偏好吗？ (例如性能、默认支持、版本支持等)

如果是，请说明。

Answer 1

在一行中计算两个表达式应该不会有这么大的差异。人们使用第二个版本的原因是索引的使用。 Mysql对此有特殊的优化，甚至可以与或一起使用。

参见 IS NULL Optimization

IS NULL Optimization

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

If a WHERE clause includes a col_name IS NULL condition for a column that is declared as NOT NULL, that expression is optimized away. This optimization does not occur in cases when the column might produce NULL anyway; for example, if it comes from a table on the right side of a LEFT JOIN.

MySQL can also optimize the combination col_name = expr OR col_name IS NULL, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null when this optimization is used.