在MySQL中，效率更高：IFNULL还是NULLIF？

These two MySQL functions do the same thing:

这两个MySQL函数做同样的事情：

IFNULL(column_name, 'test') = 'test'

要么

NULLIF(column_name, 'test') IS NULL

Which one is more efficient?

哪一个更有效率？

3 个解决方案

#1

They are both as efficient as each other - the functions have about the same overhead as each other.

它们彼此之间的效率都很高 - 这些功能的开销大致相同。

But this is more efficient than either:

但这比以下任何一个都更有效：

(column_name is null 
 or column_name = 'test')

Because the function doesn't need to be invoked.

因为不需要调用该函数。

You may find putting the more commonly encountered test first improves performance.

您可能会发现更常见的测试首先会提高性能。

With questions like this, the simplest and more reliable way to discover relative performance is to just try them and compare query timings. Make sure you have production-sized and realistic-valued datasets so the test is fair.

有了这样的问题，发现相对性能的最简单，最可靠的方法就是尝试它们并比较查询时间。确保您拥有生产规模和实际值的数据集，因此测试是公平的。

#2

Purpose of NULLIF and IFNULL is not same, so performance comparison does not make any sense.

NULLIF和IFNULL的目的不一样，所以性能比较没有任何意义。

NULLIF is used to return null if the expression has a specific value, whereas IFNULL is used to return text if expression is null.

如果表达式具有特定值，则NULLIF用于返回null，而如果expression为null，则使用IFNULL返回文本。

Example:

例：

SELECT IFNULL(field,'empty') from table1;

从table1中选择IFNULL（字段，'空'）;

Since null does not make much sense to end user.

因为null对最终用户没有多大意义。

insert into table1 (field) values (nullif(field,'empty'));

插入table1（字段）值（nullif（field，'empty'））;

Since null has a special meaning in database.

由于null在数据库中具有特殊含义。

#3

they are used for different purpose, performance comparison doesn't make any sense.

它们用于不同的目的，性能比较没有任何意义。

select if(a,b,c);    # a ? b : c       # if a!=0 and a is not null then b else c

select ifnull(a,b);  # a ? a : b       # if a is not null then a else b

select nullif(a,b);  # a=b ? null : a  # if a=b then null else a

http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

#1