mysql，ifnull vs coalesce，哪个更快？

if it's known that there are only two values to candidate for the result of a column,

如果知道列的结果只有两个候选值，

ifnull(a, b) as a_or_b_1

and

和

coalesce(a, b) as a_or_b_2

will give the same result. but which is faster? when searching i found this article, which says ifnull is faster. but it was the only article i found. any views on this?

会给出相同的结果。但哪个更快？在搜索时我发现了这篇文章，其中说ifnull更快。但这是我找到的唯一一篇文章。对此有何看法？

thanks in advance :)

提前致谢：）

1 个解决方案

#1

My view is that you should benchmark for your usage.

我的观点是你应该根据你的用法进行基准测试。

I doubt there will be much difference. Bear in mind that while a single benchmark might suggest that one is slightly better, variation in the data over time might change that result.

我怀疑会有太大的不同。请记住，虽然单个基准测试可能表明一个稍微好一点，但随着时间的推移，数据的变化可能会改变这一结果。

Also note that COALESCE has been part of standard SQL since 1992 - I'm not sure IFNULL is in any standard yet.

另请注意，COALESCE自1992年以来一直是标准SQL的一部分 - 我不确定IFNULL是否符合任何标准。

There's a nice article by Adam Machanic about benchmarking an equivalent scenario - Performance: ISNULL vs. COALESCE (in SQL Server). Note some of the provisos on getting a valid test.

Adam Machanic有一篇关于对等效场景进行基准测试的好文章 - 性能：ISNULL与COALESCE（在SQL Server中）。请注意获得有效测试的一些附带条件。

#1